G
Guest
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.
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.