Solved

VB6 run time error 3218

Posted on 2000-02-29
25
472 Views
Last Modified: 2013-12-25
I have a project coded in VB6 that issues a Run time error upon executing the recordset.update method:

3218, Couldn't update; currently locked

I know that the record isn't locked, nor is any other record or table.  Here's part of the code:

rstIngred is the MS Access table that is getting thousands of records added to it so this code resides inside a loop.

rstIngred.AddNew
rstIngred!Formula = NewFormCode
rstIngred!Line_No = Ingrs
rstIngred!Instruct = False
rstIngred.Update  '<=ERRORS OUT HERE

When I trap the error with On Error Goto and then Resume, I notice that it  pauses from time to time then continues where it would otherwise probably error out (remember, this is adding thousands of records) but then, before it is done, the entire program locks up hard.  Taking some previous advice. I also tried placing a 'DoEvents' inside the loop but it doesn't help.

Any help would be appreciated
0
Comment
Question by:MHef
  • 13
  • 7
  • 2
  • +3
25 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2570457
Show us how you open your recordset. Also, is this code ADO or DAO?
0
 

Author Comment

by:MHef
ID: 2572663
I'm using DAO.  At least I guess I am since I'm using the following
code to open the database and table:

Set WS = DBEngine.CreateWorkspace("MainWS", "admin", vbNullString)

Set InstallDB = WS.OpenDatabase("install.mdb", False, False) ', sConnect)

Set rstI_Ingr = InstallDB.OpenRecordset("I_Ingr")

FYI, this program will be used by a single user on an isolated
machine.  Hopefully this helps.  I'm relatively new to VB so please excuse any ignorance on my part.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2572981
I see no problems. Try the code bellow (changing the path to NWIND.MDB) which I use to test. Is it normal that the recordset you use in your 2 messages have different names?

Private ws As DAO.Workspace
Private InstallDB As DAO.Database
Private rstI_Ingr As DAO.Recordset

Private Sub Command1_Click()
With rstI_Ingr
..AddNew
!lastname = "Moreau"
!firstname = "Eric"
![BirthDate] = Date - 1
..Update  '<=ERRORS OUT HERE
End With
End Sub

Private Sub Form_Load()
Set ws = DBEngine.CreateWorkspace("MainWS", "admin", vbNullString)
Set InstallDB = ws.OpenDatabase("D:\Microsoft Visual Studio\VB98\nwind.mdb", False, False)     ', sConnect)
Set rstI_Ingr = InstallDB.OpenRecordset("Employees")
MsgBox rstI_Ingr.RecordCount
End Sub
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:MHef
ID: 2573974
First of all, sorry, but I copied and pasted the wrong line from the code:

This line: Set InstallDB = WS.OpenDatabase("install.mdb", False, False) ', sConnect)

Should be: Set InstallDB = WS.OpenDatabase("weighsys.mdb", False, False) ', sConnect)

AND

This line: Set rstI_Ingr = InstallDB.OpenRecordset("I_Ingr")

Should be: Set rstIngred = InstallDB.OpenRecordset("Ingred")

I do have 2 Access databases open.  I'm using the tables in install.mdb
to update tables in weighsys.mdb.

I tried your code and then placed the record writing code (from .addnew to .update) inside a loop writing 100,000 dummy records out and it worked fine.  Also tried same on my table `Ingred' in
the weighsys.mdb.  It also worked fine in this small program with only `weighsys.mdb' open and `Ingred' table open.

Perhaps it's the number of databases (2) and tables (20) open that's causing the problem?

I've read a bit on ADO vs DAO and ADO is newer and recommended.  Would you suggest recoding this for ADO?

Thanks


0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2574028
For new applications: go ADO for sure!
For existing apps, it depends on your existing code!

I use ADO since a year and I'm really happy with it.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2574038
Have you try the Idle method?

After each update, call this method:
rstIngred.Update  
DBEngine.Idle
0
 

Author Comment

by:MHef
ID: 2576442
Adjusted points to 200
0
 

Author Comment

by:MHef
ID: 2576443
Tried the DBEngine.Idle Method after every .update but didn't help.  
In my earlier comments, you've seen how I open a database and then a table.  When I use a table, for reading records, writing, adding or removing I use the .Addnew, .Edit methods and so on.  If I switched to from DAO to ADO, what coding changes would be involved and could I still use the .edit .addnew .seek, .index etc methods-my code is now chocked full of them.  Just thinking that this may be be an answer to my problems.

