Originally Posted by
lamore48
...now problem is that ExportData is an existing table and when I run this routine it adds a copy of the data to the existing table, so I guess I need to add code to delete the table first and then add the new one after. Any thoughts on how to do it?
Constantly deleting, then recreating table is a sure path to corruption.
Delete the RECORDS, then do the import.
Something like this:
Code:
'delete the records in the table
CurrentdB.Execute "DELETE * From ExportData", dbfailonerror
'import new records
DoCmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,"ExportData", Me.txtFileName ,True
Another method is to link to the Excel workbook, then treat the workbook as a standard table.
Or, use automation to open the workbook and read through the workbook, appending the data to the table.