Query Errors When Replaceing Tables in Access

[SherpaBoy]

I created a master MDB (Week01.mdb) with a "One Week" database, an
import of a DBF. It has 14 queries built on this one table (Wk01).
All 14 are used for viewing and editting based on an external TIFF
image database application.

The issue is that I get a new DBF every week that needs to be viewed
and editted in this manner. Let's call the new weekly DBF's Wk02.DBF,
Wk03.DBF, etc.

When I import a week into a copy of the master MDB and try to change
the name of the tables, some of the queries accept the new table as a
replacement of the original and some don't.

Is there a better way to do this? Previous posts indicate that viewing
SQL, using an editor to do search and replace and then pasting over the
existing SQL is an option. (I tried it, it works but it will get VERY
tedious.)

 

[Guest]

What about using a name like MyWeek and overwriting it each week with a copy
of the new week's data.

 

[SherpaBoy]

I tried that, in fact that is part of my current work-a-round.

The issue is that when I Get External Data, there is no option as to
what to call the DBF when it gets imported. Likewise if I rename the
DBF to the name of the table already in the MDB, it automatically
renames the incoming DBF with a 1 appended to the end.

The actual way I have been using is as follows;
copy the database
import the DBF
open all queries in design view
rename the the new table (from DBF) to the name of the existing table
(eg, rename Week to Weeka, rename Wk03 to Week, delete Weeka)
then close all design views
compact the DB

 

[Guest]

Do not import - link.