ADO Recordset AddNew doesn't work. 3

[RaiderBob]

I am using MS Visual Studio to create a form and Load an MS Access DB. I have made all the connections and am able to access the DB. I created an Add button control. My problem is with the AddNew ADO Recordset.
****** Code *******
Private Sub Add_Click()
adcEmployee.Recordset.AddNew
MsgBox "Record Added"
End Sub

When I ran this it would substitute the current record with the info that was entered on the form instead of creating a new record in the MS Access DB Table.

Then I attempted to run the following:
********** Code ******************
Private Sub Add_Click()
adcEmployee.Recordset.AddNew
adcEmployee.Recordset!Emp_L_Name = Emp_L_Name.Text
adcEmployee.Recordset!Emp_F_Name = Emp_F_Name.Text
adcEmployee.Recordset!Emp_MI = Emp_Middle_Init.Text
adcEmployee.Recordset.Update
MsgBox "Record Added"
End Sub

I got the following error when I went to add it.
Run-time error '-2147217842(80040e4e)';
The change was canceled during notification; no columns are changed.

The DB connections are working, I just need help for the AddNew button.

 

[link9]

Have you set your recordset locktype to something other than the default adLockReadOnly ???

If not, then do this before you open your recordset:

rsObject.lockType = 3 (adLockOptimistic)


Paul Prewett

 

[RaiderBob]

Paul,
I added the following statement before the AddNew statement:
adcEmployee.Recordset.LockType = 3

and I got the following error:
Run-time error '3705';
The operation requested by the application is not allowed if the object is open.

 

[woyler]

You have to set the lock type, cursor type before you open the recordset

 

[RaiderBob]

Link9 and Woyler,
I have the Data Link Properties set to "Share Deny None" in the Connection string properties.

I also have my ADO properties LockType set to "3-adLockOptimistic" and I am still getting the same error msg - Run-time error '-2147217842(80040e4e)';
The change was canceled during notification; no columns are changed.

What did I do wrong now?

 

[daimaou]

RaiderBob, How did you exactly opened your recordset for edition?

Show the code, there seems to be something wrong there...

I'd try this one:

Private Sub Add_Click()

Dim adcEmployee as New ADODB.Recordset

adcEmployee.Open "SELECT ...", YourConnectionString, adOpenStatic, adLockOptimistic

adcEmployee.AddNew
adcEmployee!Emp_L_Name = Emp_L_Name.Text
adcEmployee!Emp_F_Name = Emp_F_Name.Text
adcEmployee!Emp_MI = Emp_Middle_Init.Text
adcEmployee.Update
MsgBox "Record Added"

End Sub

Let me know if this works

 

[RaiderBob]

OK, I am not sure of the syntax, but this is what I have right now:

Private Sub Add_Click()
Dim Employee As New ADODB.Recordset
Employee.Open "SELECT * FROM EMPLOYEE;"
"Provider=Microsoft.Jet.OLEDB.4.0,Data Source=C:\TandE.mdb",
adOpenStatic , adLockOptimistic
With Employee.Recordset
.AddNew
!Emp_Last_Name = Me.Emp_Last_Name
!Emp_First_Name = Me.Emp_First_Name
!Emp_Middle_Init = Me.Emp_Middle_Init.Text
!Emp_Soc_Sec_No = Me.Emp_Social_Security_No
.Update
End With
MsgBox "Record Added"
End Sub

 

[daimaou]

hmm that really sounds weird since it seems you are doing it right, anyway I don't know if this would solve it, but instead of referring to Employee.Recordset just do:

With Employee
.AddNew
Employee!Emp_Last_Name = Me.Emp_Last_Name
Employee!Emp_First_Name = Me.Emp_First_Name
Employee!Emp_Middle_Init = Me.Emp_Middle_Init.Text
Employee!Emp_Soc_Sec_No = Me.Emp_Social_Security_No
.Update
End With

If this still don't work try to start a new project and try that code there since I'm really not sure on why you're getting that error msg.

 

