OpenDatabase help please?

[Gargoyle]

OS: XP Home, Access: 2003Pro

Okay, here's an oddity . . .

Two databases, each with identical tables (TDFs). Data stored in the
tables is split across the 2 DBs, so half of the data is stored in the
tables of DB1, other half of the data is stored in the tables of DB2.

DB1 does not have its database-password set.
However, DB2 DOES have a database-password.

Problem: how to programmatically open DB2 from within DB1 at run-time?

I've tried these approaches:-

Approach#1:
DBEngine.Workspaces(0).OpenDatabase("C:\DB2.mdb", False, False,
"ODBC;pwd=xxxxx")
Database.Connection.OpenRecordset("Table1")

Approach#2 (gleaned from Access help files):
Set wrkJet = CreateWorkspace("", " user-level-username",
"user-level-password", dbUseJet)
Set dbsDB1 = wrkJet.OpenDatabase("C:\DB2.mdb", False, False,
";pwd=xxxxx")
Set rstDB1 = dbszpwd1.OpenRecordset("Table1")

where wrkJet, dbs, and rst are Workspace, Database and Recordset
respectively.

With Approach#1 I get Error 3151. Fair enough.

With Approach#2 I can open the database and access the table (although
I do get an annoying MsgBox saying "Opening DB2…", which I have to
click to close and then there is a noticeable delay before any results
from reading the table are available.

I'm not used to working with multiple DBs this way, usually I just use
front/back end approach and linked-tables, so I accept that there
might be better ways.

Can anyone point me in the direction of some sample code?
Thanks.

 

[Albert D. Kallal]

The first problem here is that your 2nd example is talking about user level
security.

That means you secured the file using workgroup security and THIS HAS
NOTHING to do with a database password. They are a HUGE grand
canyon of different issues.

However, I might be miss-reading your question, and perhaps you are using
workgroup security here..and hence the 2nd example?

If you just trying open up a database, and you need to supply a password
(not user level security where you need both a user name + logon password),
then you can use the following:


Dim MyDb As DAO.Database
dim rst As DAO.RecordSet

Set MyDb =
OpenDatabase("c:\dbhold\test.mdb", False, False, "MS Access;pwd=zoo")

set rst = MyDb.OpenRecordSet("table1")

You should not be seeing any message boxes, or anything. The above is NOT
automaton, and is NOT launching a 2nd copy of the database (so, startup code
and forms etc. does not run). Note that you can't use automation to launch a
2nd copy of ms-access with the runtime (but, opendatabase DOES NOT launch a
2nd copy).

If you question was actually about workgroup security, and secured database,
then do feel free to correct my assuming here....

Note that the password supplied has to include the "MS Access;" part, and
no quotes need be included in the actual string:

strPwd = "zoo"

strPwd = "MS Access;pdw=" & strPdw

Set MyDb = OpenDatabase("c:\dbhold\test.mdb", False , False, strPdw)

 

[Gargoyle]

On Sat, 22 Sep 2007 14:01:01 -0600, "Albert D. Kallal"

Thanks Albert

I think it's the first solution, but I'll try both.