Rename Tables Programmatically in MsAccess 2003

[access2010]

Every month we receive an Excel Spreadsheet that we import into our MsAccess Database table “ RBC “

Can I please get a suggestion on how to;
Rename Table RBC_D to RBC_To-Day’s date
Rename Table RBC_C to RBC_D
Rename Table RBC_B to RBC_C
Rename Table RBC_A to RBC_B
Rename Table RBC to RBC_A

Copy RBC_Org, which contains no data and rename the table to RBC, which will then receive the data from the new Excel File.
Currently the data in each of the 5 tables is in individual rows on our forms.
Other than doing the above manually, could we please get your suggestion?
= = =
I have created the Module and Macro with information from the internet, but I have not been able to run the program.
Your suggestions will be appreciated.
Nicole

 

[DHookom]

I would use a single table and add a field that identifies the import date. Having multiple tables with the same structure is considered bad practice.

 

[access2010]

I would use a single table and add a field that identifies the import date. Having multiple tables with the same structure is considered bad practice.

Thank you for your suggestion, but the Excel Spreadsheet we receive each month has about 60 different fields and we would like to keep the data for a few months for comparison. What do you suggest? Thank you.

 

[The_Doc_Man]

If you continuously manipulate tables in that way, you accumulate a pile of RBC-date tables. The problem will be that when you import the table and you adjust the names as you suggest, you have a huge pile of old data that you are still keeping around. What were you planning to do with the old data? Because if you keep it in the DB, eventually you will fill up your .MDB file. Every record is potentially 500 bytes based on the table definitions in your file.

One way to do this is to import the sheet using a staging table. Then if needed, clean up the imported data. Then do an append from the cleaned-up staging table in which you add an extra DATE field that didn't come from the imported sheet (and populate that field with today's date). That action would go to the main table (that has a date-imported column). Then empty the staging table. I don't know how much manual adjustment you need to the raw spreadsheet data but the odds are that something is needed.

For what it is worth, the method you use for the rename of the table is probably the most efficient way to do the rename operation since the only thing that has to be updated is the TableDef structure applying to those names. The table itself doesn't have to be copied.

For the import, the most efficient way would be to not import the spreadsheet via DoCmd TransferSpreadSheet but rather would be to dynamically link the sheet as an external table and then append it to the previously mentioned main table. You could then use

INSERT INTO main-table (list-of-fields) SELECT list-of-fields FROM linked-sheet-table ;

In the above, the list-of-fields would include that extra date field I mentioned plus the 10 or 11 fields from the sheet. This involves the fewest actions that "scramble" the data by copying or rewriting.

 

[The_Doc_Man]

Your reply to DHookem overlapped my response. Having 60 fields of potentially 50 bytes each is 3000 bytes per record. How many records do you normally get? Because keeping them for a few months is going to really quickly run into a capacity issue. One record per day for a month would give you 90K bytes. Multiply that by the number of records per day.

I have created the Module and Macro with information from the internet, but I have not been able to run the program.

Do you get an error message? Why can you not run the program? If it is an error that stops you, tell us the exact error that it gives you.

 

[Mark_]

Nicole, Doc has given you a suggestion that helps with another issue you may have. Is any of the data you receiving numeric? If so, just importing as text won't always give you the results you want. Storing numbers as numbers also normally takes a lot less space than storing as a string.

I'd suggest working out what everything is supposed to look like FIRST, then work out how to do the import. This will help you later when you are doing analysis as you won't have near the issues with bad data.