TransferDatabase acExport produces code 2501

[Softop]

Due to a change in versions of Access by Users I am converting existing code from Data Access Objects (DAO) to conform to ActiveX Data Objects (ADO). All is working well except one thing which is twisting my brain.

I want to export all tables, a form, a macro and a module to a new database using VBA script. Here is the code...

Private Sub cmdBackup_Click()
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim appPath As String
    Dim tablename As String
    Dim cat As New ADOX.Catalog

    On Error GoTo FileErr

    'Delete old database file if it exists

    If Dir(txtPath.Value) <> &quot;&quot; Then
        Kill txtPath.Value
    End If

    'Create a database in the defined path

    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & txtPath.Value & ";"

    ' Open the tables schema rowset

    Set cnn = CurrentProject.Connection
    Set rst = cnn.OpenSchema(adSchemaTables)

    ' Loop through the results and export the tables

    Do Until rst.EOF
        If rst.Fields(&quot;TABLE_TYPE&quot;) <> &quot;VIEW&quot; Then
            tablename = rst.Fields(&quot;TABLE_NAME&quot;)
            If Left(tablename, 4) <> &quot;MSys&quot; Then

                DoCmd.TransferDatabase acExport, "Microsoft Access", & _
                    txtPath.Value, acTable, tablename, tablename

                DoCmd.Close acTable, tablename
            End If
        End If
        rst.MoveNext
    Loop

    'Export form, module, and macro

    DoCmd.TransferDatabase acExport, "Microsoft Access", & _ 
        txtPath.Value, acForm, "frmRestore", "frmRestore"
    DoCmd.TransferDatabase acExport, "Microsoft Access", & _
        txtPath.Value, acModule, "clsCommonDialog", "clsCommonDialog"
    DoCmd.TransferDatabase acExport, "Microsoft Access", & _
        txtPath.Value, acMacro, "buAutoExec", "AutoExec"

FileErr:
    If Err.Number = 3044 Then
        MsgBox "This pathname does not exist.  Please enter a valid pathname."
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

The loop and transfer of the tables works fine, but message 2501: 'The TransferDatabase action was canceled' appears for the transfer of the form, module, or macro. Any ideas why and how to overcome this problem? Could it be something to do with the references? I have 6 checked...

Visual Basic for Applications,
Microsoft Access 9.0 Object Library,
OLE Automation,
Microsoft Visual Basic for Applications Extensibility 5.3,
Microsoft ActiveX Data Objects 2.1 Library, and
Microsoft ADO Ext. 2.7 for DDL and Security.


Thanks,

Softop

 

[BillPower]

Hi Softop,

This should work ok now, additions in red:

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim appPath As String
Dim tablename As String
Dim cat As New ADOX.Catalog

On Error GoTo FileErr

'Delete old database file if it exists
If Dir(Me!txtPath) <> &quot;&quot; Then
Kill Me!txtPath
End If

'Create a database in the defined path
cat.Create &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=&quot; & Me!txtPath & &quot;;&quot;
' Open the tables schema rowset
Set cnn = CurrentProject.Connection
Set rst = cnn.OpenSchema(adSchemaTables)

' Loop through the results and export the tables
Do Until rst.EOF
If rst.Fields(&quot;TABLE_TYPE&quot <> &quot;VIEW&quot; Then
tablename = rst.Fields(&quot;TABLE_NAME&quot
If Left(tablename, 4) <> &quot;MSys&quot; Then
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Me!txtPath, acTable, tablename, tablename
DoCmd.Close acTable, tablename
End If
End If
rst.MoveNext
Loop
rst.Close
cnn.Close
Set cat = Nothing ' the important one
'Export form, module, and macro
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Me!txtPath, acForm, &quot;frmRestore&quot;, &quot;frmRestore&quot;
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Me!txtPath, acModule, &quot;clsCommonDialog&quot;, &quot;clsCommonDialog&quot;
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Me!txtPath, acMacro, &quot;buAutoExec&quot;, &quot;AutoExec&quot;
Exit Sub
FileErr:
If Err.Number = 3044 Then
MsgBox &quot;This pathname does not exist. Please enter a valid pathname.&quot;
Else
MsgBox Err.Number & &quot;: &quot; & Err.Description
End If
End Sub

In VBA you should use the Me keyword and not the Value property, though in this case it wasn't causing a problem.

Bill

 

[Softop]

Works a treat! 'Me' comments also noted. Thanks, Bill.

 

[siena]

Bill, Softop,
Please allow me to ask both of you a question concerning what Softop's code.
I am doing something similar.
In my case, I originally used transferDatabase Macro action to try and move data from one access db to another.
After performing this task, I sheduled a windows task to run the macro everyday after a certain time.
Lastly, I used a .bat file to run the macro every day.
Problem here is that there are 5 access databases, each with 15 tables (that's 75 tables to be moved).
So I decided to write a vb code similar to perform this task for me.
The problem I am struggling with right now is determining how to automate this vba to do exactly what the macro was going to do for me.
Is it possible to automate a vba to run the task of moving data every day?
If yes, can anyone of you please assist.
I thank you for your time in advance.