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?

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

Thanks Carl. That did not work either. So, to recap everything and to make this a little easier on everyone. I have a database on my hard drive. The location of this database is on the desktop. The name of this database in PGA Matrix.accdb. Here is the exact location: C:\Users\Mikemoo\Desktop\PGA Matrix.accdb Within this database is a table called "Main Data" Upon exit of this database, I want to copy this table and ONLY this table to a network drive into another MS Access database called "PGA MATRIX 6-2022 Rep Version.accdb" The location of this database is here: Z:\CSC\MMOORE\ACCESS\Backup\ So, can someone tell me how to write the DoCmd.TransferDatabase function to do this? I have tried about every combination I can think of and I am just in need of a sample and I'm good to go. Thank you in Advance!

Karl - That did the trick. Thank you so very much! I'm good!

TransferDatabase help - Microsoft Q&A

Answer accepted by question author

Karl Donaubauer 2,691 Reputation points MVP

2022-06-15T08:07:59.363+00:00

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
Please sign in to rate this answer.
Moore, Mike [EMR/MSOL/HSTN] 0 Reputation points

2025-09-05T18:34:57.7+00:00

After several months of usage, I went and looked at my back-up database. Access has created a new table every time, aka Maindata1, Maindata2, etc. I just want it to overwrite the main file.

Here is the code from by VBA files -

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

Where destfilename = destfilename = "Z:\General\Mike M\ACCESS\PGA Matrix - sales version.accde"

Any clues on how to get it to just over write the files instead of creating new ones?

Karl Donaubauer 2,691 Reputation points MVP

2025-09-05T20:36:02.63+00:00

Hi,

Any clues on how to get it to just over write the files instead of creating new ones?

The question is rather how you manage to not do that. The default behavior in all versions of Access is that the table is overwritten. I just tested it again with your code.

Servus
Karl

Access Forever, News, DevCon
Access-Entwickler-Konferenz AEK

Michael Moore 21 Reputation points

2025-09-06T10:54:05.7966667+00:00

I understand, but it seems to create new tables everytime. I want to prevent this. I just want it to overwrite the current files

Ken Sheridan 3,546 Reputation points

2025-09-06T11:21:52.6766667+00:00

Another option would be to open the backup file and pull the data in from the source file, rather than pushing it. You could then drop the backup table before importing the data. This could be done with the TransferDatabase method. Or alternatively as follows:

1. Create a link in the backup file to the table in question.

2. Create an empty copy of the source table as the backup table in the backup file.

3. Create a little function which firstly empties the backup file, then inserts all rows from the linked table.

The following code is an example, using the Orders table from Northwind:

Public Sub BackupOrders() On Error GoTo Err_Handler Dim strSQL As String Dim strCriteria As String DBEngine.BeginTrans ' empty Orders_bak table strSQL = "DELETE * FROM Orders_bak" CurrentDb.Execute strSQL, dbFailOnError ' insert rows from Orders_lnk into Orders_bak strSQL = "INSERT INTO Orders_bak SELECT * FROM Orders_lnk" CurrentDb.Execute strSQL, dbFailOnError DBEngine.CommitTrans Exit_Here: Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" DBEngine.Rollback Resume Exit_Here End Sub

Rajiv. Kumar Jaiswal 0 Reputation points

2025-09-06T15:45:14.8166667+00:00

R k

Michael Moore 21 Reputation points

2025-09-06T15:52:19.77+00:00

Thanks Ken. The problem there is the master database is on my desktop, and the slave is on a network drive. So, I need to push the data, as my computer may not always be on. So, I’m thinking I may just copy the master over to the network drive. It may help. I’ll find out monday

Ken Sheridan 3,546 Reputation points

2025-09-06T20:38:40.11+00:00

Before calling the TransferDatabase method you could drop the existing backup table:

Dim dbs as DAO.Database Dim strSQL As String Set dbs = OpenDatabase(PathToBackupFileGoesHere) strSQL = "DROP TABLE NameOfTableGoesHere" dbs.Execute strSQL, dbFailOnError code to call TransferDatabase method goes here

Moore, Mike [EMR/MSOL/HSTN] 0 Reputation points

2025-09-12T11:25:04.5566667+00:00

Ken -

I tried taking your last suggestion, and Access still creates additional tables and does not over write them. I think the issue just maybe my version of Access in connection with our company security protocols, etc. I'm at wits end here and I think I will just manually copy the tables over. Thank you for all your effort in this matter.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

TransferDatabase help

6 additional answers

Your answer