How about a ReportDef or ModuleDef???

[dBjason]

Access 2003

Hello,

Well, I opened my mouth and got handed a task. Here's what I'm trying to do:

I'm trying to import all objects from one Access database to another. This works great with tables and queries, as I'm able to reference the TableDef and QueryDef objects and iterate through them (see code below). The transfer routines are a little cumbersome but work OK.

'Import Tables
For Each tdf In dbsImport.TableDefs
    If Not IsSystemObject(tdf.Name) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", NewDatabaseFile, acTable, tdf.Name, tdf.Name, False, False
    End If
    DoEvents
Next

So yeah, I can get all the tables and queries from one database to the current. No Prob. But now they want goodies like Forms, Reports, Pages, Macros, and Modules as well.

Now I know the DoCmd.TransferDatabase will pull these out no problem, but it needs an object name to reference in order to grab it. Without a collection container (like the TableDefs object), I have no way of iterating through the remote databases objects and pulling them out.

I need like uh.... a ModuleDefs collection!!

<somebody please help!>


Thanks in advance,
Jason

 

[PHV]

You may interrogate the remote MSysObjects table.
In a standard code module create the following function:

Public Function basObjectNames(ObjType As Long) As String
Dim tmp As String
Select Case ObjType
Case 1
  tmp = "Table"
Case 2
  tmp = "Database"
Case 3
  tmp = "Collection"
Case 4, 6
  tmp = "Linked Table"
Case 5
  tmp = "Query"
Case 8
  tmp = "Relationship"
Case -32768
  tmp = "Form"
Case -32766
  tmp = "Macro"
Case -32764
  tmp = "Report"
Case -32761
  tmp = "Module"
Case Else
  tmp = "Unknown"
End Select
basObjectNames = tmp
End Function

Now you may use the following SQL to open a recordset:

SELECT Name, Type, basObjectNames([Type]) AS MyType
FROM MsysObjects
WHERE Left([Name],1)<>'~' AND Left([Name],4)<>'Msys'
ORDER BY 3,1

Another way is to play with OLE automation:
instantiate a new Access.Application object
use its OpenCurrentDatabase method
play with the AllXXXX collections of its CurrentProject property

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dBjason]

Thanks!