Solved Showing the Date when I Append Records to a Table

[theDBguy]

DBguy, I always appreciate your help.
In previous posts I’ve explained where the Excel file that I import comes from.
Now that I have the Excel file, I need to import it. The only way I know how to import is manually. Therefore your solution for the update date, is to add it manually.
Do have any examples on how to import an excel file, completely erasing the existing, via VBA? Then perhaps I could figure out how to write the code for updating the date.
Idea: If I stored the excel file in the same location and called it the same name every time, would linking the table work for me? Still need some code to perhaps push a button and update via the link. I’d have to delete the old excel file prior to placing the new updated file with the same name. Would that break the link?
I’m just not sure in this.
Thanks for helping!

Hi. Thanks for the clarification. There are so many ways to do this automatically. If you can't be sure the Excel file to import will have the same name and location all the time, you can have the code ask the user to "pick" the file they want to import. You can control the name of the table where the data goes though. If you keep the name and location the same for each import, then you could try the TransferSpreadsheet method. It should overwrite the table each time (I'll have to test it out to be sure). To update the date table at the same time, you could just execute an UPDATE query.

 

[theDBguy]

Hi. Thanks for the clarification. There are so many ways to do this automatically. If you can't be sure the Excel file to import will have the same name and location all the time, you can have the code ask the user to "pick" the file they want to import. You can control the name of the table where the data goes though. If you keep the name and location the same for each import, then you could try the TransferSpreadsheet method. It should overwrite the table each time (I'll have to test it out to be sure). To update the date table at the same time, you could just execute an UPDATE query.

Okay, I just did a quick test and the TransferSpreadsheet method "appends" the data to the table. In that case, your code should do something like this:

1. Execute a DELETE query to empty out the table
2. Run the TransferSpreadsheet method to import the data from Excel
3. Execute an UPDATE query to update the import date in the ImportDate table

Hope that helps...

 

[Weekleyba]

Thanks DBguy!
I'll give this a try and let you know.
Never heard of the TransferSpreadsheet method before. Nice!

 

[theDBguy]

Here's a link to MS docs for your reference. Good luck!

DoCmd.TransferSpreadsheet method (Access)

Access VBA reference

docs.microsoft.com

 

[Weekleyba]

Thanks for all the help to all!
It works perfectly.
Here's the code I ended up with.
Thanks again!!

Private Sub Command12_Click()
On Error GoTo SubError
    
    DoCmd.SetWarnings False
    
    
    Const fName As String = "C:\Users\brweekley\Documents\Databases\Funding_Database\IHS - Allowance Status by Locat.xlsx"
  
    DoCmd.OpenQuery "Delete_IHS - Allowance Status by Locat"
  
    ' True means the spreadsheet has column names for the first row
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "IHS - Allowance Status by Locat", _
            fName, True, "IHS - Allowance Status by Locat!"
    
    MsgBox "Worksheet imported!", vbInformation + vbOKOnly, "Success"
  
    DoCmd.OpenQuery "UpdateImportTable_Q"
    DoCmd.RefreshRecord
  
    DoCmd.SetWarnings True
    
  
SubExit:
On Error Resume Next

    Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
       "An error occurred."
    GoTo SubExit

End Sub

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.