Updating Back-End Table

[blarson0]

I am having some trouble deciding the best way to update a back end table. The table will need to be updated at least once a day, but there will always be users looking at the info in the table. The problem is that, when we get the new data to update, we must download the whole table as a .txt report, then I use a program to export it to a .mdb table. I can update data in a table no problem, but to do this update I have to overwrite the table, which will cause errors.
I am just a temp, so I would like to make updating the table as easy and automatic as possible. Any ideas?

Thanks!

-Brad

 

[thornmastr]

Hi Brad,

I have clients who have this setup also.. Somewhere you have this big old clunky main frame number cruncher and it downloads this text file, and you have to extract all kinds of information from it and then do something with that information.

I build a conversion function that takes each text record and converts the information I need into the correct data types and then stores it into a table. Then I use that table to update the primary associated tables. After I'm done with the proccess, i delete all rows from the work table, the one used to store the stuff from the text file.

If you need more specifics, drop me a line and include such trivia as what the text record looks like and how you want things to end up being.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[blarson0]

Yes that is pretty much exactly what I am doing. I can't just replace the table, because that will cause sharing violations.

Getting the information from the old clunky mainframe into a table is no problem, but how do I replace the old table? I am considering just doing some SQL to DELETE * FROM currenttable and then INSERT * FROM newtable... but will that work if someone is looking at some records? It would be much better if I could just delete the records that are either not in the new table or have changed, and then insert only what I need to...


Thanks for the help!

-Brad

 

[thornmastr]

Brad,

Just issue a docmd.runsql “DELETE * FROM yourtablename”

This of course assumes the fact that this data can be deleted.then you can just do a standard old INSERT INTO and your done.

Another approach is to define a unique key that is common to both tables. Any duplicate records will automatically be rejected. Just remember, before running it, to set your warnings to false until the query comletes, then turn them on again.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com