By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

The Database Engine Could Not Lock Table error in Access 2003

P: 21
I have added a section of code to a function that is designed to copy data from several tables and place them into a single table. The new table already contains a unique ID and name, and I am working on adding data from the second table to it.

The problem is that the second table has multiple records for each ID. For example, review this sample data:

New Table:
Expand|Select|Wrap|Line Numbers
  1. ID NAME OBJ1
  2. 1 Billy
  3. 2 Bobby
  4. 3 Sue
  5.  
Second Table (data to be merged into New Table):
Expand|Select|Wrap|Line Numbers
  1. ID OBJ
  2. 1 Item1
  3. 1 Item2
  4. 1 Item3
  5. 2 Item1
  6. 3 Item1
  7. 3 Item1
  8.  
As you can see, the ID's match, but there are more objects than columns. The output I'm going for is this:

Expand|Select|Wrap|Line Numbers
  1. ID NAME OBJ1 OBJ2 OBJ3
  2. 1  Billy item1 item2 item3
  3.  
To accomplish this, I am using the following code (recordsets at. al. already defined and parsed):
Expand|Select|Wrap|Line Numbers
  1. with rs
  2. 'Number of OBJ columns there are.  Start with 1
  3. maxobj = 1
  4.  
  5. Do Until .EOF
  6. r = 1
  7. z = 1
  8. set rstemp = dbo.OpenRecordSet("SELECT * FROM NEWTABLE WHERE ID=" & rs![ID])
  9.  
  10. For Each FLD in rstemp.Fields
  11. strname = "OBJ" & r
  12. if FLD.Name = strname then
  13.      r = r + 1
  14.      if Len(FLD.Value) > 0 then
  15.           z = z + 1
  16.      End if
  17. End if
  18. Next
  19.  
  20. set rstemp = Nothing
  21.  
  22. if z > maxobj then 
  23.      dbo.Execute _
  24.      "ALTER TABLE NEWTABLE " & _
  25.      "ADD OBJ" & z & " INTEGER"
  26.      maxobj = z
  27. End if
  28.  
  29. dbo.Execute _
  30.      "UPDATE NEWTABLE SET OBJ" & z & "=" & rs![OBJ] & _
  31.      "WHERE ID = " & rs![ID]
  32.  
  33. .MoveNext
  34. Loop
  35.  

This code will first check if OBJ1 exists. If it does, check if there is anything in it. If there is data in this field, check for OBJ2, then OBJ3, etc. until we either find an empty field, or until we get to the end of the record.

This code appears to be working properly. First pass says OBJ1 is empty, and data is properly written to OBJ1. Second pass says OBJ1 has data in it, so z is set to 2.

Since z is larger than our OBJ counter (1), the code attempts to ALTER TABLE to add the OBJ2 column. Once this is complete it will then write data to the new column. This is where the code breaks.

"The database engine could not lock table NEWTABLE because it is already in use by another person or process."

The script can update the table just fine. But it cannot Alter it? Am I missing something here?

PS sorry if this is a bit long-winded, but hopefully I got the problem across. :)
May 15 '07 #1
Share this Question
Share on Google+
3 Replies


P: 21
I've discovered that if I remove the rstemp code:
Expand|Select|Wrap|Line Numbers
  1. set rstemp = dbo.OpenRecordSet("SELECT * FROM NEWTABLE WHERE ID=" & rs![ID])
  2.  
  3. For Each FLD in rstemp.Fields
  4. strname = "OBJ" & r
  5. if FLD.Name = strname then
  6.      r = r + 1
  7.      if Len(FLD.Value) > 0 then
  8.           z = z + 1
  9.      End if
  10. End if
  11. Next
  12. set rstemp = Nothing
  13.  
...then I can ALTER and UPDATE just fine. So it looks like "set rstemp = nothing" is not releasing the lock on the table. Is there another way to release it?
May 15 '07 #2

P: 21
And I answered my own question. :)

I was able to resolve the problem by encapsulating the rstmp data with a WITH statement, and ending with a .CLOSE

Expand|Select|Wrap|Line Numbers
  1.       set rstemp = dbo.OpenRecordSet("SELECT * FROM NEWTABLE WHERE ID=" & rs![ID])
  2.  
  3. With rstemp 
  4.      For Each FLD in rstemp.Fields
  5.       strname = "OBJ" & r
  6.       if FLD.Name = strname then
  7.            r = r + 1
  8.            if Len(FLD.Value) > 0 then
  9.                 z = z + 1
  10.            End if
  11.       End if
  12.       Next
  13. .Close
  14. End With
  15.  
  16.        set rstemp = Nothing
  17.  
This releases the flock on the NEWTABLE table, allowing the script to ALTER it.

Thanks for all the great advice throughout these forums. It helped me figure this one out. :)
May 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Glad you got it worked out and all on your own too.

Mary
May 17 '07 #4

Post your reply

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