I try to back up all my tables to another DB with below code, it's work for for some certain number of tables only. After a while "transfer object method" was locked and won't unlocked further. Then coming out the below message

Code:

Dim BUpath As String
Dim obj As AccessObject, dbs As Object, PRname As String

BUpath = Application.CurrentProject.Path & "\Ritcha BU.mdb"
MsgBox "Already Back up Table at " & BUpath

Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
PRname = obj.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", BUpath, acTable, PRname, PRname, False
Next obj


After transfer around 10-13 tables then it come out the following message

"The database engine could not lock table 'MSysAccessObjects' because it is already in use by person or another process"

If someone could know code to unlock it, please help advise

Thks so much for your help

I found reason of locked already.

Access normally have hidden table called SYSTEM Objects (can see by go to Tools > Option > View and mark "System Objects" to unleash it), that not allowed to be deleted or replace.

Once we try to transfer tables by coding, all these system tables also being transferred and stucked there as it cannot go to replace the existing system tables in our backup DB.

So I just solve it easily by check IF to cross such a system table per below coding, and that's it.


Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim BUpath As String
Dim obj As AccessObject, dbs As Object, PRname As String

BUpath = Application.CurrentProject.Path & "\Ritcha BU.mdb"
MsgBox "Program will start Back up Tables at " & BUpath

Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
PRname = obj.Name
If PRname <> "MSysAccessObjects" And PRname <> "MSysACEs" And PRname <> "MSysACEs" _
And PRname <> "MSysObjects" And PRname <> "MSysQueries" And PRname <> "MSysRelationships" Then
DoCmd.TransferDatabase acExport, "Microsoft Access", BUpath, acTable, PRname, PRname, False
End If
Next obj

MsgBox "All Tables on Database already backed up"

Exit_Command33_Click:
Exit Sub
Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub