TransferDatabase help

Question

I have a MS Database that when I close the database, I want to export the main data table to a back-up database. Here is my VBA script -

destfilename = "PGA Matrix 6-2022 - REP VERSION.mdb"
destfileloc = "Z:\CSC\MMOORE\ACCESS\Backup\"
DoCmd.TransferDatabase acExport, "Microsoft Access", destfilename, acTable, "Main Data", destfileloc, False

This fails every time. Not sure what I'm doing wrong?

Answer 1

Hi,

You should always insert here the exact code you tried, the error number and the message you received. Otherwise it is difficult to find out where the problem is.

Following your latest information, syntactically this should work:

Dim strDestFullPath As String

strDestFullPath = "Z:\CSC\MMOORE\ACCESS\Backup\PGA MATRIX 6-2022 Rep Version.accdb"

DoCmd.TransferDatabase acExport, "Microsoft Access", strDestFullPath, acTable, "Main Data", "Main Data"

If you still get an error it could e.g. be because the target drive is not accessible for Access. If you get something like that, test the code with a safe reachable path on C. If it works there and not with Z, then the problem is the access to the drive.

Servus
Karl
Access News
Access DevCon

Answer 2

Thank you. The error I receive is a Run-time error: '3024'
Could not find file "C;\users\system\documents\PGA Matrix 6-2022 - rep version.mdb"

The file above should be my destination file? If so, I do not want it on my hard drive, but
my cloud drive which is in the code I mentioned.

I have read over the documentation, but it does not provide the information for export from
one file to another. I am just trying to figure this out. Thank you!

Answer 3

For a backup do a file operation to copy the back end file to your OneDrive folder. You might like to take a look at Backup.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes an option to backup on close. In my case this is to an external local drive, but it can easily be changed to OneDrive by changing 'BackUpPath' to 'OneDrivePath' in the GetBackUpPath function:

Public Function GetBackUpPath() As Variant

GetBackUpPath = DLookup("BackUpPath", "FileLocations")  
  

End Function

I use this routine myself, and in my accounts database have added some code which writes the current data to a set of reports in the specified OneDrive folder. This allows me to see the current state of my accounts on my iPhone whenever and wherever I might need to.

Answer 4

I have solved 1/2 of my problem (i think). Its the other half that I cannot seem to figure out. When I worked on this with the help I have received it seems to
work now with this being the exception - The "Main Data" table is being copied right back into my main database and NOT the database on the
cloud drive. Here is an update to my code -

destfilename = "C:\Users\Mikemoo\Desktop\PGA Matrix.accdb"
destfileloc = "Z:\CSC\MMOORE\ACCESS\Backup\PGA MATRIX 6-2022 - REP VERSION"

DoCmd.TransferDatabase acExport, "Microsoft Access", destfilename, acTable, "Main Data", destfileloc, False

When this runs it creates a new table in my current database as: Z:\CSC\MMOORE\ACCESS\Backup\PGA MATRIX 6-2022 - REP VERSION
So, it appears it is not going to the "Z:\" drive and placing the table in the "PGA MATRIX 6-2022 - REP VERSION" database.

All I want to do is take the table "Main Data" from the current database and copy it over to the "Main Data" on my share drive.
Any clues as to what I'm missing here?

Answer 5

Hi,

It's still not clear, what you want to achieve because of your two variables destfilename and destfileloc.

You would need sth like:

strDestFullPath = "Z:\CSC\MMOORE\ACCESS\Backup\PGA MATRIX 6-2022 - REP VERSION\PGA Matrix.accdb"

or wherever and whatever the destination file is. And then:

DoCmd.TransferDatabase acExport, "Microsoft Access", strDestFullPath, acTable, "Main Data", "Main Data"

Where the second "Main Data" is the name you want for the table copy in the destination database. This name can be different.

Servus
Karl
Access News
Access DevCon