Import Excel to Access

[fibayne]

Hi ...I have been looking for a way to automate a data import from an Excel spreadsheet, which is updated daily and needed to be imported to the DB, the previous days data needed to be deleted with the current days data only, being stored in the table, I wanted to do this by clicking on a command button on a switchboard, bit of a novice but found a couple of posts, which have done in a simple way just what I needed, hopefully it will be of use to someone else

The qry is an Access delete query.

Private Sub XLImport_Click()
DoCmd.OpenQuery "qry_DeleteAgedDebtors"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblYourtblName", "Path:\YourPathName\ExcelSpreadsheetName.xls", True
EndSub

cheers
Fi

 

[Ashfaque]

Hi,

Can you post your query (qry_DeleteAgedDebtors) here?

 

[fibayne]

Hi...SQL view
DELETE tblYourTableName.*
FROM tblYourTable Name;

its a Delete Query which you can do by creating a query in design view, select the table then go to Query type select Delete query, add to the query which data you want to delete or use * to select all
hope this helps...cheers
Fi