[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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?
2 Solutions
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.
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



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now