exporting the same msaccess objects to .....

Hi, sorry I may have been too succinct.

I am willing to explain further if need be....

Thanks

Take a look at the TransferDatabase command, which let's you move objects between databases.

Depending on the number of DB's involved and if this is a one time thing or not, you may want to:

1. Simply "hard code" all the exports in a VBA procedure.
2. Use a table and do something up a little more elaborate.   In the table, you might have the name of the object, along with the source and target DB's.  Then you procedure would loop through this and carry out the action.

 Many ways to structure this and it's hard to be more specific without knowing more details.

Jim.

By the way, here's an example of exporting a query from the current DB:

DoCmd.TransferDatabase  acExport, "Microsoft Access", "C:\myTargetDB.MDB", acQuery, "myquery", "myQuery"

  TransferDatabase will let you move forms, reports, queries, etc.

Jim.

"Hi, sorry I may have been too succinct"
Close to it, but we get the gist.

Now, to details.  As @Jim said, the VBA is DoCmd.TransferDatabase.  You are using A03, which has very good help (don't talk to me about the help in A2007+!)  How good are you with VBA?  As @Jim said, you could create a table that has each of the parameters for the job in it

expression.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
expression   Required. An expression that returns one of the objects in the Applies To list.
TransferType   Optional AcDataTransferType.
AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.
Note  The acLink transfer type is not supported for Microsoft Access projects (.adp).
DatabaseType  Optional Variant. A string expression that's the name of one of the types of databases you can use to import, export, or link data.
Types of databases: note: this argument is fulfilled by one of the strings below, in quotation marks
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBase IV
dBase 5.0
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases
WSS
In the Macro window, you can view the database types in the list for the Database Type action argument of the TransferDatabase action.
DatabaseName  Optional Variant. A string expression that's the full name, including the path, of the database you want to use to import, export, or link data.
ObjectType   Optional AcObjectType.
AcObjectType can be one of these AcObjectType constants.
acDataAccessPage
acDefault
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
acTable default
This is the type of object whose data you want to import, export, or link. You can specify an object other than acTable only if you are importing or exporting data between two Microsoft Access databases. If you are exporting the results of a Microsoft Access select query to another type of database, specify acTable for this argument.
If you leave this argument blank, the default constant (acTable) is assumed.
Note  The constant acDefault, which appears in the Auto List Members list for this argument, is invalid for this argument. You must choose one of the constants listed above.
Source   Optional Variant. A string expression that's the name of the object whose data you want to import, export, or link.
Destination   Optional Variant. A string expression that's the name of the imported, exported, or linked object in the destination database.
StructureOnly   Optional Variant. Use True (–1) to import or export only the structure of a database table. Use False (0) to import or export the structure of the table and its data. If you leave this argument blank, the default (False) is assumed.
StoreLogin   Optional Variant. Use True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.

And then code a loop based on a recordset drawn from that table.

Alternatively, if this is something you do occasionally and repeatedly (it sounds like you are creating new objects in a master or dev copy, and then pushing them out to production files!), a form could be built that would allow you to select all the needed parameters and then click a command button that would push out the objects.

Are all the exports going from a single database to many others?  Or are there exports from many databases going to many others?  That can be done, too but needs some extra steps.

It's all doable.

Thanks for responses...

I like the idea of looping ...

2. Use a table and do something up a little more elaborate.   In the table, you might have the name of the object, along with the source and target DB's.  Then you procedure would loop through this and carry out the action.

If I do this what would be the code for looping....

Presently I have created a small database with the objects I want to export to a number (forty of them) of dbs. these objects are
1 query
1 form
1 report
2 macros

If I now create a table with the location of all the dbs eg
field 1 - folder          -------------- c:\1\xxx1
field 2 - name of db ------------- db1.mdb

How could do a loop to export all five objects to every single db?  I have never done a loop before and I would be grateful if you could provide me/ assist me with the structure of the code.

Thanks

Hi,

Within my small database, I have now created a separate form with a button that will run an event procedure that hopefully will run my loop procedure.

I am now trying to figure out how to export all five objects from this small database across to all my other dbs (i.e. all my other mdbs)

Grateful for assistance.

<<How could do a loop to export all five objects to every single db?  >>

Would look something like this:

Public Sub CopyDatabaseObjects()

Dim db As DAO.Database
Dim rst as DAO.Recordset

On Error goto CopyDatabaseObjects_Error

Set db = Currentdb()
Set rst = db.OpenRecordset("myTable")

Do until rst.EOF
   
   ' Copy the objects
   DoCmd.TransferDatabase  acExport, "Microsoft Access",rst![Field1] & "\" & rst![Field2], acQuery, "myquery", "myQuery"
 
   ' Repeat Transferdatabase command as many times as needed to copy the objects.

  ' Move to next database
   rst.MoveNext
Loop

CopyDatabaseObjects_Exit:
   On Error resume Next

   rst.close
   Set rst = nothing

   set db = nothing

   Exit Sub

CopyDatabaseObjects_Error:
  MsgBox "unexpected error " & err.number & " - " & err.description
  Resume CopyDatabaseObjects_Exit

You would paste that code into a standard module, and in the OnClick of your button, do:

  Call CopyDatabaseObjects

Jim.

Hi,

When I do all that and I call the module as written above , from the event procedure it is extecting a event proocedure and wont call the module!!??!!

help please.

Thanks

I am getting.....

Compile error

Expected variable or procedure, not module

I am using , believe or not MS Access 97      :)

