Prev: Not Compacting Correctly
Next: simple loop code needed
From: alex on 5 May 2010 09:42
Unlock table

Hello,
Using Access ’03…

I have some code that exports various qdf results to Excel. They work
pretty well, but sometimes my on error code fires because, e.g., the
user already has an xls doc opened by the same name or the xls doc
they’re trying to name contains invalid characters, etc.

When the on error code fires, the first thing I do is delete the two
qdfs created in code:
…
Exit Sub
errHandler:
'On Error Resume Next does not ignore ‘Item not found in this
collection’ for some reason??
‘workaround
For Each qdf In dbs.QueryDefs
If qdf.Name = strQDF1 Then 'delete first qdf
dbs.QueryDefs.Delete (strQDF1)
Exit For
End If
Next
'you need 2 for loops because on error resume next will not ignore
an error if a qdf does not exist!
For Each qdf In dbs.QueryDefs
If qdf.Name = strQDF2 Then 'delete second qdf
dbs.QueryDefs.Delete (strQDF2)
Exit For
End If
Next

In most cases the code above cleans everything up. On occasion,
however, if the user does not rerun the code that creates the qdfs
(the code above my errHandler), the table utilized in their sql
strings remains locked and throws other errors!

What I need to do, in addition to deleting the qdfs, is unlock the
table and free it so it can be used by other code.

Any thoughts on how I could to that?
Thanks,
alex
 | 
Pages: 1
Prev: Not Compacting Correctly
Next: simple loop code needed