Problems using DAO to open CurrentDb (front end), and back end db

[Post Tenebras Lux]

I have a split database, with tables in tbl.mdb and all code and queries in
front.mdb

I want to open recordsets (using DAO - because I'm only using Access, and
its way faster than ADO if I'm staying in Access - I've tested it) from each
database.

Dim sDbTBL as string, sDbFront as string
Dim dbTBL as DAO.Database, dbFront as DAO.Database

sDbTBL = CurrentProject.Path & "\" & "TBL.mdb"

Set DbFront = DBEngine(0)(0) ' I've also tried = CurrentDb()
Set dbTBL = OpenDatabase(sDbTBL)

'this works fine to here BUT if I try to make any changes to DbFront (which
is my front end and in which I'm writing the code), I get an error message:
"Microsoft Access can't save design changes or save to a new databse object
because another user has the file open. To save your desing changes or to
save to a new object, you must have exclusive access to the file."

The mdb is set to Shared, and I'm the only one using it (it's on my local
computer). It seems to be the reference to dbFront that causes this error.
I also get
"The database has been placed in a state by user <name> on machine <name>
that prevents it from being opened or locked. (Error 3734) "

I've tried the solution at http://www.source-code.biz/snippets/vbasic/10.htm
but doesn't solve the saving problem (though I don't get the Error 3734
message).

So, my question is - how do you have two open db references (using DAO),
where one of the references is to the MDB you are writing code in (FrontDb)
and still save new code?

All the questions I've seen around this don't seem to solve the problem.

Thanks, in advance.

 

[DAVID]

Short of converting back to A97, I don't think there
is anything you can do. A2K+ always shifts to exclusive
mode if you try to make design changes to the VBA
project.

Still, you should try the solution suggested at
http://www.source-code.biz/snippets/vbasic/10.htm
What error do you get when you try that?

Note that the solution suggests using an ADO
connection, and it is really only suggested for
changing database properties (which also normally
require exclusive access).

When I want to make code changes to an Access
project, I make sure that I have exclusive
access and no other copies of the database are
open.

In my case, this normally means that I have to
close then re-open Access before I can make
any changes to my forms.

(david)

 

[Post Tenebras Lux]

Thanks - but it turns out that I missed the solution to be found in... the
HELP file! Clicking F1 on CurrentDb in the VBE revealed the following:

If you need to work with another database at the same time that the current
database is open in the Microsoft Access window, use the OpenDatabase method
of a Workspace object. The OpenDatabase method doesn't actually open the
second database in the Microsoft Access window; it simply returns a Database
variable representing the second database. The following example returns a
pointer to the current database and to a database called Contacts.mdb:

Dim dbsCurrent As Database, dbsContacts As Database
Set dbsCurrent = CurrentDb
Set dbsContacts = DBEngine.Workspaces(0).OpenDatabase("Contacts.mdb")


It solved the problem. Who would have thought that the Access VBA help file
might actually be helpful!