bfuchs
asked on
how to determine if record was inserted into SQL table
Hi Experts,
I have the following code inserting a record and checking if record was successfully added, however when record already exsists and it would violate a unique index, it giving me an error.
see attached.
untitled.bmp
I have the following code inserting a record and checking if record was successfully added, however when record already exsists and it would violate a unique index, it giving me an error.
see attached.
strSql = "Insert into Employeestbl(column1,column2..)values(1,2..) ; Select @@Identity"
set rs = CurrentProject.Connection.Execute(strSql).NextRecordset
Debug.Print "New Record ID: ", rs(0)
what is the correct way to handle this?untitled.bmp
ASKER
Hi,
Is the below correct?
Is the below correct?
on error resume next
If UBound(v) > 1 Then
strSql = "Insert into EmpTovWeeklyHours" _
& "(SocialSecurity,Day,Hours)" _
& " Values ('" & Replace(v(0), Chr(32), "") & "','" & Mid(v(3), 3, 2) & "/" & Mid(v(3), 5, 2) & "/" & Mid(v(3), 1, 2) & "'," & Replace(Replace(v(4), Chr(32), ""), "'", "") & ")"
strSql = strSql & " ; Select @@Identity"
CurrentProject.Connection.Execute strSql, i
'Set rs2 = CurrentProject.Connection.Execute(strSql).NextRecordset
'If rs2(0) = 0 Then
If err.Number = -2147217873 Then
strSql = "Update EmpTovWeeklyHours Set Hours = " & v(4)
strSql = strSql & " Where SocialSecurity = '" & v(0) & "'"
strSql = strSql & " And Day = '" & Mid(v(3), 3, 2) & "/" & Mid(v(3), 5, 2) & "/" & Mid(v(3), 1, 2) & "'"
strSql = strSql & " And Hours <> " & v(4)
CurrentProject.Connection.Execute strSql, i
End If
End If
something like that
There are actually two sets of errors you might need to check
* Err
* Errors(#) -- a collection of errors. You can access these in a For Each or a traditional For loop
There are actually two sets of errors you might need to check
* Err
* Errors(#) -- a collection of errors. You can access these in a For Each or a traditional For loop
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One way:
Here I use Table1(Field1)
You may include more fields in the insert part of the code.
To insert new records with no duplicates:
You may expand to alert of the existence of the record:
Here I use Table1(Field1)
You may include more fields in the insert part of the code.
To insert new records with no duplicates:
If IsNull(DLookup("Field1", "Table1", "Field1='" & "EN" & "'")) Then CurrentDb.Execute "Insert into Table1(Field1) VAlues('EN') "
You may expand to alert of the existence of the record:
If IsNull(DLookup("Field1", "Table1", "Field1='" & "EN" & "'")) Then
CurrentDb.Execute "Insert into Table1(Field1) VAlues('EN') "
Else
MsgBox("Duplicate record, check data")
End If
ASKER
Hi Experts,
This will be running in a loop for thousands of records therefore I am looking for the most efficient way to process that, I don't think dlookup is appropriate for this, not sure if opening new recordset is the right approach either, I think the best in this case would be to open one recordset at the beginning and for each record being inserted do rs.findfirst and if rs.nomatch...something like that.
What are your thoughts?
This will be running in a loop for thousands of records therefore I am looking for the most efficient way to process that, I don't think dlookup is appropriate for this, not sure if opening new recordset is the right approach either, I think the best in this case would be to open one recordset at the beginning and for each record being inserted do rs.findfirst and if rs.nomatch...something like that.
What are your thoughts?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@hnasr,
You meant to say that the same recordset used to verify if the record already exists could be used for inserting in case it does not exists and for updating in case it does? that's cool, I didn't thought of that..
I'm sure that's more efficient than execute insert/update statements thousand times.
You meant to say that the same recordset used to verify if the record already exists could be used for inserting in case it does not exists and for updating in case it does? that's cool, I didn't thought of that..
I'm sure that's more efficient than execute insert/update statements thousand times.
@bfuchs
I don't claim that because I was referring to reusing the recordset for new insert. You raised another good point to use the recordset for updating existing record. Thanks for the contribution.
I don't claim that because I was referring to reusing the recordset for new insert. You raised another good point to use the recordset for updating existing record. Thanks for the contribution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok Experts, I think I will have plenty of work by now with all those suggestions, at least I got some ideas where to start..
Thank you very Much!
Thank you very Much!
Welcome!
You will need an On Error statement