Neil:
You can rename the tables using the TableDefs collection of the Database
object in DAO. You will need a reference to the DAO object library, of
course. For example:
Function RenameTables()
Dim tdf As TableDef
Dim db As Database
Set db = CurrentDb
Set tdf = db.TableDefs("W eek2Data")
tdf.Name = "Week3Data"
Set tdf = db.TableDefs("W eek1Data")
tdf.Name = "Week2Data"
RefreshDatabase Window
Set db = Nothing
Set tdf = Nothing
End Function
You will need to consider the possibility that Week3Data already exists
before the renaming process begins and how to handle this outcome, as well
as checking to make sure that both Week1Data and Week2Data currently exist.
This can be done by iterating through the TableDefs collection using a For
Each loop and checking the Name property each TableDef, or alternatively by
using error trapping.
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com
This response is supplied "as is" without any representations or warranties.
"Neil10365" <ne*******@gmai l.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
I wonder if someone can help me with a small conundrum I am having.
This is what I want to achieve:
Scenario
--------
Each week, I import an excel spreadsheet called Week1.xls into an
access database. This table is then called Week1Data.
Already in the database is the table from the previous weeks import,
which is compared to the latest file, this file was called Week1Data
last week but is now called Week2Data.
There is a collection of past weeks imports in the database for
historic searching. This builds each week.
Problem
-------
How do I rename last weeks Week1Data to make it Week2Data? ..and then
when I do another import next week, move Week2Data into an archive by
calling it Week3Data etc., So, my latest import will always be
Week1Data with the previous weeks Week1 now renamed Week2 with that
previous weeks Week2 now named Week3.
Thanks for any help!
Neil
