Code to export objects from one mdb to another

[Jim Pockmire]

I want to export all objects from one mdb to another. I can think of a way
to export tables and queries looping through the querydefs and tabledefs
collections, but what about forms, macros and modules? Is there an easy way
to do this? Note that the export to mdb contains additional objects such
that I cannot simply make a copy of the source mdb.

 

[BillCo]

there's not straight forward way to do this - in fact there's not way
to do this at all that I know of. but the chances are that if you think
about it you probably dont need to. what are you trying to achieve? the
odds are there is a more standard way of achieving this

 

[Jim Pockmire]

there's not straight forward way to do this - in fact there's not way
to do this at all that I know of. but the chances are that if you think
about it you probably dont need to. what are you trying to achieve? the
odds are there is a more standard way of achieving this

 

[Jim Pockmire]

This is a large database of several hundred reports and supporting objects.
I am creating new reports for a central location and I only want to send
them the new/revised objects. Rather than having them transferring the
objects manually from one mdb to another, I was looking for a way to
automate the procedure.

 

[Dirk Goldgar]

I want to export all objects from one mdb to another. I can think of
a way to export tables and queries looping through the querydefs and
tabledefs collections, but what about forms, macros and modules? Is
there an easy way to do this? Note that the export to mdb contains
additional objects such that I cannot simply make a copy of the
source mdb.

What version of Access? In Access 2000 or greater, you can loop through
the AllForms, AllReports, AllMacros, and AllModules collections of the
CurrentProject object. For each object (of type AccessObject) in the
collection, you can use DoCmd.TransferDatabase to export it. For
example,

Dim ao As AccessObject

For Each ao In CurrentProject.AllForms

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
"C:\Temp\Output.mdb", _
acForm, _
ao.Name, _
ao.Name

Next ao

 

[Jim Pockmire]

...excellent.

...

What version of Access? In Access 2000 or greater, you can loop through
the AllForms, AllReports, AllMacros, and AllModules collections of the
CurrentProject object. For each object (of type AccessObject) in the
collection, you can use DoCmd.TransferDatabase to export it. For
example,

Dim ao As AccessObject

For Each ao In CurrentProject.AllForms

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
"C:\Temp\Output.mdb", _
acForm, _
ao.Name, _
ao.Name

Next ao

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jim Pockmire]

I assume that I still need to loop through tabledefs and querydefs to
identify and export those objects.

 

[Jim Pockmire]

In your code below, can "ALLForms, AllMacros, etc. be used as variables in
"CurrentProject" to reuse the same code?

 

[Dirk Goldgar]

I assume that I still need to loop through tabledefs and querydefs to
identify and export those objects.

Yes; either that or use CurrentData.AllTables and
CurrentData.AllQueries. Note that, for tables and queries, it's the
CurrentData object, not CurrentProject, that contains the relevant
collections.

 

[Dirk Goldgar]

In your code below, can "ALLForms, AllMacros, etc. be used as
variables in "CurrentProject" to reuse the same code?

Not readily. The best I've been able to figure out is something like
this:

'----- start of code -----
Sub ExportObjects(ot As AcObjectType, OutFile As String)

Dim c As AllObjects
Dim ao As AccessObject

Select Case ot
Case acForm: Set c = CurrentProject.AllForms
Case acReport: Set c = CurrentProject.AllReports
Case acModule: Set c = CurrentProject.AllModules
Case acMacro: Set c = CurrentProject.AllMacros
Case acDataAccessPage: Set c = CurrentProject.AllDataAccessPages
Case acTable: Set c = CurrentData.AllTables
Case acQuery: Set c = CurrentData.AllQueries
Case Else
Err.Raise 5, "ListCollection", "Unsupported object type"
End Select

For Each ao In c

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
OutFile, _
ot, _
ao.Name, _
ao.Name

Next ao

Set c = Nothing

End Sub
'----- end of code -----

You'd then call it like this:

ExportObjects acForm, "C:\Temp\Output.mdb"
ExportObjects acReport, "C:\Temp\Output.mdb"
ExportObjects acModule, "C:\Temp\Output.mdb"
ExportObjects acMacro, "C:\Temp\Output.mdb"
ExportObjects acDataAccessPage, "C:\Temp\Output.mdb"
ExportObjects acTable, "C:\Temp\Output.mdb"
ExportObjects acQuery, "C:\Temp\Output.mdb"

 

[Dirk Goldgar]

Correction to code: you should change this line ...

Err.Raise 5, "ListCollection", "Unsupported object type"

.... to something like this ...

Err.Raise 5, "ExportObjects", "Unsupported object type"

.... so as to reflect the correct name of the procedure.