DAO problem between 16bit Access and SQL7 using VB

Access Version 2.0 database sucessfully  linked to an SQL7 server.

The frontend application is currently VB4 16bit.

At present references are DAO 25 library with below code

Global US as Recordset (contained in a module)
Global DB as Database (contained in a module)

Set db = Workspaces(0).OpenDatabase(DatabasePath & "\RPI.MDB", False, False)

Set US = db.OpenRecordset("Users")  

US.Index = "ByUserIDSeq"

When 2.5 dao library is referenced we get the error runtime error 3251 feature not available on the line

US.Index = "ByUserIDSeq"

However, when we use the DA02.5/3.0 compatability library we get error runtime error 420 class does not support ole automation on the line

Set db = Workspaces(0).OpenDatabase(DatabasePath & "\RPI.MDB", False, False)
Is there another 2.5/3.0 compatability library we can use. Or are there some declarations missing somewhere.

Any ideas what the problem could be?
Who is Participating?
inthedarkConnect With a Mentor Commented:
Each version of Access had different syntax. So try:

Dim AnyDB as DAO.Database ' make sure using DAO

Set AnyDB = Workspaces(0).OpenDatabase("c:\My Path\RPI.MDB") ' simplefy this statement

If it still does not open, then try moving the code into a module in the database this will prove that your access v2 installation is OK.
PaulHewsConnect With a Mentor Commented:
I think 2.5 had OpenTable, OpenSnapshot, etc.

Set US = db.OpenTable("Users")  

With the compatibility library, you should be able to do this

Set US = db.OpenRecordset("Users", dbOpenTable)
supersuny2kAuthor Commented:
Unfortunately, neither solutions work, still getting the same error.

Access 2.0 can talk to SQL, I can create a new table in Access 2.0 and connect fine. However the application involved has been around for ages and has 300,000 lines of code therefore major changes cannot happen to the application. The application can talk directly to the access DB with no errors however the DB is over 200MB in size, thus the reason to try and stabalise with SQL.

Any ideas with be great


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.