Switching Databases On the Fly

[vb6forever]

I'm experimenting, as I would like to place different data tables into two separate databases.

My program originally had one database (dbDAO) where all tables (including all data tables) resided.
I moved certain data tables to another database (dbSrc).
I then created a form with a combobox which would allow me to switch databases
and list the tables from the selected database (dbDAO or dbSrc).

Private Sub cboDBPathSymbol_Click()

   If mblnFormStartUp Then Exit Sub

   With cboDBPathSymbol
      Call MDao.DBOpen(dbSrc, .ItemData(.ListIndex))
   End With

   Set dbDAO = dbSrc
   
   dbSrc.Close
   Set dbSrc = Nothing
  
   Call obListBy_Click(OB_LISTBY_ALL)
 
End Sub

My call to obListBy_Click which lists the table names errors with
Error: 3420 "Object Invalid or No Longer Set"

That error occurs on dbDAO.OpenRecordset

My guess is it fails because one cannot: Set dbDAO = dbSrc

=================
Is there a way to Open and use another database, while referring to the first open database, which is referenced (dbDAO) throughout the APP?

[Eduardo-]

Comment the line dbSrc.Close and see what happens.

' dbSrc.Close

After the line dbDAO = dbSrc both variables point to the same database object, then if you do dbSrc.Close it is the same as if you did dbDAO.Close.

dbSrc and dbDAO are just object variables that point to an object but not a full object copy.

[vb6forever]

Eduardo: Thanks for responding.

1)
Still Errors with comment out both dbSrc.Close and Set dbSrc = Nothing
Error: 3048 "Can't Open Any More Databases"
FWIW: dbDAO is NOT being opened Exclusive.

2)
Thanks for the reminder I'm dealing with object variables.

[Zvoni]

Since it's DAO: Why not use the Workspace-Object?
https://learn.microsoft.com/en-us/of...ce-members-dao

https://learn.microsoft.com/en-us/of...s-property-dao

[vb6forever]

Zvoni: Thanks for responding.

I currently Open using

   Set dbDAO = DBEngine.OpenDatabase(strPath, False)     ', True, False

If I'm correct, you're suggesting this:

   Set dbDAO = DBEngine.Workspaces(0).OpenDatabase(strPath, False)      ', True, False

I guess I could Open the second DB as:

 Set dbSrc = DBEngine.Workspaces(1).OpenDatabase(strPath, False)      ', True, False

But I don't see off hand where that helps me, as all code to manipulate the records in each table is coded with dbDAO.
IMHO, an assignment needs to take place to make dbDAO refer to dbSrc, in order to use those tables -- OR --
what am I missing?

(NOTE: 4:00am here so going to get some shut eye for a bit).

[dz32]

with ado you would just create two connection objects each pointing to their own database and it would work. you could pass the connection object to the same functions and it would use whatever database passed.

hopefully dao has something similar, if not the switchover might not be to bad unless you used data binding

[Zvoni]

Zvoni: Thanks for responding.

I guess I could Open the second DB as:

 Set dbSrc = DBEngine.Workspaces(1).OpenDatabase(strPath, False)      ', True, False

Close.

Set dbSrc = DBEngine.Workspaces(0).OpenDatabase(strPath, False) ', True, False

But I don't see off hand where that helps me,

The Workspace-Object has a "Databases"-Collection (Look at my second link)
Now how do you work with Collections?
Aircode

Set dbMyDatabase = DBEngine.Workspaces(0).OpenDatabase(strDB1, False) ', True, False 
Set dbMyDatabase = DBEngine.Workspaces(0).OpenDatabase(strDB2, False) ', True, False 

Set dbMyDatabase = Nothing  'You're just setting the reference to Nothing. The Database-Objects still exist in the Collection

'Do Something

Set dbMyDatabase = DBEngine.Workspaces(0).Databases(1)
Set rsMyRecordset = dbMyDatabase.OpenRecordset(SomeSQL)
'
'
Set dbMyDatabase = DBEngine.Workspaces(0).Databases(2)
Set rsMyRecordset = dbMyDatabase.OpenRecordset(SomeSQL)

EDIT: This link might shed some more light
https://learn.microsoft.com/en-us/of...ase-method-dao

[vb6forever]

Thanks All for Responding.
1)
Just requested this thread be moved to the database forum as believe more appropriate in that location.

2)
Will review posts (dz32 and Zvoni) and post back when done.
IN the interim, DAO allows two databases to be Opened at the same time.
The kicker as I see it is, all my code (e.g. OpenRecordsets) references dbDAO.OpenRecordset.
When switching databases, dbDAO must be active, but Point to a Different Database (dbSrc in this case).
I can close the original dbDAO and open it pointing to dbSrc, BUT that will affect ALL currently running code which points to the original dbDAO.

[Eduardo-]

Just FYI, I use three DAO databases (Access MDB) in a program without any issue.

[vb6forever]

Eduardo:
Please see post #8(2). It is Not having multi databases open. It is how to structure the code, so the same code can be used to switch between those databases. without having to pass in a database parameter to needed procedures.

[vb6forever]

Zvoni:

Your post #7, looks really promising as a solution. Thanks for pointing out the Databases property. Was Not familiar with it.
Burned out right now. Will test later today/tomorrow and get back this thread.

[vb6forever]

If I run the following code,, the two databases are added to the Workspace Collection.
NOTE: GetPath is a function that returns the database Path.
The ENUMs used in function identify which database path.

'All three object variables are PUBLIC, but shown here for INFO ONLY
Dim wrk As DAO.Workspace 
Dim dbDAO As Database 
Dim dbSrc As Database 
 
 Set wrk = DBEngine(0)
 Set dbDAO = wrk.OpenDatabase(GetPath(PATH_DATA_DB, False)
 Set dbSrc = wrk.OpenDatabase(GetPath(PATH_DATA_HISTDB), False) 

>>> NOTE:   The Workspace Collection, now contains two databases (indexes 0 and 1).

However, if I change the above code by either:
1) Changing the object variable dbSrc to dbDAO
-OR_
2) Add a line (Set dbSrc = Nothing) AFTER the database dbSrc is added to the Collection

ONLY the first database (ie. dbDAO above), is included in the collection.

SO, in both cases, changing the object variable appears to delete the object from the collection and reduce the Count.

///////////////////////////////////////

Having two object variable does NOT get me anywhere -- UNLESS I can assign one object variable to another -- ALL current code procedures are hardcoded using dbDAO (e.g. dbDAO.OpenRecordset).

The only solutions I see are:
1) Modify a lot of code so I can pass into procedures the database object variable (ie. add a parameter),
OR
2) Come up with another workaround -- have an idea -- where no swapping of databases is needed.

[dz32]

you could make dbdao as a global placeholder and set it with the real db instance on demand.

probably harder to maintain but if it's just one developer and you remember in the future then ...

[vb6forever]

dz32: Thanks for responding.

Sadly, your suggestion in post #13, I believe, creates more complexity.

The database is split, where the primary holds all tables NOT data, and one or more secondary databases holding the data. Since each procedure uses dbDAO, I need to know which DB I'm using, so some type of tracking is needed -- which database is using which procedure. Naturally, I could be missing something with that logic.

[dz32]

the more you play with it the right solution will present itself just takes time and e experimentation