469,588 Members | 2,809 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

The Database Engine Could Not Lock Table error in Access 2003

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
3 10200
Himmel
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
Himmel
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
14,534 Expert Mod 8TB
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.

Similar topics

reply views Thread by Ian | last post: by
1 post views Thread by Johnny E Jensen | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.