[woyler]

RaiderBob,
Try This,:
Dim Employee As ADODB.Recordset
Dim sSQL As String
Set Employee = CreateObject("ADODB.Recordset&quot

Employee.ActiveConnection= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
sSQL = "SELECT * FROM EMPLOYEE"
Employee.Open sSQL, , adOpenStatic, adLockOptimistic, adExecuteNoRecords And adCmdText

With Employee
.AddNew
!Emp_Last_Name = Me.Emp_Last_Name
!Emp_First_Name = Me.Emp_First_Name
!Emp_Middle_Init = Me.Emp_Middle_Init.Text
!Emp_Soc_Sec_No = Me.Emp_Social_Security_No
.Update
End With
MsgBox "Record Added"

 

[RaiderBob]

OK. I am now adding the record(I checked the DB). I am also getting the msgbox msg that I am sending. But when I try to go backward or forward using the ADO box I get the following Msg :
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.

There are no duplicate values on the table. Why is this happening? Here is the code.

Private Sub Add_Click()
Dim Employee As ADODB.Recordset
Dim sSQL As String
Set Employee = CreateObject("ADODB.Recordset&quot

Employee.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TandE.mdb"
sSQL = "SELECT * FROM EMPLOYEE"
Employee.Open sSQL, , adOpenStatic, adLockOptimistic, adExecuteNoRecords And adCmdText

With Employee
.AddNew
!Emp_Last_Name = Me.Emp_Last_Name
!Emp_First_Name = Me.Emp_First_Name
!Emp_Middle_Init = Me.Emp_Middle_Init.Text
!Emp_Soc_Sec_No = Me.Emp_Social_Security_No
.Update
End With
MsgBox "Record Added"
End Sub

 

[woyler]

I would have to think somewhere in your table design, you have set a Primary Key and are trying to write a record that would duplicate data in at least that field.
Emp_Social_Security_No would be the logical choice for the key. If you have a record already in the table,
Emp_Social_Security_No
111-22-3333
And you are trying to write a new record with the same value for Emp_Social_Security_No, the error will generate. The same is true for auto numbers. Double check both.

 

[RaiderBob]

WOYLER,
I was very careful to make the SS# the primary key and not to duplicate it. As I mentioned before, the record is being added to the database. What I think is happening is that the info is being left on the screen and that info is trying to be added again to the database. Thus the error msg. Should I clear out the fields so they are not the same as what was added to the database?

 

[woyler]

If you are using the contents of the fields on the form as the values to be inserted into your table, then yes, I would clear out the values afer you have updated the table

 

[bubarooni]

Did you ever solve this problem RaiderBob? Thanks to the posts in this thread I just did.

 

[Guest_imported]

Please, please don't use the ADO Control to control your recordset. There are a million & one better ways to manage your data. If you need to scroll thru' records then do it on a different form than the one that you want to Add & Delete records from. ADO Controls are notorious for not updating & refreshing recordsets and they also adversly affect the data retrieval speed of your application. You are a programmer - use code

 

[Guest_imported]

Please, please don't use the ADO Control to control your recordset. There are a million & one better ways to manage your data. If you need to scroll thru' records then do it on a different form than the one that you want to Add & Delete records from. ADO Controls are notorious for not updating & refreshing recordsets and they also adversly affect the data retrieval speed of your application. You are a programmer - use code

 

[Guest_imported]

Please, please don't use the ADO Control to control your recordset. There are a million & one better ways to manage your data. If you need to scroll thru' records then do it on a different form than the one that you want to Add & Delete records from. ADO Controls are notorious for not updating & refreshing recordsets and they also adversly affect the data retrieval speed of your application. You are a programmer - use code

 

[CrackerSam]

Yes, don't use ADO Control (i had this problem in the past)

for updating or insertion

use Raw SQL (ie 'UPDATE .....' and 'INSERT INTO ...'

----
an other thing , use the DATAENVIRONMENT for connecting to
your database (it's very useful).