how do i call the sub routine?

:(

the event procedure on the on Click runs a Private Sub

This is what I have:

Private Sub Command8_Click()
Call CopyDatabaseObjects
End Sub

When I press the button on the form the following error appears

Compile error:
Expected variable or procedure, not module


Is there something to do about Public or Private??

Hi,

As suggested when I compile the module everything seems ok.....

Thanks for the continued support

any clues why it doesnt call the sub routine?

Hi all,


Using MSAccess 97, so far I have the following:

A Module called  - mdlCopyDatabaseObjects  with the following code:-

Option Compare Database
Option Explicit

Public Sub CopyDatabaseObjects()

Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo CopyDatabaseObjects_Error

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_Folders")

Do Until rst.EOF

'copy the objects
DoCmd.TransferDatabase acExport, "Microsoft Access", rst![folder] & rst![db], acQuery, "Qry_EoI", "Qry_EoI1"

'repeat export for every database
' move to next database
rst.MoveNext

Loop

CopyDatabaseObjects_Exit:
    On Error Resume Next
   
    rst.Close
    Set rst = Nothing
   
    Set db = Nothing
   
    Exit Sub
   
CopyDatabaseObjects_Error:
   
    MsgBox "unexpected error" & err.Number & "-" & err.Description
    Resume CopyDatabaseObjects_Exit

End Sub


I then have a Form titled Frm_Transfer with a button on it with the following code as part  of the event procedure on the On Click option:-



Private Sub Command8_Click()
Call mdlCopyDatabaseObjects
End Sub

The whole process is this sample test is to copy a particular object, in this case a query, across a number of databases as specified in a table titled tbl_Folders

Somehow this whole process is not working and I am getting the following error.  (see attached)

Could someone please assist me….Thanks
sample.doc

You don't call the name of the module.  You call the procedure inside the module.

Ron

hi,

When I do that I get

Compile Error
Ambiguous name detected: CopyDatabaseObjects


:(

That means you have another procedure in your database that you also named CopyDatabaseObjects.  Make sure there is only one with this name.

but they are!!!

my module is called mdl_CopyDatabaseObjects

and my procedure is CopyDatabaseObjects

???

Can Someone check my code, please....

found them.... i had a copy of the module  which I had kept as a backup....  :(

Thanks guys ....got it to work....

excellent