473,224 Members | 1,944 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

CurrentDB and database exclusive lock

Hi all,
I have a problem which I can't figure out what is it caused by and how to solve it as I never expericed it before even though I've been using same approach many times before.
I have front end database with form that creates back end db, inserts new tables in BE and then creates linked tables in FE linked to BE. It used to work but stopped couple of days ago.
Ok, so I have that form with button calling the code - code is located in class objects rather than modules.
Everything goes just fine until I call a public function located in a module:
Expand|Select|Wrap|Line Numbers
  1. Public Function FindDelQryDef(strQueryName As String, blnDelete As Boolean) As Boolean
  2. 'Returns True if table or query exist in currentDb
  3.     Dim qryd As DAO.QueryDef
  4.     Dim blnResult As Boolean
  5.     blnResult = False
  6.     For Each qryd In CurrentDb.QueryDefs
  7.         If qryd.Name = strQueryName Then
  8.             blnResult = True
  9.             If blnDelete = True Then CurrentDb.QueryDefs.Delete (strQueryName)
  10.             Exit For
  11.         End If
  12.     Next qryd
  13.     Set qryd = Nothing
  14.     FindDelQryDef = blnResult
  15. End Function
  16.  
When it executes 'CurrentDb.QueryDefs.Delete (strQueryName)' the database locks itself and when I try to save changes made during debugging of my code, it says: "Microsoft Office Access can't save design changes or save to a new database object because another user had the file open. To save changes or to save to a new object, you must have exclusive access to the file."

It must be associated with command Application.CurrentDb, because when I earlier tried to exectue:
strThisDb = CurrentDb.Name , I was getting same error message when tried to save changes afterwards.
Even if I do it through

Expand|Select|Wrap|Line Numbers
  1. Set objThisDb = CurrentDb()
  2. strThisDb = objThisDb.Name
  3. objThisDb.Close
  4. set objThisDb = Nothing
it doesn't change anything and I'm still unable to retrieve an exclusive access after code is executed.
Can anyone help please? It's killing me!
Aug 2 '10 #1
7 9405
Stewart Ross
2,545 Expert Mod 2GB
Hi. If you are sure there are no other users active when you are trying to run the DB updates it suggests to me that the database is remaining locked as a result of an interrupted process leaving the .LDB file in an inconsistent state. This happens if a user has to 'kill' the database using Task Manager, say, or if their PC or the network crashes before the lock file is released, leaving it with an apparent lock that does not match any current active process, and a lock-file which you cannot delete because it is still classed as in use by another process.

If the lock file is present when you do not have the database open yourself then either someone else is using the database, or you have had an instance of an interrupted Access occurrence not releasing the database.

Check the lock file (the .LDB file) which is associated with your DB - you can open it read-only using Word, for example, to see who is active, or apparently active, at the time.

-Stewart

PS it is unusual to build a front-end DB that users share that creates a back-end DB as a result of some administrative process. This defeats the purpose of having a front-end DB, as you cannot create or modify certain objects when the DB is in shared mode. Is there a reason why this cannot be done using a separate admin front-end which is not shared by other users?
Aug 2 '10 #2
Hi Stewart,
the reason for FE creating BE is that the database I'm working on links multiple data sources and compiles them into one - what I call "parent" database. That parent database is an existing database. To start the process the admin performs few tasks: points to the parent database/table (which creates a link in FE database, and creates BE to store historical compiled updates) and then sets up all links to feeding data sources, which can be Excel spreadsheets, other database tables etc. (admin sets up the mapping using designated form to establish relationships between fields in parent table and data sources). This process is done only once by the admin, after which users use created mappings to import the data, compile it, and produce reports. Hope that gives you bit more understanding of the whole process..

But anyway, I've checked the .ldb file after the database gets locked up and it looks like this:
PE11113629 Admin PE11113629 admin

where PE11113629 is a number of my computer
before I run the code that locks it, it looks like this:
PE11113629 Admin

FYI, I'm the only user opening the database - it sits on my portable hard drive. The code doesn't attempt to kill any of the processes - in fact what I tried before, I placed the code:
strDbName = CurrentDb.Name
right at the top of the Sub and it did the same thing: I opened the database, and went straight to executing that line and soon as VBA chewed it up it locked the db.
At some stage I was able to fix the problem by replacing that line with:
Set objDB = CurrentDb
strDbName = objDB.Name
objDB.close
Set objDB = Nothing
and it worked for couple of days, but one day when I executed same code, it locked the db again. What's even more strange is that other lines of code using CurrentDB started doing the same thing - like the one I sent in my previous post. Strangely enough, I am still able to open tables in design mode and modify their content - it seems to affect only forms and VBA modules.
I thought this was happening because the code was executed from "on button click" event within the form, and the database takes over the database to create another instance of MS Access to be able to create object CurrentDb - or something like that.
I found a code that does the trick:
Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim ws As PrivDBEngine
  3.     Set ws = New PrivDBEngine
  4.     Set db = ws(0).CreateDatabase(strNewPathFile, dbLangGeneral)
  5.     ws(0).Close
  6.     Set ws = Nothing
  7.     Set db = Nothing
