Solved

VB6 run time error 3218

Posted on 2000-02-29
25
465 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 69

Expert Comment

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

Author Comment

by:MHef
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:MHef
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
Have you try the Idle method?

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

Author Comment

by:MHef
Comment Utility
Adjusted points to 200
0
 

Author Comment

by:MHef
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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 69

Accepted Solution

by:
Éric Moreau earned 200 total points
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
How is it woriking now?
0
 
LVL 1

Expert Comment

by:mathies
Comment Utility
How is it woriking now?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
mathies,

What are you trying to do?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
MHef, please reject mathies answer and grade me.
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
MHef, are you still alive?
0
 
LVL 14

Expert Comment

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

Expert Comment

by:ianB
Comment Utility
Comment accepted as answer
0
 
LVL 14

Expert Comment

by:mcrider
Comment Utility
Yea Ian! Justice does prevail!!
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Thanks Ian.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now