Can VB's DBEngine open 2 databases using 2 different MDW files? 1

[ibanezGuitars]

Hello,

I am revamping a VB6 system using Microsost's DAO 3.6 Object Model and Access 2002 databases on a Win XP machine. There is a Master.mdb database on the server that everyone shares and there is a Local.mdb database on each user's local hard drive for their own individual needs.

Can I use 2 separate .mdw (security) files to open each database individually?

In other words, as users logon to the system, can I have all of the users open the Master.mdb file on the server using the same Master.mdw security file (also on the server) and then have each user open their individual Local.mdb files which reside on their local hard drive using their own copy of the Local.mdw file (also residing on each user's hard drive) ???

Any suggestions or code snippets would be greatly appreciated.

Thanks for your time,

Dave

 

[eyeswideclosed]

You can have 2 connection objects, yes, but I would use ado if you can, dao is ancient technology. Btw I play an Ibanez too, artist.

 

[ibanezGuitars]

Thanks for the confirmation. The app is too darn big to rewrite... this one falls under the category of just trying to keep it breathing at this point. BTW - I've got a '77 Bob Weir Model with the Tree of Life inlay up the neck and to the top of the headstock. It's got the "Professional" headstock with the ornate scrolls cut into the top. Great guitar but it weighs 14.5 pounds - it's like wearing a bowling ball around your neck!

 

[eyeswideclosed]

I fully understand, we upgraded our apps from dao to ado when we went to .net and it was not fun, but it paid off.
I don't play much anymore, especially since some guitar tech dorked up my pickup wiring. I want to get an sg for the lightness and thin neck but they're so damn pricey, I'm looking at epiphone copies.

 

[ibanezGuitars]

Looking at the documentation, I'm not sure that I can instantiate two of the DBEngine.SystemDB objects at the same time. The code for the master database on the server is as follows-

'Workgroup Administaration file on network
DBEngine.SystemDB = mstrAppPath & "\CMT.mdw"
Set mwsW2W = DBEngine.CreateWorkspace("", mLogID, mLogPW)
Set mdatW2W = mwsW2W.OpenDatabase(mstrAppPath & mstrMDB)

I access the mstrMDB database (on the server) utilizing the CMT.mdw Workgroup Administaration file also on the server. Can I open up the user's local database using a different local mdw file on each user's PC? The code for the local users database is as follows.

DBEngine.SystemDB = mstrLocalPath & "\Local.mdw"
Set mwsLocal = DBEngine.CreateWorkspace("", mLogID, mLogPW)
Set mdatLocal = mwsLocal.OpenDatabase(mstrLocalPath _ mstrLocalMDB)

I guess my question is can the DBEngine be used again to open a local database using a local mdw file located on the user's own PC without causing errors or disabling access to the master mdb that I just opened on the server (prior to this one)?

Thanks for your time,

Dave

 

[eyeswideclosed]

Oh, I didn't use dbengine.systemdb when I used dao, I just created the workspace and opened the database, I was thinking you could have 2 of each of those, name them differently, do you have to do it that way?

 

[ibanezGuitars]

I'm trying to lighten the load - we've had an influx of new users and there are some problems with communications between buildings so I was trying to move the common reference tables to the user's local database that they currently use for processing purposes.

Both the server's master database and each local user's processing database are password protected. Currently, each utilizes the same mdw file on the server. My plan was to use a second workgroup Administration file (mdw) on each user's local machine to reduce the "record locking traffic on the server.

If I can use 2 different mdw files at the same time in both locations to reduce the record locking on the server's mdw file, then I was going to follow up and move all of the reference tables which hold specific lists of choices that appear in numerous pull-down boxes on many of the forms we are using on the screen down to the local user's database file. I've got to reduce the load on the master database.

The main issue to me is, "Can I use the original mdw file on the server to manage the data there and then use a second mdw file on the local user's PC to handle their local database's record locking needs?

Thanks, I appreciate your time,

Dave

 

[eyeswideclosed]

Well what about this then, after the first db is open, reset the dbengine.systemdb to use the 2nd mdw and open the second db? I don't think I'm helping you much.

 

[eyeswideclosed]

I dug up my old dao code:

reference dao

Dim wrkODBC As Workspace
Dim dbs As Database

Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set dbs = wrkODBC.OpenDatabase("mydsn", dbDriverNoPrompt, False, "ODBC;DSN=mydsn;UID=;PWD=;")

 

[ibanezGuitars]

Hello eyeswideclosed,

I think your earlier post, "After the first db is open, reset the dbengine.systemdb to use the 2nd mdw and open the second db? ", may be the only potential solution besides going to an ODBC connection for the second database.

I hope that once a database has been opened, I can change the mdw file that I specified for the DBEngine.SystemDB and open the 2nd database. Ill give it a try but there is one statement in the Microsoft documentation that says something to the effect that the DBEngine.SystemDB's mdw file must be set before any other reference to a DAO object is executed. I'll give it a try anyway but I think I may be forced to go the ODBC route.

Thanks for all of your time,

Dave