VB6 run time error 3218

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

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.

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

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

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.

Have you try the Idle method?

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

Adjusted points to 200

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!)

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

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.

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?

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.

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.

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

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.

How is it woriking now?

How is it woriking now?

mathies,

What are you trying to do?

MHef, please reject mathies answer and grade me.

MHef, are you still alive?

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

Comment accepted as answer

Yea Ian! Justice does prevail!!

Thanks Ian.