Can't save design changes with 2 different set dbs?

[Post Tenebras Lux]

I have encountered an odd problem. I just split my database into linked
tables (in Tables.mdb) and code and queries in my main mdb (Program.mdb).

Pulling a recordset from the table in the TABLES.MDB works fine:

Dim sDbPath as string, strTable as string
Dim dbTables As DAO.Database
Dim rs_InTablesDatabase As DAO.Recordset

sDbPath = c:\work\Tables.mdb
strTable = "FirstTableToGetDataFrom"
Set dbTables = OpenDatabase(sDbPath)
Set rs_InTablesDatabase = dbTables.OpenRecordset(strTable, dbOpenTable)

As I'm testing my code, I can happily save changes during the Run or after
breaking.

However, I need to get a list of Tickers via a query that is in my
CurrentDb (Program.mdb) that accesses the a linked table in Tables.mdb. This
is not the same table as I opened the recordset with.

'This line works fine
Dim dbProgram as DAO.Database

'As soon as the next line runs:
Set dbProgram = CurrentDb

'the code will continue, but if I try to save any design changes, from that
moment on I get the following error message:

"Microsoft Office Access can't save design changes or save to a new database
object because another user has the file open. To save your design changes or
to save to a new object, you must have exclusive access to the file."

I found an unhelpful kb on the matter:
http://support.microsoft.com/kb/824278

This message comes whenever I try to save any design changes after that Set
dbProgram line, and I can't save anything, but the code will continue to run.
It won't let me save even if I break / reset the code, try
dbProgram.close
set dbProgram = Nothing

Only closing Access and reopening restores my ability to save design changes.

I need this second database object (I think), because I want to do the
following:
sql = "Select * from strFilterQuery" ' this query is in my Program.mdb (=
CurrentDb)

Set rst_DataFromQuery = dbProgram.OpenRecordset(sql, dbOpenSnapshot)

If i don't set dbTables (the first db) to Tables.mdb, but simply use:
set dbTables = CurrentDb

and then open a recordset from the LINKED table in Program.mdb, everything
works fine all the way and I can save anywhere as often as I like.

As the problem arises BEFORE I get to setting the second recordset to the
Query, I don't think that has anything to do with this.

Why can I use set db = CurrentDb all over the code without any problems, but
if I first set one db variable (dbTables) to another mdb file, using set db =
CurrentDb subsequently causes this problem.

Thanks for any help on resolving this one.

 

[Post Tenebras Lux]

Ok - think I've solved it, but not really sure why.

Between the first and second "set db =" lines, I had NOT opened the
recordset for the first dbTables.openrecordset.

I was able make the second set dbProgram = " and "dbProgram.OpenRecordSet"
run first, and then ran the set dbTables and dbTables.openrecordset after
that. Now it all works fine.

I don't know whether reversing the order of opening the recordsets made the
difference, or whether the originally first "set dbTables =" needed to be
followed by a dbTables.OpenRecordSet BEFORE I could set a second db ("set
dbProgram = ").

I'm guessing that Jet can only handle one set db at a time before it's used.
Anyone with more certain knowledge able to tell me so I can avoid this in
the future?

Thanks