Multiple workgroups Jet DAO

Hi,

I've got two JET databases, each database has its own workgroup file. How can I open both databases within the same application ??

According to the help text, as soon as you use DAO, the systemDB property of the DBEngine object becomes read only.

Is there a way around this ??
LVL 10
smeggheadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simonbennettCommented:
Smeggs,

I don't think you cna do this in access - you have to use VB. The closest I could get was to open 2 db's with different users from the same mdw e.g.

Dim ws1 As Workspace
    Dim ws2 As Workspace
    Dim db1 As Database
    Dim db2 As Database
   
    Set ws1 = DBEngine.CreateWorkspace("1", "User1", "PWD1")
    Set ws2 = DBEngine.CreateWorkspace("2", "User2", "PWD2")
   
    Set db1 = ws1.OpenDatabase("c:\db1.mdb", , False)
    Set db2 = ws2.OpenDatabase("c:\db2.mdb", , False)

I'll be dead keen to know if there is an answer to this one as I am certain JET can only use 1 system db.

Simon
0
simonbennettCommented:
WHOOPS - though I was in the access area!

Ok you can do it!

I do it at the moment by maintaining an array containing the data from each recordset, following this process..

set system db1
open database1
copy data from rs to array
close
set to nothing
set system db2
open database2
copy data from rs to array
close
set to nothing

****DO WHATEVER***** (i merge the data here)

set system db1
open database1
save data from array to table
close
set to nothing
set system db2
open database2
save data from array to table
close
set to nothing

Of course this is a kind of one of thing - if you want it for concurrent access I think if you use ADO and talk to access via a DSN, you can create a DSN for each DB specifying the system.mdw. Never tried it though....

Good Luck

Simon
0
smeggheadAuthor Commented:
That's the way I'm currently doing it.. via odbc, I was just wondering if there was a way using DAO.

I don't suppose there can be as the SystemDB property applies to a non-collective object (DBEngine).

Thanks for your suggestions anyway..

if nobody adds any other comments in the next few days, I'll award the points.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

leg1Commented:
systemdb has to be set before allocating dbengine.  Once it's allocated you can't change it.  It seems like you should be able to allocate 2 dbengines though.
0
leg1Commented:
Whoops, You can only create 1 dbengine but I wonder if you can create 1 dbengine/DLL.  What abaout setting up a different project to handle the other database.
0
smeggheadAuthor Commented:
leg1, I thought about the 'Other project' approach, but it's a bit messy. However, the suggestion about multiplie instances of DAO... now you might have something there...

can you ..

dim DAO1 as new dao
dim DAO2 as new dao

set dao1.dbengine.systemdb=file1
set dao2.dbengine.systemdb=file2

Hmmm, I'll have a play with that.
0
smeggheadAuthor Commented:
Nope, just tried it... Unless I'm doing something wrong...

Option Explicit
 Dim x As New DAO.DBEngine
 Dim y As New DAO.DBEngine


Private Sub Form_Load()
 x.SystemDB = "c:\winnt\system32\rrrr.mdw"
 MsgBox x.SystemDB
 y.SystemDB = "c:\winnt\system32\system.mdw"
 MsgBox x.SystemDB
 MsgBox y.SystemDB
End Sub


The first messagebox appears as rrrr.mdw, then the next two as system.mdw

Oh well
0
leg1Commented:
It seems to me like the only possiblility is 2 projects.  What you would export between projects would be rst's.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smeggheadAuthor Commented:
Thanks Leg and Simon...

I gave up on it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.