Append from one table to another

Dim dbs As DAO.Database
Dim td As DAO.TableDef
Dim fd As Office.FileDialog
Dim BatchID As string

   ' set dbs to current database
   Set dbs = CurrentDb
   
   ' loop through all tables in current database
   For Each td In dbs.TableDefs
   ' check if Temp table is still in current database
      If td.Name = "DLPTemp" Then
        ' it is, so drop it
         dbs.Execute "Drop Table DLPTemp;"
      End If
   Next
    


            Set fd = Application.FileDialog(msoFileDialogFilePicker)

   With fd

      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Please select the file to impot."

      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel 2003", "*.xls"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        txtFileName = .SelectedItems(1) 'replace txtFileName with your textbox

      End If
   End With
    
    
   ' import table
      DoCmd.TransferSpreadsheet acImport, , "DLPTemp", txtFileName, True
   
   ' execute the insert SQL from temp table to Export

BatchID = me.Batch_ID
   
   dbs.Execute "INSERT INTO dbo_tbl_reference_data ([Batch_ID], [Reference1], [Reference2], [Reference3], [Reference4], [Reference5], [Reference6]"
   
' THIS IS MY ATTEMPT TO INSERT THE DATA INTO MY LIVE TABLE
'The Batch_ID field is the only field which is not present in the Excel document or temp table.  For each record being inserted into tbl_reference_data i need to assign the Batch_ID.  
  
   ' drop table
   'dbs.Execute "Drop Table DLPTemp;"
   
  ' release objects
   
   Set dbs = Nothing
   Set td = Nothing

End Sub

Select allOpen in new window