We help IT Professionals succeed at work.

VB6 run time error 3218

MHef
MHef asked
on
627 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
Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Show us how you open your recordset. Also, is this code ADO or DAO?

Author

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
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


Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Have you try the Idle method?

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

Author

Commented:
Adjusted points to 200

Author

Commented:
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!)
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
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.
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.
Brendt HessSenior DBA
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
How is it woriking now?

Commented:
How is it woriking now?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
mathies,

What are you trying to do?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
MHef, please reject mathies answer and grade me.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
MHef, are you still alive?

Commented:
emoreau, Looks like you may get rooked on this one... :-(

Commented:
Comment accepted as answer

Commented:
Yea Ian! Justice does prevail!!
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Thanks Ian.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.