May 29, 2007 at 8:01 am
I have some DTS packages that export to Access. The issue with this is if someone is in the table that the package exports to, it places a lock on it and the package times out or fails.
My questions are thus. Is there a way for DTS to detect a lock on an Access DB, and if there is, is there a way to kick out to the user of teh DTS package who may have the invoked the lock?
Also, can DTS invoke a lock on an Access DB so that it is locked by that process and keep it locked until completion of the package? If so, would that cause issues trying to run multiple packages that use the same Access DB??
If anyone can help shed some light on this, that would be greatly appreciated. I am pretty sure that this can not be done, but I was asked to research it further so I thought there was no better place than here to start!!
Thanks,
Steve
May 30, 2007 at 8:32 am
I don't think the actual lock can be detected, but how about the lock's trail?
When an Access db is open (xxx.MDB) a locking file (xxx.LDB) is created right beside it. When the last person in the database closes it, the lock file is removed.
If you open the LDB file (I usually make a copy of the file and change the extension to .txt) you will find a list of the users logged in to the database.
However, the list is an "Add" only list. Example.
user1 logs into xxx.mdb - xxx.ldb is created and user1 is contained in it.
user2 logs into xxx.mdb - xxx.ldb now would contain user1 user2
user 1 logs out of database - xxx.ldb would still contain user1 user2
user1 logs back into database - xxx.ldb now would contain user1 user2 user1
user1 logs out of database - xxx.ldb would still contain user1 user2 user1
user2, the last active member logs out of database - xxx.ldb now would be deleted.
I hope you can get some use out of this info, but it doesn't answer your question.
May 30, 2007 at 8:37 am
Thanks Robert!
Thanks what thought as well. I am pretty sure the only way to get around Access locks is to phase out Access.
Thanks for the tip about changing the copy of the .ldb to .txt to see who is in it.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply