Connecting to DB and opening RecordSets (Access 2000 SR1)

Topic

New Reply

Hi there all.

Scenario:
I have a situation where I attach some tables from one mdb, get some information out of them, and delete the attached tables.

I then repeat the process, except the only difference is that the name of the mdb is different.

I have designed it in such a way that the list of tables (which is very much dynamic during development) that I have to attach sits in a table, so all I have to do is have the problem fetch the list of names of the tables that it has to delete or attach and do the deletion/attachment.
(Sounds confusing/complicated doesn’t it! You should then see what the rest of the program will be like!)

Problem:
I am able to do this in Access 97 without any problem, but I am struggling to make heads or tails of the Access 2000 way of connecting to the database and opening recordsets.

I know that my SQL statement for retrieving the list of tablenames is correct (which I have as: “SELECT TableName FROM tbl_AttachedTableORDER BY TableName;”

Is there anyone who has a code example to help me get started off? (I’m familiar with Access 2 and Access 97 and VBA in Word 2000)

Thanks in advance!

James

Reply | Quote

Replies

Hi

I wonder if your problems with recordsets arise from using the right reference to DAO rather than ADO.

Step 1. From the VBA window , In tools —> references make sure you have ADO 3.6 checked
Setp 2. Explicity declare your database and recordset variables as DAO

ie.

Dim dbConnect as DAO.Database
Dim rstConnect as DAO.Recordset

This may help?

Reply | Quote

This is definitely it!

Set it all to DAO and it worked first time. can’t believe it was something so stupidly simple!

Thanks for the help!

James

Reply | Quote

You can use the same code in 2000 as in 97 using DAO if you set the DAO 3.6 library reference. Creating links is easier using DAO than ADO, since it uses familiar objects, methods and properties instead of the objects, methods and properties exposed by the OLEDB provider. However, as Jayden suggests, you need to specify the object model in your declarations so that Access knows if you’re dealing with a DAO.Recordset or an ADODB.Recordset, etc.

Reply | Quote

Thanks Charlotte…

I’ll definiely remember this for the future!

James

Reply | Quote