Hi all,

I am using an Access 2000 database with VB6SP5.
Opened the database, successful.
Now I am trying to open a table, with SQL.
Somehow it completely ignores the SQL statement, but it does open the table, remarkably. Recordset contains no records though.

Here's (a part of) my code.

dbfilename = App.Path & "\database.mdb"
Set db = New ADODB.Connection
db.Provider = "Microsoft.jet.OLEDB.4.0"
db.Open dbfilename

Set rs = New ADODB.Recordset
rs.ActiveConnection = dbschema
rs.LockType = adLockOptimistic
rs.Source = "SELECT * FROM speelschema WHERE poule=" & poulenummer & " ORDER BY datum ASC, vantijd ASC"
rs.Open

If I use the exact same SQL phrase in Access2000, it return 15 records, like it should. In VB6SP5, it doesn't return an error. However rs.RecordCount returns -1. Meaning an empty Recordset.

Does anyone have any clue how to open the table with working SQL statement ?

Any help would be greatly appreciated !!

Hello. I don't know the rest of your code, so I can only do some reflections: you have set the rs.ActiveConnectio property on the dbschema variable, but previously you have open a connection named db. Are you sure the dbschema connection is an existing, valid, open connection?

____________________________
Real Programmer can count up to 1024 on his fingers

Thanks for your reply, yronium.

I already corrected that typo.
It should read rs.ActiveConnection = db.
Somehow it ended up worng in my post.

Other info : The table doesn't have primary keys set.
I read somewhere that could be a problem. I set the primary key to an AutoNumber field, but that didn't do the trick so I changed it back to what it was.

Any other helpful hints ?

Thanks,
Jan

OK....
It gets weirder the more I get into it.

Turns out the RecordSet is not empty.
When I call rs.MoveFirst it moves to the first record.
I can browse through the records. And it looks like the SQL has worked fine.
But.....the .RecordCount still returns -1.
So I can never check whether or not my recordset is empty.

Am I doing something wrong ?

Hi

Put the following line before your db.open

db.CursorLocation = adUseClient

Also

You can still test for the eof by

Do while not rs.eof     .... your stuff     rs.movenext loop

Steve

Thanks, steve_w.
The .CursorLocation = adUseClient did the trick

Thanks again
Jan