Primary Key Violation Error Message

[Guest]

Hi,

I have an "insert into query" sql query that runs well (code below). What I
want is to ensure that you cannot be run it twice. The primary key for the
table is a combination key of Year (Year) and CompanyID (CoID). The Insert
Into query inserts the appropriate year (which is used in the primary key).

Hence when it attempts to run it again, an error message appears saying
"Didn't add records due to key violations, Do you want to run this action
query anyway? To ignore the errors and run the query click Yes. (with yes
no & help buttons)".

I want to handle this error/situation and not get this Access created
message and somehow have my own custom message.

Any ideas how to go about this? Thanks for any assistance.

****code starts here****
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String


' Turn off screen updating
DoCmd.Echo False

strSQL = "INSERT INTO tblAnalysis (Year, CoID) " & _
"SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
tblCompany.CoID " & _
"FROM tblCompany " & _
"WHERE
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate]) AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
"OR
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND
((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"


' Apply the SQL statement to the stored query
Set db = CurrentDb
Set qdf = db.QueryDefs("qry37AdminAnalysisSetUpStoredQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

'Open the Query
DoCmd.OpenQuery "qry37AdminAnalysisSetUpStoredQuery"

****code ends here****

 

[Allen Browne]

Instead of OpenQuery, use the Execute method inside a transaction. You can
pop up your on message to get the user's response, and RollBack the entire
operation if they don't want to proceed.

If you have never used transactions, there are several pitfalls, so see the
example in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
The example shows how to execute a pair of action queries in tandem for an
all-or-nothing result, but the same principles apply to your single query.

 

[Guest]

Hi Allen,

Thanks for the information and the code on your website. I have applied my
SQL and touches to your code and unfortunately I am getting an error "Too Few
parameters. Expected 3."


I have done some searches and found this information
http://support.microsoft.com/default.aspx/kb/210244#XSLTH3154121123120121120120
But am not sure if this applies to my case.

Any ideas where the problem is?

My current code is:

Sub cmdExecuteSetUp_Click()

On Error GoTo ErrorPoint
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

'Step 2: Execute the append.
strSql = "INSERT INTO tblErrAnalysis (Year, CoID) " & _
"SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
tblCompany.CoID " & _
"FROM tblCompany " & _
"WHERE
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate]) AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
"OR
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND
((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"
db.Execute strSql, dbFailOnError

'Step 3: Get user confirmation to commit the change.
strMsg = "Execute the Set Up of" & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

ExitPoint:
'Step 4: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub

ErrorPoint:
MsgBox Err.Description, vbExclamation, "Execution of Set Up failed: Error
" & Err.Number
Resume ExitPoint
End Sub


--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

Instead of OpenQuery, use the Execute method inside a transaction. You can
pop up your on message to get the user's response, and RollBack the entire
operation if they don't want to proceed.

If you have never used transactions, there are several pitfalls, so see the
example in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
The example shows how to execute a pair of action queries in tandem for an
all-or-nothing result, but the same principles apply to your single query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an "insert into query" sql query that runs well (code below). What
I
want is to ensure that you cannot be run it twice. The primary key for
the
table is a combination key of Year (Year) and CompanyID (CoID). The
Insert
Into query inserts the appropriate year (which is used in the primary
key).

Hence when it attempts to run it again, an error message appears saying
"Didn't add records due to key violations, Do you want to run this action
query anyway? To ignore the errors and run the query click Yes. (with
yes
no & help buttons)".

I want to handle this error/situation and not get this Access created
message and somehow have my own custom message.

Any ideas how to go about this? Thanks for any assistance.

****code starts here****
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String


' Turn off screen updating
DoCmd.Echo False

strSQL = "INSERT INTO tblAnalysis (Year, CoID) " & _
"SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
tblCompany.CoID " & _
"FROM tblCompany " & _
"WHERE
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
"OR
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND
((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"


' Apply the SQL statement to the stored query
Set db = CurrentDb
Set qdf = db.QueryDefs("qry37AdminAnalysisSetUpStoredQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

'Open the Query
DoCmd.OpenQuery "qry37AdminAnalysisSetUpStoredQuery"

****code ends here****

 

[Allen Browne]

Dylan, I should have read your query more carefully. The Execute method
cannot call the Expression Service which Access uses to resolve the
references to the controls on the form.

Concatenate the values from the form into the string. Numbers don't need any
delimiter, but you do need to check that a value is present. Text type
fields need quotes as delimiters. Dates need # as the delimiter, and if your
database could be used in a country that uses non-US dates, you need to
force the string to use the US date order and slash separators.

It will end up something like this:

Const conJetDate = "\#mm\/dd\/yyyy\#"

With [Forms]![frm23AdminAnalysisSetUp]
strSql = "INSERT INTO tblErrAnalysis (Year, CoID) SELECT " & _
![cboPeriodYears] & ", tblCompany.CoID FROM tblCompany " & _
"WHERE ((tblCompany.LicenceGranted <= " & _
Format(![txtPeriodEndDate], conJetDate) & _
") AND (tblCompany.LicenceSurrendered Is Null)) " & _
"OR ((tblCompany.LicenceGranted <= " & _
Format(![txtPeriodEndDate], conJetDate) & _
" AND ...
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for the information and the code on your website. I have applied my
SQL and touches to your code and unfortunately I am getting an error "Too
Few
parameters. Expected 3."


I have done some searches and found this information
http://support.microsoft.com/default.aspx/kb/210244#XSLTH3154121123120121120120
But am not sure if this applies to my case.

Any ideas where the problem is?

My current code is:

Sub cmdExecuteSetUp_Click()

On Error GoTo ErrorPoint
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

'Step 2: Execute the append.
strSql = "INSERT INTO tblErrAnalysis (Year, CoID) " & _
"SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
tblCompany.CoID " & _
"FROM tblCompany " & _
"WHERE
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
"OR
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND
((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"
db.Execute strSql, dbFailOnError

'Step 3: Get user confirmation to commit the change.
strMsg = "Execute the Set Up of" & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

ExitPoint:
'Step 4: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub

ErrorPoint:
MsgBox Err.Description, vbExclamation, "Execution of Set Up failed: Error
" & Err.Number
Resume ExitPoint
End Sub


--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

Instead of OpenQuery, use the Execute method inside a transaction. You
can
pop up your on message to get the user's response, and RollBack the
entire
operation if they don't want to proceed.

If you have never used transactions, there are several pitfalls, so see
the
example in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
The example shows how to execute a pair of action queries in tandem for
an
all-or-nothing result, but the same principles apply to your single
query.

I have an "insert into query" sql query that runs well (code below).
What
I
want is to ensure that you cannot be run it twice. The primary key for
the
table is a combination key of Year (Year) and CompanyID (CoID). The
Insert
Into query inserts the appropriate year (which is used in the primary
key).

Hence when it attempts to run it again, an error message appears saying
"Didn't add records due to key violations, Do you want to run this
action
query anyway? To ignore the errors and run the query click Yes. (with
yes
no & help buttons)".

I want to handle this error/situation and not get this Access created
message and somehow have my own custom message.

Any ideas how to go about this? Thanks for any assistance.

****code starts here****
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String


' Turn off screen updating
DoCmd.Echo False

strSQL = "INSERT INTO tblAnalysis (Year, CoID) " & _
"SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
tblCompany.CoID " & _
"FROM tblCompany " & _
"WHERE
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
"OR
(((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
AND
((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"


' Apply the SQL statement to the stored query
Set db = CurrentDb
Set qdf = db.QueryDefs("qry37AdminAnalysisSetUpStoredQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

'Open the Query
DoCmd.OpenQuery "qry37AdminAnalysisSetUpStoredQuery"

****code ends here****