I am using the following code to insert a single record into a table:
Dim cnn As New ADODB.Connection
Dim rs3 As New ADODB.Recordset
Dim sQRY As String
Set rs3 = New ADODB.Recordset
sQRY = "INSERT INTO tblWRIDLeedsCodes " & _
"(WorkRequestID, LeedsCode) " & _
"VALUES (" & rs1("WorkRequestID") & ", '" & rs2("CodeOut") & "');"
If rs3.State = 1 Then rs3.Close
rs3.Open sQRY, cnn, adOpenStatic, adLockOptimistic
Set rs3 = cnn.Execute(sQRY)
This works when executed, and a record is inserted into the appropriate table. However, I then get an error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
WorkRequestID is a primary key in tblWRIDLeedsCodes. The table is empty prior to my inserting the new record. I have checked that rs1 and rs2 have returned values in the Locals Window. Anyone with any ideas?
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!