Help in deleting tables used by queries

[MechEngr]

Looking for a little help with deleting a table. I have six tables that I
have two versions of... one new and one old, ie. HQMembers & HQMembers_OLD.

In my procedure I wish to update the database by deleting the “_OLD†tables,
rename the Current Tables by appending the “_OLDâ€, and then import six new
tables.

This works fine except for two tables that are used in queries which are
comparing the new and old tables. For these two tables, I get the warning
message the table can not be unlinked and is in use by another process. By
the time I execute this procedure, all recordsets and forms are closed…
except for the two queries that acting on the tables.

My question is: Is there a way shut down the queries until I can update the
tables, or some other work around. I guess I could always set up a couple of
routines to purge and then copy data from one table to the next, but simply
deleting and renaming is so much cleaner.

Your help is appreciated in advance.


On Error Resume Next
DoCmd.SetWarnings False

For i = 0 To 5
strOldTableName = "HQ" & DataFileArray(i) & "_OLD"
strNewTableName = "HQ" & DataFileArray(i)

DoCmd.DeleteObject acTable, strOldTableName

DoCmd.Rename strOldTableName, acTable, strNewTableName

DoCmd.TransferDatabase acImport, "dBase 5.0", HQDataPath,
acTable, DataFileArray(i), "HQ" & DataFileArray(i)
Next

MsgBox "The Database has been updated"
On Error GoTo 0
DoCmd.SetWarnings True

The_End:

DoCmd.RunCommand acCmdRefresh
End

 

[Ken Snell \(MVP\)]

Why not keep tables in the database where you'll put the "OLD" data (delete
any data already in "OLD" tables, then append the data into those tables
from the current tables using an append query), then delete the "OLD" data
form the current tables (use delete queries), and then import your new data
to the current tables? No need to delete tables, just move the data around.

 

[MechEngr]

Thanks Ken. That is what do. I was hoping for the easy three line solution.

 

[MechEngr]

Well, where there is a will, there is a way... I figured it out! YEA!.

The querry that was locking the tables was used as the data source of a
form. I changed the forms RECORDSET TYPE to "Dynaset (Inconsistent Updates)"
and it now works. The tables are unlocked and can be deleted, renamed, etc.

The interesting thing is that the form did not even have to be opened to
lock the data! When I ran the script, I had everything closed! There are
about a half dozen posts here that describe similar problems. I will leave
it to the MVPs to determine if what I have done is a good thing (there may
trouble lurking out there that I am not aware of) and pass this tip on… or
not.

Thanks anyway.

 

[MechEngr]

Well, I was fooling myself... there does not seem to be a way to get around
this. I will follow the recommendation of delete and then copy records.

Sorry for the false start.

"