Transfering Databases Command Question

[ChristineRose]

I am trying to transfer about 20 Reports from one database to another. I need to use this command since I am using a form with a prompt to which database to export to (I have five different databases but all of the reports will need to be transferred to each ). Is there a simple way to do this in code without having to write out for each report:

DoCmd.TransferDatabase acExport, "Microsoft Access", path name of destination, Objecttype(acReport), "source report name", "destination report name"

Is there anyway to "select all" from my source database when transferring? Any advice is greatly appreciated! Thanks!!!

 

[THoey]

I am not sure if this is a one time transfer or not, but if it is, couldn't you just select the reports you want to copy, do a <CTRL-C> to copy them, open the other database and then do a <CTRL-V> to paste them?

If this is going to be a re-occurring thing, then doing it with code like you are trying to do is best... Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[ChristineRose]

Thanks.. Yes, this is going to be a reoccurring transfer.

 

[ErikZ]

I'd use a Macro to do what you're doing. I don't know if it's possible to use a wildcard to select reports in this function, but my gut feeling is 'no'. Do you suspect report names will change in the future? If so, then you'll have to find a way to &quot;Select All&quot; reports.

Later,
ErikZ

 

[ChristineRose]

Still Need help...

I wrote my code to transfer all my queries and report. All my queries export fine but if I export more than one report I get an illegal error and program shuts down. I don't understand why. Any suggestions... I am trying to get all my queries, forms, reports, macros and modules from a central database and put them into five different databases. Since changes are reoccurring to my forms, queries and reports, I want a central location to make those changes then export them to the five databases that use them versus making the change in five different places. Any help would be GREATLY appreciated. Thank you!!

 

[Luxor]

I'm just working on a similar problem, I think this might work:

create a query (qryMSysObjects) on the MSysObjects table with the following fields:

- Name
- Type
- Flags

Add the following criteria:

To Type: 5 (Queries)
Or: -32768 (Forms)
Or: -32764 (Reports)
Or: -32761 (Modules)
Or: -32766 (Macros)
to avoid tables/links to be selected

To Flags 0
to avoid system objects to be selected.

Open a recordset in VB:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;qryMSysObjects&quot

Process the recordset:

fileTo = getTargetFile() 'Function to get targetdatabase, like: 'C:\test.mdb', or '\\server\test.mdb')

If Not rst.EOF Then
rst.MoveFirst
While Not rst.EOF
Select Case rst!Type
Case 5
acType = 1
Case -32768
acType = 2
Case -32764
acType = 3
Case -32766
acType = 4
Case -32761
acType = 5
End Select
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, fileTo, acType, rst!Name, rst!Name
rst.MoveNext
Wend
End If
rst.close
Set dbs = Nothing

I'm still working on this myself, but I think this will do the job.

I hope I have helped a little.