Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Import Worksheet To Overwrite Existing Table Contents 1

Status
Not open for further replies.

dominicdunmow

Technical User
Jul 28, 2004
125
CA

I currently run some code that automatically imports a specific excel worksheet to an Access Table - using the "DoCmd.TransferSpreadsheet"

I want to import the same worksheet daily so I can capture any updates, but each time the code imports it, Access tries to append the new data to the existing table. My aim is to overwrite the existing table replacing it with the new table derived from the updated worksheet.

Can anyone tell me the code required to do this please?

Thanks

 

Can I enter this line into the module? how do I identify the Table I want to delete?

Thanks
 
in the same module as your transferspreadsheet command :

Code:
docmd.runsql "DELETE * FROM [b] mytable [/b]"
docmd.transferspreadsheet ...

replace mytable with the name of the table you want to delete the records from.

 

I've tried that and I get the error message "Run-Time Error 3131, Syntax error in From clause
 
...sorry, the complete code I've used is:

Function Import_Present_XL()

DoCmd.RunSQL "DELETE * FROM 2004 Auto Actual "
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"2004 Auto Actual", "\\nalmond-2\SERVICE REVENUE\Master.xls", True, "Master!"

End Function
 
hi it should be

Code:
Docmd.RunSQL "DELETE * FROM [2004 Auto Actual]"

you should avoid spaces in object names for this exact reason : you need to add brackets.
 

Yes thanks, I've just been playing around with it and found that if I changed the table name to "2004" it worked.

Thanks very much for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top