Solved Showing the Date when I Append Records to a Table

[Weekleyba]

Pat,
I may be going crazy here because I thought I had this working but now.....it's not.
When you append the table, is it suppose to add all the records to the existing table? I do not want this. It just doubles the number of records!
If Import the source data, it deletes the other records, which is fine, but it also deletes the CreateDT field, not fine.
Not sure what to do now about the CreateDT field.
Suggestions?

 

[Pat Hartman]

Append - adds new rows which might duplicate existing rows if you don't have correct primary keys defined.
Update - updates existing rows.

Managing updates from two sources is a recipe for disaster. Either your Access database is the master or the Excel sheet is the master. If the Excel sheet is the master, don't import anything. Just link to the Excel sheet and run your reports or whatever the app needs to do. If the Access database is the master, the Excel sheet should only contain new records or updates to existing records. It should not contain all records.

Can you clarify please.

 

[Weekleyba]

The source for the Excel spreasheet is an online program called FBIS. I log into it and input my criteria, and export the data into an Excel spreadsheet.
My plan then was to import that data into my db for all the engineers to easily view in a much more viewer friendly reports.
But the data that I export to Excel changes all the time. So append is out.
I would be fine with just importing it, while wiping out the existing table data.
The only problem with that is not being able to automatically have the CreateDT work. Since it deletes that field.
Maybe I’m down to just having to have the admin manually put in the date each time.
Does that clarify?
Thanks for looking at this for me.

 

[theDBguy]

Hi. In Post #15, I made a suggestion, and in Post #18, I asked a question. Just wanted to bring your attention to them, just in case I am still able to help. Cheers!

 

[Weekleyba]

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!

 

[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)

Office VBA reference topic

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.