473,320 Members | 1,976 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,320 software developers and data experts.

How to solve error 3211 in Access 2000?

I received the error 3211: database engine could not lock table TF_Classification as it's been used by another person or process on the line bolded in the below code: tblWM.Fields.Append fldWM

I already tried 2 ways:
1. to close the recordset and set the object to nothing
2. close the table using the command: DoCmd.Close acTable, "TF_Classification", acSaveYes
but none works, so any idea?

Thank you.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command12_Click()
  2. Dim xlApp As Object
  3. Dim xlwkb As Object
  4. Dim xlsheet As Object
  5. Dim Conn As ADODB.Connection
  6. Dim rs As New ADODB.Recordset
  7. Dim dbWM As Object
  8. Dim tblWM As Object
  9. Dim fldWM As Object
  10. Dim CreateField As Boolean
  11.  
  12. If Combo38.Value = vbNullString Then
  13.     RetValue = MsgBox("You have to enter your initial in the Updater field", vbOKOnly)
  14. Else
  15. Set xlApp = CreateObject("excel.application")
  16. Set xlwkb = xlApp.Workbooks.Open(CurrentProject.Path & "\WM_Feedback.xls")
  17. Set xlsheet = xlwkb.Sheets(1)
  18.  
  19. i = 2
  20.  
  21. DoCmd.Hourglass True
  22. DoCmd.SetWarnings False
  23.  
  24. CapDis$ = vbNullString
  25.  
  26. Set Conn = CurrentProject.Connection
  27.  
  28. Do Until xlsheet.Range("A" & i).Formula = vbNullString
  29.  
  30.     strSQL$ = "SELECT * FROM TF_Classification " & _
  31.     "WHERE (TF_Classification.ISIN = '" & xlsheet.Range("A" & i).Value & "') AND (TF_Classification.Flag_Current = " & True & ");"
  32.     Set rs = New ADODB.Recordset
  33.     rs.Open strSQL$, Conn, adOpenKeyset, adLockOptimistic
  34.     If rs.RecordCount > 0 Then
  35.         strSQL$ = "UPDATE TF_Classification SET TF_Classification.Flag_Current = False " & _
  36.         "WHERE (((TF_Classification.Flag_Current)=True) And ((TF_Classification.ISIN)= '" & xlsheet.Range("A" & i).Value & "'));"
  37.         DoCmd.RunSQL strSQL$
  38.     Else
  39.     End If
  40.  
  41.     rs.Close
  42.     Set rs = Nothing
  43.     j = 0
  44.  
  45.     Do Until xlsheet.Range(Chr(66 + j) & "1").Formula = vbNullString
  46.         CreateField = False
  47.  
  48.         strSQL$ = "SELECT Classification_Fields.* " & _
  49.         "FROM Classification_Fields " & _
  50.         "WHERE (((Classification_Fields.Field_Type)='" & xlsheet.Range(Chr(66 + j) & "1").Formula & "'));"
  51.         Set rs = New ADODB.Recordset
  52.         rs.Open strSQL$, Conn, adOpenKeyset, adLockOptimistic
  53.         If rs.RecordCount = 0 Then
  54.             CreateField = True
  55.         Else
  56.         End If
  57.  
  58.         'DoCmd.Close acTable, "TF_Classification", acSaveYes
  59.  
  60.         If CreateField And xlsheet.Range(Chr(66 + j) & "1").Formula <> "WKN" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD260" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD270A" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD270B" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD332" Then
  61.             Set dbWM = CurrentDb
  62.             Set tblWM = dbWM.TableDefs!TF_Classification
  63.             Set fldWM = tblWM.CreateField(xlsheet.Range(Chr(66 + j) & "1").Formula, dbText, 50)
  64.             tblWM.Fields.Append fldWM            
  65. Rem dbWM.TableDefs.Append tblWM
  66.  
  67.         Else
  68.         End If
  69.  
Nov 25 '11 #1
1 2876
Bumping it cause I still didn't find the solution so would need help
Nov 30 '11 #2

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

Similar topics

3
by: Jay | last post by:
I previously posted this question under Visual Basic newsgroup, but was advised to re-post here. I'm hoping someone can help me solve an issue I'm having with VB.Net and Access 2000. Here's...
6
by: Uttam | last post by:
Hello, I am using the code from Chapter 17 specifically the code from the font frmListFonts in my application. I have taken care to copy all the relevant modules / class modules into the...
4
by: Corepaul | last post by:
I am a beginner, using Access 2000. I am having difficulty with the Visual Basic Help. Specifically, using the Index tab, when I enter "OpenRecordset" in the keyword field and click the Search...
6
by: beav At wn DOT com DOT au | last post by:
Students at the school where I work are getting the error "Application-defined or object-defined error." when trying to document their Access databases. If you open the documenter window, select a...
3
by: MT 2MB | last post by:
I created an access journal entry program many years ago. It works for all and was working for an individual until recently. The report shows ?Name! for every instance it repeats in the report. ...
0
by: Colleyville Alan | last post by:
My app is giving me this error. Run-time error 3211: The database engine could not lock table 'Sorted_Template' because it is already in use by another person or process. When I run the app...
4
by: Barry Young | last post by:
I have Access 2000 installed on a machine and I create the MDE just fine. When I copy the MDE file over to another machine that has Access 2000 installed, the references are hosed and I get unable...
0
by: Gary Carson | last post by:
Can anyone tell why the query below would throw a divide-by-zero error? The only reason I can see for the error happening would be if SUM() came out to be zero, but this never happens with the...
4
by: deko | last post by:
I'm trying to import an Excel Spreadsheet into an Access Table but sometimes things get buttered up if the spreadsheet does not have the right columns. So I want to drop the Access table and...
15
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.