Easy Table Replace?

[Hakala]

Hi, Tek-tips!

I am looking for an easy way for my users to replace a monthly table. The table changes names every month in a predictable fashion (MCNT0607 and then MCNT0608 for July and August) and is in an Access 2002 database.

The Access 2002 database I'm working with does a compare against the data in the MCNT table. However, those tables have about 400,000 records each, so we don't want to store that data in this database. We pull the newest table in, use it for comparing, and then delete it. (Links don't work because we're on a network and the MCNT table is in another city 70 miles away; lag time is incredible.)

My users aren't Access-savvy. What I'd like to do is give them a way to pull in the new table, change it's name so my queries will work, and then do their comparison without leaving the data entry form. (Or at least without needing to be behind-the-scenes.)

Any ideas on an easy way to accomplish this? Thanks!



Michelle Hakala

http://www.winebird.com/

 

[PHV]

We pull the newest table in
How did you that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hakala]

It's a manual process for me as the DBA; we want to give the task to a user.

I open the database behind-the-scenes, choose File, Import, find the table and after it's imported, rename it. Then I let them know the new data is in, and when they're done a query deletes the table.




Michelle Hakala

http://www.winebird.com/

 

[rubbernilly]

Look at the TableDefs collection of the CurrentDB object, and at the TransferDatabase method.

You should be able to give the user a button that would say "Update Data to New Month" or something similar.

Option 1:
1) You know the table name, as derived by your knowledge of when the table needs to be updated and the date range it represents. For instance, you know that in August, you are actually working with July data, giving you a table name of MCNT0607 (derived by Month and Year functions).
2) You know the name of the database that houses the updated table (consistently named from month to month)

Option 2:
1) Ask the user to supply a name or a piece of the name that you use to render the table name. (InputBox)
2) Possibly ask the user to locate the database through native windows functionality (requires API calls).

Implementation:
If you had to use logic to render the table name or path of the database, then use the Dir() function and TableDefs collection to verify the existence of not only the update database but also of the table within it. If the Dir() test fails, you will probably want to cancel the update and prompt the user to give you a call so that the new update database can be moved into place. If the TableDefs collection does not contain a TableDef with a Name property of what you expect, then you might want to provide a small form with a listbox that lists the TableDefs in the TableDefs collection (possiby with some limitations, like the leftmost character cannot be "~", or that the leftmost 4 characters need to be "MCNT"). Let the user pick from this list the table they are going to use for the update. Then use the TableDefs collection of the local database to delete the old table. Finally, use the TransferDatabase method to get the new table into the database.

HTH