(I increased the points to 200 which is what I assume one earns upon answering a question.-thanks for the time & the help!)
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2576614
The thing is that I'm not sure that ADO will solve the problem. I don't want you to work for a couple of days to come back with the same problem but different technologies.

To answer your question about moving from DAO to ADO, in the code you show here, you have just the database opening to change because the addnew, update are the same.

Instead of using the AddNew method, use the Execute method like this:
Private Sub Command1_Click()
'With rstI_Ingr
'.AddNew
'!lastname = "Moreau"
'!firstname = "Eric"
'![BirthDate] = Date - 1
'.Update  '<=ERRORS OUT HERE
'End With
InstallDB.Execute "INSERT INTO Employees(Lastname, FirstName, BirthDate) Values('Moreau', 'Eric', '2000/01/01')"
End Sub
0
 

Author Comment

by:MHef
ID: 2591677
I tried the 'InstallDB.Execute "INSERT INTO..."'
Still got the currently locked error. Also, the program went much much slower.  I'm recoding it to ADO, at least the portion giving the error.  If it works with no error, I'll recode the entire project since EVERYONE says ADO is the way to go.  Problem was I had MSADO15.DLL version 2.0 and it didn't support the recordset.seek nor the .index methods.  I'm not sure how people used it without it, keep in mind I'm relatively new at this though, I'm sure there was some way of using indexes and seeking.  At any rate, I got version 2.1 which has .Index and .Seek methods but I now have a problem with the seek.  I have an Access Table with an index made up of 3 fields.  With DAO, the seek method would take a parameter for each field:

rsMyTable.Seek "=", Value1, Value2, Value3

ADO I think only wants 1 parameter and errors out if I apply the same syntax as with DAO.  Am I correct here, does it only take 1 parameter for the indexed value, and if so, how do I pass along each field value in ADO's seek?

I think once I get this done, I can test if ADO makes a difference.  Thanks again for any help.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 200 total points
ID: 2591803
You have a number of consideration to use the Seek method. See this extract for the Help file:

«Seek Method
Searches the index of a Recordset to quickly locate the row that matches the specified values, and changes the current row position to that row.

Syntax

recordset.Seek KeyValues, SeekOption

Parameters

KeyValues   An array of Variant values. An index consists of one or more columns and the array contains a value to compare against each corresponding column.

SeekOption   A SeekEnum value that specifies the type of comparison to be made between the columns of the index and the corresponding KeyValues.

Remarks

Use the Seek method in conjunction with the Index property if the underlying provider supports indexes on the Recordset object. Use the Supports(adSeek) method to determine whether the underlying provider supports Seek, and the Supports(adIndex) method to determine whether the provider supports indexes. (For example, the OLE DB Provider for Microsoft Jet supports Seek and Index.)

If Seek does not find the desired row, no error occurs, and the row is positioned at the end of the Recordset. Set the Index property to the desired index before executing this method.

This method is supported only with server-side cursors. Seek is not supported when the Recordset object's CursorLocation property value is adUseClient.

This method can only be used when the Recordset object has been opened with a CommandTypeEnum value of adCmdTableDirect.»

There is also an example:

Seek Method and Index Property Example (VB)
This example uses the Recordset object's Seek method and Index property in conjunction with a given Employee ID, to locate the employee's name in the Employees table of the Nwind.mdb database.

Public Sub Main()
   SeekX
End Sub

Public Sub SeekX()
Dim rst As ADODB.Recordset
Dim strID As String
Dim strPrompt As String
strPrompt = "Enter an EmployeeID (e.g., 1 to 9)"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open "employees", _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=c:\temp\northwind.mdb;" & _
            "user id=admin;password=;", _
            adOpenKeyset, adLockReadOnly, adCmdTableDirect

' Does this provider support Seek and Index?
If rst.Supports(adIndex) And rst.Supports(adSeek) Then
rst.Index = "EmployeeId"
' Display all the employees.
      rst.MoveFirst
      Do While rst.EOF = False
         Debug.Print rst!EmployeeID; ": "; rst!firstname; " "; _
                     rst!LastName
         rst.MoveNext
      Loop
   
