Make Table query & Indexes

[Glen Appleton]

Hi All!

I'm using a Make Table query to update the data in an external (backend) Jet
database from an external SQL (production) database. I'm using the DAO
CreateIndex method to replace the indexes when the query has completed, but
I've run into an interesting problem.

When I try to update the indexes after the query that replaces the existing
table in the external database, I will receive an error saying that the
index already exists. It was my belief that the Make Table queries
destroyed the indexes when they populated the table, is this not true? Is
there a difference in using a Make Table query on for a table in the local
DB as opposed to an external DB?

I've been able to work around the problem by testing the table after the
query to see if the index exists before trying to append a new one, but it
just seems odd. Maybe it has something to do with the method (DAO) I use to
create the index in the first place, I don't know.

Has anyone else run into this situation, and it is documented somewhere for
reference?

- Glen

 

[[MVP] S. Clark]

I don't know why you having such a bugger of a time with the Indexes.

If I reuse a table, like you're doing, I will not delete it and recreate it
everytime. Instead, I empty the table, then Append the new records. This
saves a lot of overhead. See if it will work for you.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Glen Appleton]

Hi Steve,

I've evaluated the Append vs. Make-Table methods for different scenarios.
In this case, I'm just capturing a snapshot of records on our production
server and caching the data to a Jet database for reporting purposes so that
the reports do not impact the production server during business hours. In
any case, since I'm not manipulating the data but rather just "dumping"
records, the Make-Table query has proven more efficient.

After more testing, this is what I've learned about the Make-Table query in
different scenarios:

Query & destination table in same .mdb file = Indexes always removed
Query & destination table in different .mdb files + query changed since last
run = Indexes removed
Query & destination table in different .mdb files + query unchanged since
last run = Indexes intact

So it seems that if the third scenario is true, the Make-Table query acts in
a way that emulates a combination of Delete and Append queries to repopulate
the data. Indexes remain intact on the tables and the index pointers are
updated correctly. Several tests on different machines have provided the
same results.

I'm just curious if this is by design, and if it's documented.

- Glen