Exporting Table into a Backend Database

1. Create first the following function:

Function PutTableOnBackEnd(DBName As String, TblName As String, DestinationTable As String) As Boolean

'DBName should include full path and name of back end database
Dim Db As Database
'test back end

On Error Resume Next

Set Db = OpenDatabase(DBName)

If Err <> 0 Then

'failed to open back end database

Exit Function

End If

If Not Db Is Nothing Then Db.Close

'test if table is local

If IsNull(DLookup("Type", "MSysObjects", "Name='" & TblName & "' AND Type=1")) Then

'table is not local

Exit Function

End If

'put table on back end
'DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, TblName, TblName

DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, TblName, DestinationTable

If Err <> 0 Then GoTo Done

'link to the back end table
'DoCmd.DeleteObject acTable, TblName
'DoCmd.TransferDatabase acLink, "Microsoft Access", DBName, acTable, TblName, TblName

PutTableOnBackEnd = True 'defaults to false if it fails to get here

Done:

End Function

2. Specify the Tables to Export:

Tables to Export
TableID	TableName	Directory	BackEndDB
1	API Control Table	\\zwnwb080\groups\API_UPDATES\API\Upload	BackEnd.mdb
2	APIData Table	\\zwnwb080\groups\API_UPDATES\API\Upload	BackEnd.mdb
3	Unused ControlNo Table	\\zwnwb080\groups\API_UPDATES\API\Upload	BackEnd.mdb

3. Call the Function in a command button of a form:

Private Sub CMD_ExportTable_Click()

Dim Result As Boolean
Dim strPC As String
Dim SourceTable As String
Dim DestTable As String
Dim strDirectory As String
Dim BackEndDB As String

strPC = Me.TxtPCName

For i = 1 To 3

‘Specify the Table Names

SourceTable = DLookup("[TableName]", "Tables to Export", "[TableID] =" & i)

DestTable = strPC & "-" & SourceTable 'Here I attached the PC Name for Record Purposes

strDirectory = DLookup("[Directory]", "Tables to Export", "[TableID] =" & i)

BackEndDB = DLookup("[BackEndDB]", "Tables to Export", "[TableID] =" & i)

‘Call the Function

Result = PutTableOnBackEnd(strDirectory & "\" & BackEndDB, SourceTable, DestTable)

Debug.Print Result

MsgBox ("Finish exporting " & SourceTable)

Next i

End Sub