Access SQL problems using MS Jet OLEDB with ADO+VB6

Hi there. I am having trouble with querying a record set by date range using the Between operator. Im using VB 6 with ADO.

Referenced: Microsoft ActiveX Data Objects 2.8 Library

Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\localdata\orders.mdb;Persist Security Info=False"

SQL Query used: "SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Code:

QueryString is passed as "(InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Public Function GetOrders(ByRef OrderArray() As OrderDetails, QueryString As String) As Boolean

On Error GoTo errorHandler

'\\ Adds a new customer to the local database.

Dim dbConnection As ADODB.Connection
Dim dbRecordSet As ADODB.Recordset
Dim intLoopIndex As Integer

If Len(Application.LocalDBConnection) < 1 Then

MsgBox "No local database connection parameter provided. Please run the configuration utility. Unable to get orders.", 48, "Warning"

Exit Function

End If

Set dbConnection = New ADODB.Connection
Set dbRecordSet = New ADODB.Recordset

'Connect to the database
dbConnection.ConnectionString = Application.LocalDBConnection
dbConnection.Open

'Build SQL command
With dbRecordSet

.Open "SELECT * FROM tbl_orders WHERE " & QueryString, dbConnection, adOpenStatic, adLockReadOnly

If Not (.RecordCount < 1) Then

ReDim OrderArray(.RecordCount - 1)

intLoopIndex = 0

.MoveFirst

Do

OrderArray(intLoopIndex).Customer = .Fields("Customer").Value
OrderArray(intLoopIndex).CustOrdNo = .Fields("CustOrdNo").Value
OrderArray(intLoopIndex).Description = .Fields("Description").Value
OrderArray(intLoopIndex).User = .Fields("User").Value
OrderArray(intLoopIndex).Total = .Fields("Total").Value
OrderArray(intLoopIndex).InsertDate = .Fields("InsertDate").Value

.MoveNext

intLoopIndex = (intLoopIndex + 1)

Loop Until (.EOF)

GetOrders = True

End If

End With

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

Exit Function

errorHandler:

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

ShowError Err, "modFunctions.GetOrders()"

End Function


Problem:

The recordset returns a record that has the field 'InsertDate' equal to 15/07/2004. This is not within my date range specified.

The actual orders.mdb file is attached to this post.

Any help much appreciated!

Regards,
-Matthew Hall.Hi,

The following query will solve your requirement.

SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #7/12/2004# AND #7/14/2004#)

I believe Access evaluates date values as mm/dd/yyyy by default. If a supplied value is not valid in this format, Access will attempt to convert it. Therefore, the query you supplied would be equal to ... BETWEEN #12/07/2004# AND #7/14/2004#, which would indeed return 7/15/2004.|||Thank you for your response.

Is this not dependent on the systems regional settings?

Is there an option I can set in the actual Access file that will set the date to be in the correct format, or am I forced to use American format dates?

access denied using a trusted connection over a VPN

Hi - when i try to run a .NET app over my VPN using ADO.NET, a trusted conne
ction with SQL in mixed mode, i get 'access denied'. when i run the same app
from my local network thru the VPN, no problem..i'm using the same login an
d password for all computer
s(SQL Server is on another computer in the VPN network, which works fine w/t
rusted connection from the VPN server) How can i get access thru my VPN? Tha
nksAfter you have VPN'd in to your network. Test the following commands:
net use \\ServerName\IPC$
ISQL -Stcp:ServerName -E
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have the exact same problems. No problems when I'm connected to the
network.
When I'm at home using my vpn-client i'm not able to use trusted
connection.
I'm able to use net use \\myserver\ipc$ without any problems.
I have about 50 clients running Windows 2000 Pro SP4 and about 10 of
them are using VPN and 3 of them have the same problem as I.
I think that these errors started to occur sometime in july.
Might this be some Windowsupdate question..?
I use automatic download and install.
Thanks in advance.
lunatiic
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message743307.html