acImport creates link?

[trax1337]

I have the following code which for some reason ended up dropping the source table, any ideas why that happened?

Sub ImportBookingsTable()
    Dim db As DAO.Database
    Dim sourceDB As String
    Dim sourceTable As String
    Dim destinationTable As String

    ' Set the source database and table
    sourceDB = "someOtherDB.accdb"
    sourceTable = "tbl_bookings"
    destinationTable = "tbl_local_bookings"
    
    ' Initialize error handling
    On Error GoTo DropTableError
    
    ' Delete existing table if it exists
    'DoCmd.RunSQL "DROP TABLE " & destinationTable
    
    ' Reset error handling for the import process
    On Error GoTo InsertError
    
    ' Import the table from the source database to the current database
    DoCmd.TransferDatabase acImport, "Microsoft Access", sourceDB, acTable, sourceTable, destinationTable, False

    ' Success message
    MsgBox "Synched", vbInformation, "Success"
    
    ' Reset error handling
    On Error GoTo 0
    Exit Sub

DropTableError:
    ' If the drop table command fails, continue to import process
    Resume Next

InsertError:
    ' Error message for the import process
    MsgBox "An error occurred during the SQL insert operation: " & Err.Description, vbCritical, "Error"
    
    ' Reset error handling
    On Error GoTo 0
End Sub

 

[arnelgp]

you should use an Insert Query to append records to your destination table.

with currentdb
.execute "delete * from tbl_local_bookings;"
.execute "insert into tbl_local_bookings select * from tbl_bookings;"
end with

 

[GPGeorge]

I have the following code which for some reason ended up dropping the source table, any ideas why that happened?

Sub ImportBookingsTable()
    Dim db As DAO.Database
    Dim sourceDB As String
    Dim sourceTable As String
    Dim destinationTable As String

    ' Set the source database and table
    sourceDB = "someOtherDB.accdb"
    sourceTable = "tbl_bookings"
    destinationTable = "tbl_local_bookings"
   
    ' Initialize error handling
    On Error GoTo DropTableError
   
    ' Delete existing table if it exists
    'DoCmd.RunSQL "DROP TABLE " & destinationTable
   
    ' Reset error handling for the import process
    On Error GoTo InsertError
   
    ' Import the table from the source database to the current database
    DoCmd.TransferDatabase acImport, "Microsoft Access", sourceDB, acTable, sourceTable, destinationTable, False

    ' Success message
    MsgBox "Synched", vbInformation, "Success"
   
    ' Reset error handling
    On Error GoTo 0
    Exit Sub

DropTableError:
    ' If the drop table command fails, continue to import process
    Resume Next

InsertError:
    ' Error message for the import process
    MsgBox "An error occurred during the SQL insert operation: " & Err.Description, vbCritical, "Error"
   
    ' Reset error handling
    On Error GoTo 0
End Sub

I agree that you can probably make this process more efficient by replacing the data, not the whole table.

That said, I can't see how the source table in a second accdb could be deleted from that second accdb by this code. Are you certain that is what actually happened? And is it repeatable?

 

[trax1337]

I agree that you can probably make this process more efficient by replacing the data, not the whole table.

That said, I can't see how the source table in a second accdb could be deleted from that second accdb by this code. Are you certain that is what actually happened? And is it repeatable?

I am 100% sure this deleted the sourceTable and I don't understand why. It was an import, not a link. I have no intention of trying a second time ^^

 

[Pat Hartman]

I agree that you can probably make this process more efficient by replacing the data, not the whole table.

delete/append data creates the same bloat as delete/create new tables. The later has more overhead though.

If you have to constantly replace a table with a fresh version from another source, there are better options.

1. Link to the other source. Just use it directly
2. Use a "side" BE which holds only the temp table. You can just recreate the temp database each time you need to replace the data. You don't need to refresh the links or anything as long as you keep the db and table names constant.

 

[GPGeorge]

I was thinking of the overhead and risks of creating and deleting tables, but yes, there is always bloat.

Option 2 is the least risky, IMO.