TransferDatabase acExport

[gblack]

I have a central database which has links to some main tables in other databases. My boss wants me to export those tables to another mdb file each month so we can back up all the main tables we use.

What I want to do is automate this process so that I can go into a form and press a button and have Access spit all those tables to said .mdb file.

I set up some code to do this, but the problem is that Access will only tranfer the link... if the table in the database is linked. How can I get Access to transfer the table structure instead of the link?

For example: "FIDM_Collections" is actually a linked table in this database. What I want is to transfer the actual table to Central.mdb not just the link. Here's my code:

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"J:\ocseuser\Public\Special Collections\MI Report Tool\Central Database\Central.mdb", _
acTable, "FIDM_Collections", "FIDM_Collections", False

 

[llkhoutx]

If the table to be exported is linked, used a make table query to tranfer that data to a table in the instant mdb, then export that table.

Maybe you could merely export a query to a table. I've never tried that.

 

[Jack Skeleton]

What you want is possible but HARD an COMPLICATED to do... if you want to know my opinion, the easiest way to do it is this one:

Dim mysql as string

mysql = "SELECT * INTO [FIDM_Collections] IN _ 
'J:\ocseuser\Public\Special Collections\MI Report Tool\Central Database\Central.mdb' _ 
FROM [FIDM_Collections]"

Docmd.runsql mysql

Enjoy it!

 

[gblack]

Thanks

Too bad you weren't around a few days ago Jack. I ended up getting it done here's what I did.

'This opens the password protected database ("FO CSU Comm Log") and then uses the Docmd
'to export the tables I want into Central.mdb.
'Because the password protected database also had a password form ("SignOn")
'I had to open and close a few forms before I was able to close the application

Dim db As Database
Dim oAcc As Access.Application

Const TMP = "J:\ocseuser\Public\Special Collections\CSU Call Log\FO CSU Comm Log.mdb"

Set oAcc = New Access.Application
Set db = oAcc.DBEngine.OpenDatabase(TMP, _
False, False, ";PWD=Password")
oAcc.OpenCurrentDatabase TMP
oAcc.DoCmd.Close acForm, "SignOn"
oAcc.DoCmd.OpenForm "frmMain", acNormal
oAcc.DoCmd.Close acForm, "frmMain"
oAcc.DoCmd.TransferDatabase acExport, "Microsoft Access" _
, "J:\ocseuser\Public\Special Collections\MI Report Tool\Central Database\Central.mdb" _
, acTable, "FIDM_Collections", "FIDM_Collections", False

db.Close
oAcc.CloseCurrentDatabase
oAcc.Quit acExit
Set oAcc = Nothing
Set db = CurrentDb
db.Close

 

[Jack Skeleton]

Hi,
I'm sorry for being too late (I cannot dedicate too time to answer questions...). But you got a working solution yourself, that's great. Anyway, if you have to do this task frequently, I think that could help you:

You can use SQL instructions also for protected databases. To copy a table from a protected database to another protected database use this SQL structure:

"SELECT * INTO [DESTINATIONDATABASESTRING].[DESTINATIONTABLENAME] FROM [SOURCEDATABASESTRING].[SOURCETABLENAME];"

being both "database strings" (source and destination) like this one:

"MS Access;PWD=" & (database password) & ";DATABASE=" & (full path and database name)

Easy, isn'it?. Arriving to this level of easiness took me weeks...

 

[gblack]

Wow

Pretty nice... I'll remember that for next time

Thanks
G

 

[Gui]

Yes really easy and nice . Thanks