CODEWITHSUNDEEP
ms-accessms-access-2007vba
Gutanoth
Gutanoth

Reputation: 842

Checking to see if a worksheet already exists Access

I am trying to use a vba script to automatically import a few excell worksheets into access. However, if someone executes the script twice, the data gets added to the already existing table.

the code I am using is this:

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel9, _
"DB_AlarmTest", _
"C:\Data\PLC_MOBZ\Test\Import\DB_AlarmTest.xlsx", _
True

Is there a way to check wether or not this table already exists and if it does, delete or overwrite the existing table and replace it with the new table instead of just adding the values?

Upvotes: 1

Views: 524

Answers (1)

Dan Metheus
Dan Metheus

Reputation: 1438

You can just drop the table before you run your import:

On Error Resume Next
CurrentDb.Execute "DROP TABLE DB_AlarmTest"
On Error GoTo 0

DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel9, _
    "DB_AlarmTest", _
    "C:\Data\PLC_MOBZ\Test\Import\DB_AlarmTest.xlsx", _
    True

Upvotes: 2

Related Questions