Discussion Locked

Differnces in Access 97 and 2000

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In Access 97 I created a recordset in a module this way:

Dim Wrk as Workspace
Dim DB as Database
Dim Rst as Recordset

Set Wrk = DBEngine(0)
Set DB = Wrk.Databases(0)
Set Rst = DB.OpenRecordset(strSQL)

In Access 2000, it is telling me that Workspace and Database are undefined user variables. All of the help screens set-up (at least the Dim) this way. Did I miss something when I set-up Access? What do I need to do? What are my alternatives?

I have done a work around but am stuck by another problem.

Dim Wrk as Variant, DB as Variant
Dim strSQL as String, Rst as RecordSet
strSQL = "Select * FROM Table"
Set Wrk = DBEngine(0)
Set DB = Wrk.OpenDatabase("Tracker.mdb")
Set Rst = DB.OpenRecordset(strSQL)

With this, it says that the strSQL is the wrong data type.

3 replies

by peyison

The difference is that in Access 97 the default is DAO, while in 2000 the default is ADO. So you have two options.

1) You can change back to DAO:

* From the VBA editor, select Tools -> References

* Uncheck Microsoft ActiveX Data Objects...

* Check Microsoft DAO XX Object Library

2) (The better option, but requires more work) Convert your code to ADO. It's not too hard to learn.

The only reason to use DAO over ADO is if you're creating tables, or editing table structures or links. ADO does not provide the ability to modify the structure of the database.

Hope this helps.

by m_ozog

Thank you for your answer. I was unaware that you had to explicitly tell Acces what you were using.

I took a crash course in using ADO recordsets.

Thanks

This discussion is closed to new replies.