Importing Excel spreadsheet to Access database daily

[rmorgan]

I need some help on this one. I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets, type, model, locations, etc. The second spreadsheet has users assigned to them, phone numbers, etc. I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.

The way I have it setup now is through a linked table, then I use a “Make table query” to filter the data.

My problem is the filtered table has relationships set up that I have to delete then recreate every time I need to run the “Make table query” because it has to delete the old table first.

Is there a better way to make this happen?

 

[Ziggy1]

change your make table query to a Append Query, this will add to your table instead of blowing it away everytime. Simply changing it in design will automatically match the fields to update.

so along with that you create a delete query that will delete the data in your table.

then using a macro you set 2 open query actions, the first runs the delete query to clean out the data. and then the 2nd runs the append to add the data to your table.

Warning though if you have auto number ID's your table it will increase the database size over time so you will need to compact with the table deleted of records.

I think this should work for you, I run a process like that myself and it works great.

 

[rmorgan]

Thanks of the input. Here is the solution theat I have come up with.

On one of my Forms, I created a Command Button with the following 'On Click' Event:

Private Sub Cmd_UpdateRawData_Click()
On Error GoTo Err_Cmd_UpdateRawData_Click

DoCmd.SetWarnings False
DoCmd.Close acTable, "tbl_Users"
DoCmd.Close acTable, "tbl_Assets"
DoCmd.RunSQL "DELETE FROM tbl_Users"
DoCmd.RunSQL "DELETE FROM tbl_Assets"
DoCmd.OpenQuery "qry_UpDtAssetTbl"
DoCmd.OpenQuery "qry_UpDtUserTbl"

DoCmd.SetWarnings True

Exit_Cmd_UpdateRawData_Click:
Exit Sub

It works great