VB Run-time error 3251 - Can someone advise?

[ChemistZ]

I have a VB app connecting to a SQL Server 7.0 database. On a form that consists of one table's data, I added a button with the AddNew method attached. When I click it I get the above error message. The words following it are "Object or provider is not capable of performing requested operation". What am I doing wrong?

 

[flyrodder]

Be sure you have the 'permissions' required to update or add new to the table in question.

Be sure you have opened the record set with adLockOptimistic and not adlockreadonly.

 

[ChemistZ]

I have used the data environment to create a connection and command. Where is the right place for the adlockoptimistic?

 

[flyrodder]

I do not use a data control I use a connection string to open the recordset
recordsetRec.Open sqlstatement, connectionstring,adOpenDynamic, adLockOptimistic


a quick example:
in a public module I define the connection string

Public Const SQLConnect = "Provider = SqlOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;InitialCatalog=databasename;DataSource=servernam"
Public SQLConnection As ADODB.Connection

Your connection string may be different - see your database administer for the correct connection string.
I then establish the connection on a startup form in the program along with the needed recordset

Set SQLConnection =NewADODB.Connection
SQLConnection.Open SQLConnect

Set recordsetname = New ADODB.Recordset

I then open the record set with a sql statment.

In this case I get the entire table so I can maintain it. All records have been returned. I can now do what I want with it.

dim sSql as string
sSQL = "Select * from tablename"
recordsetname.Open sSQL, SQLConnect, adOpenDynamic, adLockOptimistic



I find that I have more flexiblity by not using the data control. Of course it requires more programming on your part.