As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Access Help: Updating a table by wiping records and then rewriting them?

ElJeffeElJeffe Moderator, ClubPA mod
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.

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.

Posts

  • Options
    ElvenshaeElvenshae Registered User regular
    Are you using the autonumbers for anything in particular?

    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?

  • Options
    GhotiGhoti Registered User regular
    My natural inclination for this kind of thing would be to use recordsets. I think with an active connection, you can use the recordset itself to make updates and appends to tables. It would be moderately better than RunSQL.

  • Options
    azith28azith28 Registered User regular
    That entirely depends on the business requirements of the data involved and the project. It's not really a development question.

    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..

    Stercus, Stercus, Stercus, Morituri Sum
Sign In or Register to comment.