but unfortunately it doesn't work with QueryDefs, which I'm using further down in my code (.CreateQueryDefs, QueryDefs.Delete).
Now why PrivDBEngine doesn't lock the database and CurrentDb does?
Any help will be much appreciated.
Aug 3 '10 #3
Anyone help, please? this is killing me! I tried everything and it still doesn't work! I am desperate!!
Aug 26 '10 #4
Stewart Ross
2,545 Expert Mod 2GB
Hi. It's hard to advise you on this one as it is not at all obvious what is going on. Is there an LDB file present when you exit from the database? If there is, can you delete it? If you can, it is clearly not locked by any process at the time.

If the LDB is not present, then the lock file is being closed normally when you exit, and the two user-instances of your own access to the system you see in your lock file may well be arising because of something you are doing in code.

I have myself come across this on one previous occasion, when using VBA to connect to tables within the database using an ADO connection object. This created a separate instance of myself in the lock file and prevented me from modifying forms etc until I rewrote the code concerned without the separate connection.

-Stewart
Aug 26 '10 #5
NeoPa
32,554 Expert Mod 16PB
I'm not positive, but this seems as if it may be related to a fairly common problem using CurrentDB(). Most people consider this is a reference to an object, when it is really a function that returns an object. Best practice is to call the function once, but to save the returned object for use in your code. Repeated calls to the function are known to cause issues. I strongly recommend looking through the Help topic on this as it makes some of the issues clearer.
Aug 26 '10 #6
NeoPa: it's got something to do with CurentDB() - that's fact. The database is locked when it's called first time, so it's not that I'm trying to open same object multiple times. I also tried:
Set objDB = CurrentDb
but it does the same thing.

Stewart: the database is located on my hard drive locally, I'm positive noone else is using it at the same time. When I close the db, the ldb file is not present. It appears as if access was opening second instance of same database in a background to create (?) object CurrentDb and put that instance in exclusive mode, but how do I close that instance?
Aug 27 '10 #7
NeoPa
32,554 Expert Mod 16PB
Sorry. I wish I could help more. Normally such a problem disappears when object variables are used and CurrentDB is only called once. It's hard to say what else may be causing an issue trying to work via a web page.
Aug 27 '10 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: M Wells | last post by:
Hi All, I have a database that is serving a web site with reasonably high traffiic. We're getting errors at certain points where processes are being locked. In particular, one of our people...
5
by: xixi | last post by:
hi, i am using db2 udb v8.1 on windows, i create a index on a unique value column on a table to try to create row lock, here is what i do , sql = select value from table where id=1 for update,...
9
by: Lara | last post by:
Hello freaks, we have many problems with our online reorg and no idea how to resolve it. We had to do an online reorg beacause of 24 h online business. We start the reorg-statements (table by...
2
by: Mike | last post by:
I am trying to decipher deadlock events in a event monitor. I am trying to determine what type of lock was held. If I look at the data elements it shows LOCK MODE 5 LOCK MODE REQUESTED 3 I can...
3
by: Steve Crawford | last post by:
When I run: vacuumdb --full --all --analyze --quiet on my database cluster it will complete in < 2 minutes (this cluster is a few million total rows and ~2GB). After testing, I set this up as...
5
by: Bob Bins | last post by:
Is there a way to create a shared lock using the Monitor class or any other synchronization class in .NET? So I have one resource that may have multiple threads using it at once but a second...
1
by: sajithamol | last post by:
Suppose one user write a query which will fetch huge number of rows from a table.Now when the query is trying to fetch nth to (n+t) number of rows ,at that time another user update some rows which...
0
by: Skeptical | last post by:
Hello, We have MS SQL Server 2000 and recently we found out that right clicking on a database and selecting properties would lock up the enterprise manager. We have 80 Databases 4 GB Memory 2...
4
by: quincy451 | last post by:
I am running MS SQL 2000 server. The table involved is only about 10,000 records. But this is the behavior I am seeing. The local machine is querying the table looking for a particular record....
3
by: Arun Srinivasan | last post by:
Please correct me if I am wrong 1. no 2 processes can have exclusive lock on same object (by object, same row or same table) 2. on deadlock incident between 2 processes only one of them will be...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.