' Prompt the user for an EmployeeID between 1 and 9.
      rst.MoveFirst
      Do
         strID = LCase(Trim(InputBox(strPrompt, "Seek Example")))
         ' Quit if strID is a zero-length string (CANCEL, null, etc.)
         If Len(strID) = 0 Then Exit Do
         If Len(strID) = 1 And strID >= "1" And strID <= "9" Then
            rst.Seek Array(strID), adSeekAfterEQ
            '===================== =============
            If rst.EOF Then
               Debug.Print "Employee not found."
            Else
               Debug.Print strID; ": Employee='"; rst!firstname; " "; _
               rst!LastName; "'"
            End If
         End If
      Loop
End If

rst.Close
End Sub
0
 

Author Comment

by:MHef
ID: 2593362
OK, I think I get it but I'm having trouble with with VB saying that the provider doesn't support Index and Seek.  Here's the code of a very small program I coded:

Private cnn As ADODB.Connection
Private rstFormula As ADODB.Recordset
Private rstIngred As ADODB.Recordset

Private Sub Form_Load()
   Dim cnnString As String
   Set cnn = New ADODB.Connection
   cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=d:\deleteme\weighsys.mdb"
   cnn.Open cnnString
   Set rstFormula = New ADODB.Recordset
   Set rstIngred = New ADODB.Recordset
   rstFormula.Open "Formula", cnn
   rstIngred.Open "Ingred", cnn
   
   ' Both of the following displays FALSE
   MsgBox ("Supports Index:" & rstFormula.Supports(adIndex))
   MsgBox ("Supports Seek:" & rstFormula.Supports(adSeek))
   
   rstFormula.Close
   rstIngred.Close
End Sub

Why is it saying that 'Microsoft.Jet.OLEDB.4.0', won't support Index and Seek?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2594022
You didn't read all! One of the line says: «This method can only be used when the Recordset object has been opened with a CommandTypeEnum value of adCmdTableDirect.» Check the example again.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2598529
There is one more thing to add in - a call to Freelocks.

This is a DAO call that releases any locks you may have on the DB.  We had a similar problem with DAO and DB5 on an access DB, and adding Freelocks cleared it up.

rstIngred.AddNew
rstIngred!Formula = NewFormCode
rstIngred!Line_No = Ingrs
rstIngred!Instruct = False
rstIngred.Update  '<=ERRORS OUT HERE
Freelocks

Note that DAO can occasionally impose locks that create this error after a Seek, or after changing the Index on an access table.  These locks can even be held for calls that have been made on another table in the same Access DB.

We have implemented a set of calls (RSMUpdate, RSMSeek, RSMSetIndex) that do the appropriate function on the passed recordset or table, and call Freelocks after they are completed.

0
 

Author Comment

by:MHef
ID: 2601979
thanks bhess1 but I'm in the middle of switching the offending code to ADO.  But if I can't, I'll try freelocks-this could have been the answer all along.  At any rate, hate to be a pain but in reference to emoreau's comment, I still can't seem to get Seek and Index to work, says it isn't supported in following simple program:

Public Sub Main()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open "Formula", _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:\deleteme\weighsys.mdb;" & _
   "user id=admin;password=;", _
   adOpenKeyset, adLockReadOnly, adCmdTableDirect

Debug.Print rst.Supports(adIndex) ' prints False
Debug.Print rst.Supports(adSeek) ' prints False
Debug.Print rst!Name '  This does work, prints
' value in Name field in 1st record, just making
' sure table really is open

rst.Close
End Sub

Know I'm being a pain, thanks
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2606741
MHef,

I just done an interesting test.

The code you try works perfectly with an Access 2000 table but not with an Access 97 table.

The test I did was to open the same database (nwind.mdb and nwind2000.mdb) on the same PC.

With Access 97 (nwind.mbd) the result printed to the immediate are False and with Access 2000 (nwind2000.mdb) the results are true!

So you might consider upgrading your database to A2K.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2736094
How is it woriking now?
0
 
LVL 1

Expert Comment

by:mathies
ID: 2753068
How is it woriking now?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2753636
mathies,

What are you trying to do?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2979622
MHef, please reject mathies answer and grade me.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 3096214
MHef, are you still alive?
0
 
LVL 14

Expert Comment

by:mcrider
ID: 3096511
emoreau, Looks like you may get rooked on this one... :-(
0
 
LVL 5

Expert Comment

by:ianB
ID: 3100562
Comment accepted as answer
0
 
LVL 14

Expert Comment

by:mcrider
ID: 3100664
Yea Ian! Justice does prevail!!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 3102138
Thanks Ian.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
to transfer string from C lanaguage to VBA 4 64
Spell Check in VB6 13 112
Export Data to Different .csv Files 26 103
Formula problem with Excel attachment 6 26
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question