The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Access Help: Updating a table by wiping records and then rewriting them?
ElJeffe
Registered User, ClubPA regular
Registered User, ClubPA regular
So! I am creating a tool to manage the import of data into our side of a database based on stuff generated on a similar version of it. The gist of it is that I am creating two buttons. The first button will grab data from numerous Excel files and stash them in a data airlock, of sorts. That part's done. The second button will look at that data, and either update the corresponding record in one of three target tables (if it already exists in there, based on a UniqueClientID field) or append it to the target table (if it doesn't exist). The existing target table will get automatically backed up prior to this process, and afterward the airlock data will be wiped. So, here's the question:
The code for doing the update in VBA (using a DoCmd.RunSQL) was getting kind of messy, since there are dozens of fields that need to be updated. Doing it by calling existing queries still required making a bunch of queries, and setting them up, which also was kind of messy. The code for just deleting all of the matching records in the target table and then appending the new versions from the data in the airlock is comparatively tidy. Is there any reason I would not want to go the delete/append route?
The only difference I can come up with is that, since the table has autonumbers, the delete/append approach means that the autonumbers attached to a "single" record will occasionally get shuffled around. But this shouldn't affect anything going on in the database, since the user never sees them, and references to data are always based on other fields. It seems more of an aesthetic concern than something likely to cause problems.
The code for doing the update in VBA (using a DoCmd.RunSQL) was getting kind of messy, since there are dozens of fields that need to be updated. Doing it by calling existing queries still required making a bunch of queries, and setting them up, which also was kind of messy. The code for just deleting all of the matching records in the target table and then appending the new versions from the data in the airlock is comparatively tidy. Is there any reason I would not want to go the delete/append route?
The only difference I can come up with is that, since the table has autonumbers, the delete/append approach means that the autonumbers attached to a "single" record will occasionally get shuffled around. But this shouldn't affect anything going on in the database, since the user never sees them, and references to data are always based on other fields. It seems more of an aesthetic concern than something likely to cause problems.
I submitted an entry to Lego Ideas, and if 10,000 people support me, it'll be turned into an actual Lego set!If you'd like to see and support my submission, follow this link.
0
Posts
The delete-append approach will definitely mess those up routinely, but if you're not using them for anything in particular, then why are you tracking them?
Steam: Elvenshae // PSN: Elvenshae // WotC: Elvenshae
Wilds of Aladrion: [https://forums.penny-arcade.com/discussion/comment/43159014/#Comment_43159014]Ellandryn[/url]
Does the new data have every field that it has now or just the parts they wanted to update? do the new records get accumulated in any way that would make you want to keep the old data?
Are you sure the new data should overwrite the old? Just because the customer id is the same, does that always mean you wouldnt have some possible crossover with two different customers? etc...etc..