Databases Reference
In-Depth Information
str = ""
For Each db In DBEngine.Workspaces(0).Databases
str = str & vbCrLf & db.Name
Next db
MsgBox "Db Names: " & vbCrLf & str
dbExtra.Close
End Sub
If each call to
CurrentDb
produces a pointer to a new object, then it is natural to wonder
what happens if we change the object pointed to by one of these pointers. Does it affect
the other objects? What about
DBEngine(0)(0)
? Consider the code in Example 14-14,
which does the following:
Creates two
Database
object variables
dbOne
and
dbTwo
and sets both equal to
CurrentDb
•
Adds a new field
NewField1
to the BOOKS table using
dbOne
•
Adds a new field
NewField2
to the BOOKS table using
dbTwo
•
Displays the list of fields for BOOKS using
dbOne
•
Displays the list of fields for BOOKS using
dbTwo
•
Closes
dbOne
and
dbTwo
; that is, it removes their objects from the
Databases
collection
•
Example 14-14. The dbOne and dbTwo variable example
Sub exaCurrentDb2( )
Dim dbOne As Database, dbTwo As DATABASE
Dim fldNew As Field
Dim str As String
Set dbOne = CurrentDb
Set dbTwo = CurrentDb
' Get field list in BOOKS
str = "Fields before: " & vbCrLf
''MsgBox dbOne.TableDefs!Books.Fields.Count
For Each fldNew In dbOne.TableDefs!Books.Fields
str = str & fldNew.Name & vbCrLf
Next
' Use dbOne to add a new field to BOOKS
Set fldNew = dbOne.TableDefs!Books.CreateField("NewField1", dbInteger)
dbOne.TableDefs!Books.Fields.Append fldNew
' Use dbTwo to add a new field to BOOKS
Set fldNew = dbTwo.TableDefs!Books.CreateField("NewField2", dbInteger)
dbTwo.TableDefs!Books.Fields.Append fldNew
''Stop - (see the explanation in the text)
' Refresh Fields collection using dbOne!!!