Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Modules / DAO / VBA / December 2006

Tip: Looking for answers? Try searching our database.

Code to export objects from one mdb to another

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jim Pockmire - 01 Dec 2006 15:25 GMT
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.
Reply to this Message
BillCo - 01 Dec 2006 16:36 GMT
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

> 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.
Reply to this Message
Jim Pockmire - 01 Dec 2006 16:54 GMT
> 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
[quoted text clipped - 8 lines]
>> to do this? Note that the export to mdb contains additional objects such
>> that I cannot simply make a copy of the source mdb.
Reply to this Message
Jim Pockmire - 01 Dec 2006 16:59 GMT
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.

> 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
[quoted text clipped - 8 lines]
>> to do this? Note that the export to mdb contains additional objects such
>> that I cannot simply make a copy of the source mdb.
Reply to this Message
Dirk Goldgar - 01 Dec 2006 17:05 GMT
> 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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message
Jim Pockmire - 01 Dec 2006 18:10 GMT
..excellent.

..

>> 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
[quoted text clipped - 22 lines]
>
>    Next ao
Reply to this Message
Jim Pockmire - 01 Dec 2006 19:42 GMT
I assume that I still need to loop through tabledefs and querydefs to
identify and export those objects.

>> 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
[quoted text clipped - 22 lines]
>
>    Next ao
Reply to this Message
Dirk Goldgar - 03 Dec 2006 18:46 GMT
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message
Jim Pockmire - 01 Dec 2006 19:55 GMT
In your code below, can "ALLForms, AllMacros, etc. be used as variables in
"CurrentProject" to reuse the same code?

>> 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
[quoted text clipped - 22 lines]
>
>    Next ao
Reply to this Message
Dirk Goldgar - 03 Dec 2006 19:11 GMT
> 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"

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message
Dirk Goldgar - 03 Dec 2006 19:19 GMT
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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.