ADO Recordset.open problems with Access 2.0

[Sypher2]

I'm having problems getting a simple application to run on an Access 2.0 database. It works wonderfully with Access 2002. I was wondering if anyone can tell me why the following code doesn't work:

Public Conn1 As ADODB.Connection
Public rstNTE As ADODB.Recordset
Public cmdNTE As ADODB.Command

Public Sub OpenX()
    Dim strConn As String

    Set Conn1 = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\My Documents\VBProjects\Tony2.mdb"
    Conn1.Open strConn
    Set rstNTE = New ADODB.Recordset
    Set cmdNTE = New ADODB.Command
End Sub

Private Sub Form_Load()
    Dim strNum As String
    
    'Call public subroutine to establish database connection
    OpenX
    cmdNTE.ActiveConnection = Conn1
    
    'Get pay locations
    strNum = "SELECT DISTINCT P_Loc FROM ROSTER;"
    cmdNTE.CommandText = strNum
    rstNTE.Open cmdNTE, , adOpenForwardOnly, adLockReadOnly, adCmdText
    Do Until rstNTE.EOF = True
        If rstNTE!P_Loc > 100 And rstNTE!P_Loc < 400 Then
            lstPayLoc.AddItem rstNTE!P_Loc
        End If
        rstNTE.MoveNext
    Loop
    rstNTE.Close
    Conn1.Close
End Sub

My thinking is either the SQL query isn't the correct dialect or the recordset.open statement has a problem. Thanks for any help.

 

[qwe1204]

Your using the wrong provider try 3.51 instead of 4.0

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

 

[Morpheus1981]

One error that I see in ur code is this one
it is not adCmdText it must be adCmdUnkown

rstNTE.Open cmdNTE, , adOpenForwardOnly, adLockReadOnly,
[adCmdUnkown]
[adCmdText]

Try it in this way if it doesn't work let me know.

 

[Morpheus1981]

I one more thing before start using ur query use rs.Requery just in case

 

[petermeachem]

Do you mean Access 2? I use OLEDB.4.0 with that all the time, no problems.
Have you actually got any data?

Before Do Until rstNTE.EOF = True
(which should be Do Until rstNTE.EOF ) you ought to test

if not( rstNTE.eof and rstNTE.bof ) then

Peter Meachem
peter@accuflight.com

 

[Sypher2]

Still can't get it to work. I tried adCmdUnknown in the recordset.open statement. I also tried going with Set rstNTE = cmdNTE.Execute. I keep getting an unspecified error. The database is on a server, and I'm attempting to run it on a client. I have written another simple application that works fine using Jet 4.0 and gets a full table (rstNTE.open "ipssrelate", conn1, , adCmdTable).

Any other ideas?