Access too smart

[alex]

Using Access ’03…

I’ve been running into an interesting problem as of late…

I have a database with many tables and queries. Many of the queries
perform calculations based on a table named (e.g.,) tblA.

Every morning an assistant renames tblA to something like
tblA_20081009 (to temporarily archive the table). She then imports
another table and names it tblA.

You know what happens then…any query (it seems) pointing to tblA now
points to tblA_20081009 (it’s strange, but it doesn’t always seem to
do this).

While it’s nice that Access knows to do this, it causes a problem
given the situation above.

Does anyone have a solution (something like an absolute reference),
short of completely deleting the table and then importing a new one?
We usually like to archive the tables for about a week.

alex

 

[Roger Carlson]

Turn off "Name Autocorrect". Tools > Options > General tab.
--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com



Using Access ’03…

I’ve been running into an interesting problem as of late…

I have a database with many tables and queries. Many of the queries
perform calculations based on a table named (e.g.,) tblA.

Every morning an assistant renames tblA to something like
tblA_20081009 (to temporarily archive the table). She then imports
another table and names it tblA.

You know what happens then…any query (it seems) pointing to tblA now
points to tblA_20081009 (it’s strange, but it doesn’t always seem to
do this).

While it’s nice that Access knows to do this, it causes a problem
given the situation above.

Does anyone have a solution (something like an absolute reference),
short of completely deleting the table and then importing a new one?
We usually like to archive the tables for about a week.

alex

 

[MikeB]

Using Access ’03…

I’ve been running into an interesting problem as of late…

I have a database with many tables and queries. Many of the queries
perform calculations based on a table named (e.g.,) tblA.

Every morning an assistant renames tblA to something like
tblA_20081009 (to temporarily archive the table). She then imports
another table and names it tblA.

You know what happens then…any query (it seems) pointing to tblA now
points to tblA_20081009 (it’s strange, but it doesn’t always seem to
do this).

While it’s nice that Access knows to do this, it causes a problem
given the situation above.

Does anyone have a solution (something like an absolute reference),
short of completely deleting the table and then importing a new one?
We usually like to archive the tables for about a week.

alex

Well, you *could" write a small VBA program to create the
tblA_20081009 and then to construct maketable SQL to move the data
into the new table, then you delete the data from tblA. and you are
good to go with importing fresh data into tblA..

There is also an "auto renaming" or "auto name tracking" (can't
remember exactly which) that may be playing a role in what you observe.

 

[alex]

Well, you *could" write a small VBA program to create the
tblA_20081009 and then to construct maketable SQL to move the data
into the new table, then you delete the data from tblA. and you are
good to go with importing fresh data into tblA..

There is also an "auto renaming" or "auto name tracking" (can't
remember exactly which) that may be playing a role in what you observe.- Hide quoted text -

- Show quoted text -

Thanks guys for the help...I feel asking such a silly question
now...It worked fine (not the VBA part Mike!)

 

[Tony Toews [MVP]]

Thanks guys for the help...I feel asking such a silly question
now...

No such thing as a silly question. And I assure you the answer to
your question is not intuitive.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/