Transferring Queries Question

[Tom]

I am running an Access 2K database in Access 2003.

I use the TransferDatabase command inside an Autoexec Macro to transfer /
update various Objects in the same database on other company Laptops. The
database has some 100 queries I had to update and now I need to update them
in the other databases.

Is there a way or a single command to use so I can get Access to copy ALL
of the queries in one database to another database? Can wildcards work
inside the TransferDatabase command or do I need some VB Code to do the
trick?

TIA!

 

[Roger Carlson]

You could do it with VB code, but have you tried Tony Toew's Auto FE
Updater? http://www.granite.ab.ca/access/autofe.htm

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Here is a way to spin through all the queries in your database. If you want
to copy all the queries, this should do fine. If you need to filter some
out, you will have to do that using some name matching or a temp table with
all the ones to copy and a Dlookup. But, this should get you started:

Dim objQdefs As QueryDefs
Dim qdf As QueryDef
Dim strQryName As String

Set objQdefs = CurrentDb.QueryDefs
For Each qdf In objQdefs
strQryName = qdf.Name
Docmd.TransferDatabase.......
Next qdf

 

[Tom]

Roger,

Took a look at Tony's site. This may be more than I need and admittedly
some of his stuff is over my head.

I might be able to work with someone at my company to upload a file to a
server location, but then maybe not. What I typically do is create an
update inside a MDB file and then build an Autoexec using the
TransferDatabase command. I also add tables and build queries to update
tables in the main MDB file.

Is there some VB Code in a can somewhere that I can boiler plate to copy
large numbers of Objects to another MDB file?

Thanks

Tom

 

[Roger Carlson]

You have to loop through the objects as Dave suggests.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tom]

Klatuu,

I wish I knew VB Code, but I don't. I do not know how to complete the
TransferDatabase line. I tried to let the system help me, but can't get
it. I just don't know the language or the syntax (

I usually set up the TransferDatabase command to:
Export
Microsoft Access
Database Name is: C:\BPI Changeover\BPI Changeover Program.mdb
Object Type is: Query
Source Name: Query Name
Destination Name: Query Name
Structure Only: No

Thanks!

 

[Tom]

I got it to work using the Help Function and seeing the proper Syntax for
the command. Tested it and it work )

Thanks again!!!!!!!!!!!

 

[Tom]

How would I do the same thing for other Object, e.g. Reports or Macros?

Thanks