[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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 ??
0
smegghead
Asked:
smegghead
  • 4
  • 3
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
smeggheadAuthor Commented:
Thanks Leg and Simon...

I gave up on it.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now