Transferring objects between two external Access databases

Transferring objects between two external Access databases

Whilst you can easily use the TransferDatabase method to import/export/link a table (or any other object) in the current database, if the object resides in one external database, and you wish to transfer it to another external database, then you could either import it from one database, and then export it to the second. Or else you could use something like this instead:

Sub sExportExternal(strDBFrom As String, strDBTo As String, strTableName As String)
'   Procedure to transfer a table from one external Access database to another
'   Accepts:
'       strDBFrom - the name and path of the database that contains the table to be exported from
'       strDBTo - the name and path of the database that the table is to be exported to
'       strTableName - the name of the table that is to be exported
    On Error GoTo E_Handle
    Dim objAccess As New Access.Application
    With objAccess
        .OpenCurrentDatabase (strDBFrom)
        .DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable, strTableName, strTableName
        .CloseCurrentDatabase
    End With
sExit:
    Exit Sub
E_Handle:
    Select Case Err.Number
        Case 3011 ' The table does not exist in the first database
            MsgBox "'" & strTableName & "' does not exist in '" & strDBFrom & "'", vbOKOnly, "Transfer cancelled"
        Case 3044 ' The database that we are transferring the table to does not exist
            MsgBox "'" & strDBTo & "' does not exist.", vbOKOnly, "Transfer cancelled"
        Case 7866 ' The database that we are transferring the table from does not exist
            MsgBox "'" & strDBFrom & "' does not exist.", vbOKOnly, "Transfer cancelled"
        Case Else
            MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
    End Select
    Resume sExit
End Sub

Top

Last modified at 06/06/2006 14:58:06