Importing Excel sheet and replacing whole records? (1 Viewer)

[ComradeGrumbles]

This may have been asked before, but I looked around and couldn't seem to locate it.

I need to import an excel sheet containing updated tickets to my access ticket database. They have unique IDs in the form of a "Ticket ID" field.

I could just use an append query to add the new tickets to my database, but there are some changes on the excel side to tickets that already exist in access as well. What would be the easiest way to facilitate this import and overwrite all tickets with matching Ticket IDs to the new ones contained in the excel file?

I already have the excel file imported into it's own table, so it's a matter of updating my main table off of this temporary table containing the excel tickets. The field names are the same across both tables, it's just a matter of updating the info contained in them.

Thanks!


PS: This will need to be repeated for multiple different excel files, so the easier the better! Thanks!

 

[Frothingslosh]

You can use an update query. Link the staging table with the destination table via an outer join (joining on the ticket ID), include every field that has data to be transferred over, and run it. Tickets that already exist will have their data updated to match that in the staging table, and new tickets will be appended.

If you don't want to update every ticket that's being imported, then you'll need to figure out a way to screen those out.

 

[ComradeGrumbles]

Thank you! I didn't know that I should use a join and update query.

I'll give that a go and let you know if it works tomorrow

 

[Frothingslosh]

Thank you! I didn't know that I should use a join and update query.

I'll give that a go and let you know if it works tomorrow

It should - it's the process I use in one of my own tools to handle a specific mass update/append.

The real question is going to be if that is the result you actually need.

 

[vbaInet]

I could just use an append query to add the new tickets to my database, but there are some changes on the excel side to tickets that already exist in access as well. What would be the easiest way to facilitate this import and overwrite all tickets with matching Ticket IDs to the new ones contained in the excel file?

You need to identify the tickets that are different in a query.

However, if your table is already built to not allow duplicates based on certain fields, then it will block the duplicates anyway.

 

[ComradeGrumbles]

This worked out well. I wish there was a more automated way of doing it, but for now this was what I needed.

Thanks!