Import Spredsheet Question

[Jameo]

Hi all,

I am importing a spreadsheet with the following code:

Sub ImportExcel()
Dim strXls As String
strXls = "UNC Address Removed For Post"
DoCmd.TransferSpreadsheet acImport, , "tblStockImport", _
    strXls, True, "Delivery Volumes!"
 
End Sub

This works fine, however, the issue is that my import needs to be ran daily, after the import spreadsheet has been updated.

Using the above, all data in the spreadsheet becomes duplicated. Is there any way to import only 'new' data into the table? Essentially, everyday the target spreadhseet will have one additional row added. Ideally that is the only row i wish to import on a day to day basis.

Currently I am deleteing the table, creating a table and then importing. This is annoying and time consuming.

Any help will be great.

Thanks

 

[Norie]

You could import into a temporary table and then run an append query to only add the new data to the existing table.

Then you can delete the temporary table

That could all be done in code.

You could actually have code to delete all records in the destination table before the import.

Application.DisplayAlerts = False ' optional
DoCmd.RunSQL "DELETE *.* FROM tblStockImport"
Application.DisplayAlerts = True
 
strXls = "UNC Address Removed For Post"
DoCmd.TransferSpreadsheet acImport, , "tblStockImport", _
    strXls, True, "Delivery Volumes!"

 

[alansidman]

My first choice on a solution would be to follow Norie's advice. This is what I have done in the past. However, if you don't wish to be bothered, an alternative to this is to link your Excel table to Access and then your Access data would be updated every time your Excel table is updated.

Alan

 

[Jameo]

Cheers guys. I will most probably follow Norie's suggestion - I had started thinking along those lines myself. Just out of curiosity alan, how would I go about linking my excel spreadsheet to my database?

Thanks

P.S. for some reason Application.DisplayAlerts = False brings up an error for me, I am having to use DoCmd.SetWarnings False for some strange reason.

 

[Norie]

Jameo

Oops, my mistake.

DisplayAlerts is Excel not Access.

I did say it was optional though.

 

[Joe4]

Just out of curiosity alan, how would I go about linking my excel spreadsheet to my database?

Which version of Access are you using?
In 2003 or earlier, just right click and select "Link" instead of "Import".
In 2007 and higher, click on the Excel icon in the External Data ribbon, and select the "Link to the data source by creating a linked table" option.

P.S. for some reason Application.DisplayAlerts = False brings up an error for me, I am having to use DoCmd.SetWarnings False for some strange reason.

Application.DisplayAlerts = False is Excel VBA syntax.
To do the same thing in Access VBA, you need to use DoCmd.SetWarnings False instead.