Import and Update relevant records

[Danick]

I am using the following VBA to export data to an Excel Spreadsheet so that users can update the spreadsheet and send it back to me. Then I'd like to import it back into the table called tblProduct.

Dim WbName As String
    Dim WbSheet As String   

    WbSheet = "qryUpdate"
    WbName = "ProductUpdate.xls"
    
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True

But I don't want to append the spreadsheet as it would create duplicates.
Instead, I would like to update only the relavent records based on a unique field that is in each record called "MSN".


Does anyone have sample VBA that does this?

 

[sxschech]

Couple of ways could be done. Is it one file with same name? Will the name be different each time? Or are you getting files from different people?

One option is to link the file (if name is consistent) then you could run an update query between the linked file and the destination table in access.

Another option is to import the file to a temporary table (either run a delete query or delete the table before importing new data) and then run the update query off the temp table and the actual table. Would also need to periodically compact the database to reduce bloat caused by importing and deleting data.

Also, if the data you want to import/update does not exceed 255 chars in a single column (if you can have more than 255 characters in a cell, it would truncate the value in that cell), you can run an sql statement on the excel file directly. Would require either editing sql or using vba if the name of the excel file will be different each time.

I can provide some code examples depending on which option(s) you are considering.

 

[Danick]

I don't want to link the file as the file I'll be getting back is only a small subset of the data that is in the entire table. So I can't delete the table and just add the new one either.

The file being returned could be the same name and I could even store it in the same location. So for instance, I would export the data I need updating to an excel file called ProductUpdate.xls. Then send that file to a user for updating. I could then overwrite the old excel file with the new one that was updated using the same name ProductUpdate.xls.

The hard part is importing that same excel file back into the master Access table without creating duplicates.
The VBA would have to find the relevant records to update based on the "MSN" field and then update only the fields in the update query. All the fields to be updated are short text fields that will not exceed 255 chars.

So to test it out, I should be able to export the data to an excel file and then immediately import it back in. Nothing should change and there shouldn't be any duplicates.

 

[static]

Access replication is no longer a thing, but you would still be better off distributing a copy of your database with the records exported to it just to save on validation.

If users don't have Access (or a runtime version) you are stuck with linking the Excel file - joining on the ID (MSN) means only those records will be updated.

 

[Danick]

I think I may have found an article that explains how to this. It's by Doug Steele. Here's the link

http://www.vb123.com.au/200311_ds_aa.htm

Now I just have to see if I can get it to work with the guidance he provided.