Overwrite Existing Data in Access Using transferspreadsheet

Question

I have a macro that saves monthly data to an Access database in Access 2013 using the DoCmd.TransferSpreadsheet method. A possible situation I want to account for is if data is saved to the database and then someone realizes that the data was wrong and they want to rearchive the data for the month in question after they fix the data. In other words, is there a way for me to change my code to overwrite data for the same month as the data that will be archived?

One of the fields in the data that is archived is the date, and it also has a field name as well. Another important thing to know is that the data that needs to be overwritten would all be in the same month, but not necessarily the same day. Here's a simplified example of what the "my_data" array would look like in excel:

Sub excel_export()
    Dim xls_path As String
    xls_path = "C:\mywbk.xlsm"

    Dim db_path As String
    Dim db_obj As Access.Application
    db_path = "C:/mydb.accdb"
    Set db_obj = New Access.Application

    Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12Xml, "TableName", _
        xls_path, True, "my_data")
    db_obj.CloseCurrentDatabase
    Set db_obj = Nothing
End Sub

You could probably just write a fitting delete query that deletes all data from that month. Then you can import it again. To create a fitting example, I would need a little more data (like how you know which month is being imported). Is there any specific reason this wouldn't work/be difficult? (note: going offline soon, might not reply till tomorrow) — Erik A, Aug 24 '17 at 21:36
Thanks for the help! I am looking into the delete query. It looks like that will probably work, but I need a way of asking the user if they want to overwrite the data before the data is deleted. Is there a type of query that can get the month and year from the date field in an Access database? The best way to know what month is being imported is to have the program look at the data being imported. — Allen Black, Aug 24 '17 at 22:14

score 0 · Answer 1 · answered Aug 25 '17 at 08:00

Don't import the spreadsheet, link it as a table.

Then, first, create a simple select query that filters the linked table and converts, say, string dates to real dates.

Now, use this query as source for a combined update and append query for your Access table as described here:

Update and Append Records with One Query

Overwrite Existing Data in Access Using transferspreadsheet

1 Answers1