Design question make table over and over or update table

[fidlstix]

Let me start by saying - feel free to roll your eyes now as I am sure you will be doing so by the end...extreme novice question.

A question of design - I have a db that has a base table that has defect info, with a PK. The info is pulled out of our siebel system and will never be edited by the access users. However, it will be edited by admin people who access the siebel system.

The end user's data is held in a separate table where they rank the order in which they want the defects to be addressed. The defect PK from the main tbl is held in the ranking table.

I have a choice when updating data in the main table to either recreate the table everytime the data is refreshed, or do a row by row check of id's and if it exists - make sure that it updates 5-7 columns (what the admins will be changing) of the 15 in the table, if it doesn't exist create the record.

I belive the correct choice is to do the updates line by line. But if it is 6 of 1 and a half dozen of the other - it's easier for me to make the table over and over...

What are the pitfalls of doing a mk tble every time? can i expect the db to balloon to enormous proportions?

This isn't a high volume high usage db.

I guess i am looking for a push to do it 'the right way' if there is one.

Thx Much in advance,

Laura

 

[MasterRacker]

Does your import table have enforced constraints against other local Access tables? If not, I would just recreate the table on every import. That way you're certain the data is perfectly in sync with your Siebel system. I certainly could be wrong, but your new table should reuse the whitespace left by the deleted one, so your DB grow out of control. If it did, you could always periodcally compact.

I don't know much about Siebel, but an even better solution might be for your admins to create a view in the Siebel system that you can link to. You'd always be seeing live, up to date info that way.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[fidlstix]

Thanks so much for your reply - went ahead and updated the table will set the db to compact on close.

I hope to get direct access to siebel through this little db so when reviewing defect items the group making the the final decision on priorities will see it updated by the facilitator right away and subsequent queries will show the updated data...


Thanks Again!

Laura