Solved

exporting the same msaccess objects to .....

Posted on 2014-10-16
26
184 Views
Last Modified: 2014-10-19
Dear All,

Grateful for assistance.

I find myself in a situation where I have to export  the same Query, Form, Report and Macro to a number of similar MSaccess databases (mdbs) which are all held in different folders. However I could house all these databases in the same folder.

I would appreciate if anyone could provide the code to carry out this task....In short I would like to automate this laborious export or import (depending from which view).

I am using A97 and A03

Thanks
0
Comment
Question by:PipMic
  • 15
  • 6
  • 3
  • +1
26 Comments
 

Author Comment

by:PipMic
ID: 40385443
Hi, sorry I may have been too succinct.

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

Thanks
0
 
LVL 57
ID: 40386464
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.
0
 
LVL 57
ID: 40386469
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40387091
"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.
0
 

Author Comment

by:PipMic
ID: 40388076
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
0
 

Author Comment

by:PipMic
ID: 40388617
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.
0
 
LVL 57
ID: 40388757
<<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
0
 
LVL 57
ID: 40388759
You would paste that code into a standard module, and in the OnClick of your button, do:

  Call CopyDatabaseObjects

Jim.
0
 

Author Comment

by:PipMic
ID: 40389062
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
0
 

Author Comment

by:PipMic
ID: 40389086
I am getting.....

Compile error

Expected variable or procedure, not module
0
 

Author Comment

by:PipMic
ID: 40389089
I am using , believe or not MS Access 97      :)
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 40389189
I think it's just the way you pasted it in.

I noticed I didn't put an "End Sub" there as the last line (VBA normally fills it in).   So do this:

1. Delete the code you added.

2. Do a compile to make sure you have no errors.   You will need to temporarily comment out the call on your button.

3. From the database container, go to Modules, then click the new button.

4. Type:

 Public Sub CopyDatabaseObjects()

an "End Sub" should appear after you hit return.

5. Now from the earlier comment, copy and paste everything but that first line, which you've already typed and paste it in between the Public Sub and End Sub lines.

6. Make sure the app compiles.

7. Now uncomment the call in your button code and try compiling again.

Jim.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:PipMic
ID: 40389212
how do i call the sub routine?

:(
0
 

Author Comment

by:PipMic
ID: 40389226
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??
0
 

Author Comment

by:PipMic
ID: 40389228
Hi,

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

Thanks for the continued support
0
 

Author Comment

by:PipMic
ID: 40389276
any clues why it doesnt call the sub routine?
0
 

Author Comment

by:PipMic
ID: 40390055
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
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40390088
You don't call the name of the module.  You call the procedure inside the module.
Call CopyDatabaseObjects

Ron
0
 

Author Comment

by:PipMic
ID: 40390113
hi,

When I do that I get

Compile Error
Ambiguous name detected: CopyDatabaseObjects


:(
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40390119
That means you have another procedure in your database that you also named CopyDatabaseObjects.  Make sure there is only one with this name.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 40390151
Make sure your not naming the module the same either.  All module and procedure names must be unique.

Jim.
0
 

Author Comment

by:PipMic
ID: 40390210
but they are!!!

my module is called mdl_CopyDatabaseObjects

and my procedure is CopyDatabaseObjects

???

Can Someone check my code, please....
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 100 total points
ID: 40390279
Try doing a search (CTRL-F) throughout the database for CopyDatabaseObjects
0
 

Author Comment

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

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

Author Closing Comment

by:PipMic
ID: 40390368
excellent
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now