Help ! DBEngine reference not working... 1

[marco02]

Hi

I need to work with tables in two different database,
I can't manage to refer to my second database,
Both databases are open...

#DAO declarations
Dim db1 as DAO.Database
Dim db2 as DAO.Database

# this line is OK
Set db1 = DBEngine.Workspaces(0).Databases(0)

# this line BUGS
Set db2 = DBEngine.Workspaces(0).Databases(1)

Why doesn't he allows me to refer to the second database ?

thks for your help!

marco02

 

[CCLINT]

Databases is a collection. As in every collection, items do not exist until added to the collection. And the db will not get added to the collection until an object is set to the opened Database. Obviously, there is one database already opened by another object and therefore already in the Databases collection.
The second one doesn't exist. It needs to be created first.


Dim db3 As DAO.Database
Dim db4 As DAO.Database
Dim db5 As DAO.Database
Dim db6 As DAO.Database

Set db3 = DBEngine.Workspaces(0).OpenDatabase("C:\Data\Db1.Mdb&quot

'Now that a database was opened, and automatically added to the Databases collection, I can reference it with a second database object.

Set db4 = DBEngine.Workspaces(0).Databases(0)

'This will not work until another database connection is made:

Set db6 = DBEngine.Workspaces(0).Databases(1)

'So let's make another connection

Set db5 = DBEngine.Workspaces(0).OpenDatabase("C:\Data\Db1.Mdb&quot

'So now I can set another database object to the 2nd database in the Databases collection:

Set db6 = DBEngine.Workspaces(0).Databases(1)



'If you are just trying to create a 2nd database object on the same database then just do the following:

Dim db1 As DAO.Database
Dim db2 As DAO.Database

Set db1 = DBEngine.Workspaces(0).OpenDatabase("C:\Data\Db1.Mdb&quot

Set db2 = db1

'Or:
'Set db2 = DBEngine.Workspaces(0).Databases(0)

'Now you have 2 database objects set to the same database and can run independent actions on the same db using the 2 database objects.

 

[marco02]

Thanks CClint! Very clear explanation thank you, everything works fine now.

...except now that i can't manage to append a Field to an already existing table ...

Set fld = tbl.CreateField("FieldName", dbText, 30)
tbl.Fields.Append fld

bugs,... Error tells me : "The database engine could not lock table "TableName" because it is already in use by another person or process"

But that's not the case. Except setting the table
[Set tbl = dbs.TableDefs("TableName&quot] I didn't do anything with this table nor is anybody playing with it.

What is the pb really ?

 

[CCLINT]

See my response at the bottom of the page thread709-292581