Queries & table locking

[Tony]

Hi all,

I've got a database that, via a script called by a Windows scheduled task,
gets data from a Progress database on a nightly basis. In a nutshell, the
script opens the database, runs queries to repopulate tables, then cleans up
& closes. The process usually runs without any problems. However, from
time to time running one of the queries generates an error:

// Could not lock 'tblName'; currently in use by 'admin' on machine
'machineName'

When this happens, a lock file is left behind and I have to reboot the
machine to recover. The query is a make-table query, so it's deleting the
table then recreating it. At this point, I can't put my finger on why the
table shows that it's in use. The query usually takes ~ 10 minutes to run
and returns ~ 200,000 records from the source database. So, I have a few
questions:

1) Is there any way to get more information as to the table being in use,
such as the time admin accesses the table or the process (backup or anything
else) running as admin that is accessing the table?

2) Check to see if admin is accessing the table before running the query and
forcing admin to let go of it?

3) If instead of running a make-table query, I ran delete * from tableName
then an append query, would I avoid needing to lock the table?

I'm using Access 2003 running on a Windows Server 2003 machine.

TIA

 

[Dale Fye]

Generally, when I have received a similar error, there is no one else in the
database.

As I recall, unless you are running with workgroup security, Access always
interprets the current user as admin.

I generally go with Option #3.

HTH
Dale

 

[Tony]

Dale,

Thanks for the reply. Have you found that option 3 has consistently worked,
or is it still hit & miss, just a little less so? We're not using workgroup
security, as users do not use the database; it simply serves as a source for
populating combo boxes in another app.

Thanks,

Tony

 

[Dale Fye]

I almost always go with number three.

Occassionally, I will actually put all of these types of tables in a
separate backend, so they don't bloat the main application or database file.
When you do this, it is easier to clean up that application by doing an
compacting operation.

Dale

 

[Tony]

Thanks again, Dale. I'll give it a shot. I appreciate the suggestions.

Tony