JimS said:
I want to walk through the Allforms collection (and the querydefs, etc
collections) from one db("object") to another("source") (both A2007). I
want
to use the last modified date from the source db to populate a listbox on
the
object db. I'll then ask the user to select the forms (queries, etc.) he
wants to transfer to the object db. Similar to the Access Wizard, except
the
Access Import wizard doesn't tell me anything about the forms, queries,
etc.
I'm transferring.
Anyway, how do I refer to the forms collection on another database using
vba? It appears I use the "OpenDatabase" method, but I haven't gotten much
farther.
You can't get at the "All..." collections of another database without
opening the database in an instance of Access, because those collections are
properties of the CurrentProject and CurrentData objects, which are in turn
properties of the Access Application object. If you must, you can open the
other database in a separate instance of Access, and get at the collections
in that instance by automation. For this approach, you would not be using
the DAO OpenDatabase method. But it's a lot of overhead to incur for what
you want to do.
Instead, you can use the OpenDatabase method to open a DAO Database object,
and use various collections belonging to the Database object to get the
information you want. It's not quite as tidy as the All... collections,
because the appropriate collections vary according to the type of object.
Suppose you wanted to build a list of all the tables, queries, forms,
reports, macros, and modules in a database. Suppose you have a text box
named "txtDBPath" on a form, where the user has entered the path to the
database in question. Suppose you also have a list box named "lstObjects",
with three columns for the object type, object name, and last-updated date
of each object. Then you might have a function like this to list the
objects in the database:
'------ start of code ------
Function ListObjects()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim cnt As DAO.Container
Dim doc As DAO.Document
Dim strObjType As String
Dim strObjName As String
Dim strObjDate As String
' Clear the list box, to start with.
Me.lstObjects.RowSource = ""
If IsNull(Me.txtDBPath) Then
Exit Function
End If
DoCmd.Hourglass True
Set db = DBEngine.OpenDatabase(Me.txtDBPath, , True)
' Add all tables to the list box.
strObjType = "Table"
For Each tdf In db.TableDefs
strObjName = tdf.Name
If Left(strObjName, 4) <> "MSys" Then
strObjDate = Format(tdf.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
End If
Next tdf
' Add all queries to the list box.
strObjType = "Query"
For Each qdf In db.QueryDefs
strObjName = qdf.Name
If Left(strObjName, 3) <> "~sq" Then
strObjDate = Format(qdf.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
End If
Next qdf
' Add all forms to the list box.
strObjType = "Form"
Set cnt = db.Containers("Forms")
For Each doc In cnt.Documents
strObjName = doc.Name
strObjDate = Format(doc.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
Next doc
Set cnt = Nothing
' Add all reports to the list box.
strObjType = "Report"
Set cnt = db.Containers("Reports")
For Each doc In cnt.Documents
strObjName = doc.Name
strObjDate = Format(doc.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
Next doc
Set cnt = Nothing
' Add all macros to the list box.
strObjType = "Macro"
Set cnt = db.Containers("Scripts")
For Each doc In cnt.Documents
strObjName = doc.Name
strObjDate = Format(doc.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
Next doc
Set cnt = Nothing
' Add all modules to the list box.
strObjType = "Module"
Set cnt = db.Containers("Modules")
For Each doc In cnt.Documents
strObjName = doc.Name
strObjDate = Format(doc.LastUpdated, "short date")
Me.lstObjects.AddItem _
strObjType & ";" & strObjName & ";" & strObjDate
Next doc
Set cnt = Nothing
Exit_Point:
On Error Resume Next
DoCmd.Hourglass False
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Function
Err_Handler:
DoCmd.Hourglass False
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Function
'------ end of code ------
You could call the function from the AfterUpdate event of the text box.
