Violation of Primary Key constraints

[bakershawnm]

I have been searching for an answer to this. I found this thread thread705-867475 but it did not provide the answer I needed.

I have a similar problem except that I am only adding a new record to 1 table using the following code:

On Error GoTo Err_Save_rcrd_Click

Dim spccnxn As New ADODB.Connection

Dim spc1 As New ADODB.Recordset
Dim spc2 As New ADODB.Recordset

Dim fltr, mysql, DtEntrd As String

Dim comapos, prevpos, Desg, numdfcts As Integer

spccnxn.ConnectionString = "Provider='MSDASQL.1';Persist Security Info='False';" & _
"Trusted_Connection=True;Data Source='CrystalSQLKMM';Initial Catalog='KMM'"
spccnxn.Open
spc2.Open "KMM_SPC_2", spccnxn, adStatic, adLockOptimistic, adCmdTable
fltr = "DefectDescription = '" & Me!Dfct & "'"
spc2.Filter = fltr

comapos = 1
prevpos = 1
comacnt = 0
comapos = InStr(prevpos, Me!Desg, ",")
Do While prevpos > 0
prevpos = InStr(prevpos + 1, Me!Desg, ",")
comacnt = comacnt + 1
Loop
prevpos = 1
If comapos > 0 Then
For x = 1 To comacnt
If comapos = 0 Then
Desg = Trim(Mid(Me!Desg, prevpos, Len(Me!Desg) - prevpos + 1))
Else
Desg = Trim(Mid(Me!Desg, prevpos, comapos - prevpos))
End If
prevpos = comapos + 1
DtEntrd = Format(Me!DtEntrd, "Short Date")
numdfcts = Me!DfctQty / comacnt
refdsg = Me!Ref & Desg
mysql = "INSERT INTO KMM_SPC_1 ( DateEntered, InspectedBy, MONumber, " & _
"SerialNumber, DefectType, RefDesignator, DefectQty, Comment, Source, AOI, " & _
"Wave, SelectiveHand ) " & _
"SELECT '" & DtEntrd & "', '" & Me!Inspctr & "', " & _
"'" & Me!JobNum & "', '" & Me!SerialNo & "', " & spc2!DefectType & ", " & _
"'" & refdsg & "', " & numdfcts & ", 'FI', '" & Me!Comments & "' "
If IsNull(Me!AOI) Or Me!AOI = False Then
mysql = mysql & ", 0"
Else
mysql = mysql & ", 1"
End If
If IsNull(Me!Wave) Or Me!Wave = False Then
mysql = mysql & ", 0"
Else
mysql = mysql & ", 1"
End If
If IsNull(Me!S_H) Or Me!S_H = False Then
mysql = mysql & ", 0"
Else
mysql = mysql & ", 1"
End If

spccnxn.Execute mysql

comapos = InStr(prevpos, Me!Desg, ",")
Next
Else
DtEntrd = Format(Me!DtEntrd, "Short Date")
refdsg = Me!Ref & Me!Desg
mysql = "INSERT INTO KMM_SPC_1 ( DateEntered, InspectedBy, MONumber, " & _
"SerialNumber, DefectType, RefDesignator, DefectQty, Comment, Source, AOI, " & _
"Wave, SelectiveHand ) " & _
"SELECT '" & DtEntrd & "', '" & Me!Inspctr & "', '" & Me!JobNum & "', " & _
"'" & Me!SerialNo & "', " & spc2!DefectType & ", '" & refdsg & "', " & _
"" & Me!DfctQty & ", 'FI', '" & Me!Comments & "' "
If IsNull(Me!AOI) Or Me!AOI = False Then
mysql = mysql & ", 0"
Else
mysql = mysql & ", 1"
End If
If IsNull(Me!Wave) Or Me!Wave = False Then
mysql = mysql & ", 0"
Else
mysql = mysql & ", 1"
End If
If IsNull(Me!S_H) Or Me!S_H = False Then
mysql = mysql & ", 0;"
Else
mysql = mysql & ", 1;"
End If

spccnxn.Execute mysql
End If

spc2.Close
Set spc2 = Nothing
spc1.Close
Set spc1 = Nothing
spccnxn.Close
Set spccnxn = Nothing
MsgBox "Record(s) Saved"

Exit_Save_rcrd_Click:
Exit Sub

Err_Save_rcrd_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Save_rcrd_Click

I get the violation of primary key constraint error. The key field is an auto number field. There are no relationships to this table. I can add a new record manually with the linked table open in access.

The wierd thing is that it was working fine yesterday but today it is not working. Nothing has changed in the SQL DB since yesterday.

I have been banging my head on this all day and am frustrated. Any help will be appreciated.

 

[smedvid]

Just off the top of my head...
I had some goofy stuff when the DB was corrupt with Auto fields. You may want to backup and then repair/compact. Next, I would take off the Primary Index constraint and see what actually gets loaded... perhaps there is another cause that you are not seeing? htwh,

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[bakershawnm]

Thanks for the suggestion.

You are not going to believe what the fix was. It was totally stupid on my part.

The following line:

spccnxn.ConnectionString = "Provider='MSDASQL.1';Persist Security Info='False';" & _
"Trusted_Connection=True;Data Source='CrystalSQLKMM';Initial Catalog='KMM'"

should have been:

spccnxn.ConnectionString = "Provider='MSDASQL.1';Persist Security Info='False';" & _
"Trusted_Connection=True;Data Source='SPCData';Initial Catalog='SPCData'"

We recently consolidated my scattered dbs into one SQL db and I missed updating this form.

So now i have to go and check for any data that was going into the wrong table.