Problem with .AddNew using Access

[Carl Provost]

I'm having a problem with something very simple. I'm using an Access 2000 database, with a Visual Basic 6.0 front end. I'm opening the connection like this:


Dim cnData as ADODB.Connection
Dim rsCustomers as ADODB.Recordset




... then in the Form Load Event


set cnData = new ADODB.Connection
cnData.Provider = "Microsoft.Jet.OLEDB.4.0"
cnData.ConnectionString = "Data Source= <path to the db>"
cnData.Open

set rsCustomers = new ADODB.Recordset
rsCustomers.Source = "SELECT * FROM Customers"
rsCustomers.ActiveConnection = cnData
rsCustomers.CursorType = adOpenKeyset
rsCustomers.CursorLocation = adUseClient
rsCustomers.LockType = adLockOptimistic
rsCustomers.Open




' At this point everything is fine. The user enters the data into the text boxes
'and presses the save button, which calls the following code...

The error occurs on the line after .AddNew
The Error number is -2147217887
The Error Message "Errors Occured"

When I trapped the error description in a message box, I found out the the
Err.Source is the Microsoft Cursor Engine.


private Sub SaveData()
With rsCustomers
.AddNew
!FName = txtFName 'it fails on this line
!LName = txtLName
!MI = txtMI
!Address = txtAddress
!City = txtCity
!State = txtState
!Zip = txtZip
!Phone = txtPhone
.Update
End With
End Sub




This is driving me crazy! I hope someone can help

Carl

[Roger C]

Hi,

This is sort of a long shot guess, but don't you need to assign the text property like this...?

!FName = txtFName.text

rather than

!FName = txtFName

Or are txtLName, txtFName, etc... variables?

Roger

[Carl Provost]

Hi Roger - thanks for taking the time to answer.

txtFName and txtFName.Text yield the same result, because the .Text property is the default property of a Textbox. From a "readability" point of view txtFName.Text is the proper way to state it... any other ideas why I'm getting this error message?

Carl

[Roger C]

Hey, now you just taught me something. Not really hard to do as I am still somewhat new to VB.

As for your problem, it seems to be a cursor problem so I wonder if you tried using a different setting for cursor type... like changing

rsCustomers.CursorLocation = adUseClient

to the default value

rsCustomers.CursorLocation = adUseServer

In the online help it says that adUseClient uses the local cursor engine rather than the driver suplied cursor... Since your error refers to the "cursor engine" you may be lacking something in that area. Trying the default value may prove that out... I'd say it's worth a try.

If it's not that, I don't know. I use a slightly different syntax than you do... I pass the table name and connection ad parameters to rs.open all on one line. I also use the constant 'adCmdTable' instead of passing a query string to get the records. I really don't think these things have anything to do with the problem. I think you may need to reference or load a cursor engine.... or perhaps change I suggested above will help.

Please let me know how you make out.

Good luck!
Roger

[Carl Provost]

I have tried all of the Cursors and LockTypes. I went to Borders (bookstore) and found a very good book on MCSD exams. I read the section on programming ADO and I am doing the .AddNew and then the !<field name> = <value> the same exact way that they describe.

On my way back home from Borders I was trying to figure out why I am getting a Cursor Engine Error? It is either a bad driver or a memory problem. I came home... re-booted the computer, and now it is working fine! This has been a REAL Enigma!!!

I would like to know what caused this to happen.

Thanks for you interest.
Good luck to you,
Carl