How to load databases with linked tables using Microsoft DAO 3.6 object library (dao360)

Question

We've reached the 2Gb limit with our current MS Access database (see general section of https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c). This page suggests "linking to tables in other Access databases. You can link to tables in multiple database files, each of which can be as large as 2GB."

We've set this up in MS Access and the linked table opens fine in MS Access. However, the linked table is not happy to open in our software, which is using Microsoft DAO 3.6 object library (dao360).

Is there anything special that is required to open a linked table in this way? I've tried adding both databases to the workspace (along the lines of https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/workspace-opendatabase-method-dao), but this doesn't seem to help (or at least I'm not doing it right). The error I get back when opening the linked table (using the Database.OpenRecordset method is simply "invalid operation".

Apologies for my ignorance, this database code has been here many years before I joined and I've never had to go near it until now!

UPDATE

I can make some progress by specifying dbOpenDynaset as the parameter of Database.OpenRecordset method (https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao) as per this SOF post. However, it then fails later when calling RecordSet.put_Index with the error "Operation is not supported for this type of object" (presumably referring to the dynaset)

Answer 1

A few things: If you are using the accDB format, then you do NOT want, nor need a reference to the DAO object library. It is a depreciated library, and the new ACE library which replaces the DAO library is now what you use. It is in general 100% compatible. So, no reference to DAO is required if you using the accDB format.

The DAO library is NOW built into Access. You don’t use or want the external DAO library.

So just make sure you use the “default” references in Access.

Microsoft Access 14.0 Object Library

Microsoft Office 14.0 Access database engine object.

(of course 14.0 is for access 2010 – you have different numbers for later versions). So, 15.0 for access 2013. (but, these references will in general be set for you).

If you are not using the mdb format, then REMOVE the DAO referance - it is not required anymore.

At that point, then you should be good to go.

For a linked table? If you use this code for a NON linked table then it will work:

Dim rst     As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblNames1",dbOpenTable)

NOTE in above the use of dbOpenTable. You REALLY do not need or wanted to have used dbOpenTable anyway.

In fact, you REALLY should use this:

Dim rst     As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblNames1")

However,if the table NOW linked?

Well then this will work:

Dim rst     As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblNames1")

However, this will FAIL!!!

Dim rst     As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblNames1",dbOpenTable)

So you can’t FORCE or use dbOpenTable for linked tables.

And I suppose (but not necessary), to convey 100% you the developer intentions, you could use this for a linked table:

Set rst = CurrentDb.OpenRecordset("tblNames1", dbOpenDynaset)

RecordSet.put_Index

That could and would have NEVER worked. There is no such thing as put_index for DAO recordsets. So that issue is 100% separate from that of linked tables.

There is no put_Index method or property of the DAO reocrdset object, and thus no matter what you are attempting to do, that syntax is wrong, and was never supported.