Import and replace table

[yippie_ky_yay]

Hello forum,

I need to periodically import several tables into one database. If I delete all the tables first and then import everything goes well (but it's obviously risky). When I run it with older versions of the tables still there, the new tables do not replace them - rather, they get renamed to their same name with a "1" appended to the end.

Is there something I could add to my transferDatabase acImport statement that will replace tables of the same name?

Thanks in advance,

-Sean

 

[Fornatian]

Things I would consider..

Would it not be mnore feasible just to have linked tables to the lookups are simultaneous to updates?

An alternative

1. Rename all old table objects to OLDName,
2. Import new tables,
3. Check they've imported properly
4. Delete OLD* Tables

Another alternative

1. Add an extra field to your database tables for 'import date'.
2. Use an append query to import the new info setting the import date to todays date
3. Delete all info using a delete query where the import date is not today.

 

[yippie_ky_yay]

Thanks for answering Fornation!

If I did use linked tables, can I export them as regular tables?

What I didn't mention was that I'm in the middle of creating an all-purpose app using Access (just for me to use). I periodically have to send the web people a db with all current info (made up of several tables across several dbs - because different people update different dbs).

I thought it would be nice to click a button and have all the necessary tables import to my new app and then export to the "Web Database" (which is in 97 and I'm using 2000).

So, I guess my follow up question is - if I export a linked table into a db that I'm going to give someone else (to go on a different server) then will the tables not freak out trying to find the linked dbs on my network?

Thanks again!
-Sean

 

[Fornatian]

I understand your problem and suggest instead of exporting the linked table why not create a duplicate non-linked table using a MakeTable query, then export the resulting table(s).

 

[yippie_ky_yay]

Thanks again Fornation - that's exactly what I'll do!

(seemed like a waste to import and then immediately export).

-Sean