Append query fails but no error produced

[Drew]

Hi All,

I have an append query running from code (see SQL statement at
bottom). The query has two calculated expressions; one is from a
variable. The table field that is being assigned from a variable is
indexed (no duplicates).

The append query fails when the indexed field is a duplicate (good)
but no error message comes up (bad). Ultimately, I want to be able to
trap the duplicate error and present a custom msgbox. Is there any
reason it would not give the error when I run it from VBA (the same
query made in design view gives an error)?

Code:
'Run the Append Query to add the record to the main table and add the
file name and today's date
Set db = CurrentDb()
db.Execute "INSERT INTO survey_main ( Field1, Field2, Field3,
Field4, Field5, ImportDate, TextFile )" & _
" SELECT TempTable.field1, TempTable.field2, TempTable.field3,
TempTable.field4," & _
"TempTable.field5, Int(Now()) AS Expr1, '" & strFile & "' AS Expr2
FROM TempTable;"
Set db = Nothing

Thanks,
Drew

 

[Eric Butts [MSFT]]

Hi Drew,

This is by design. The only way you'll be able to accomplish that in VBA
code is to use something like the following:


On Error GoTo MyError

Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Set rs1 = Currentdb.OpenRecordset("survey_main")
Set rs2 = Currentdb.OpenRecordset(" SELECT TempTable.field1,
TempTable.field2, TempTable.field3, TempTable.field4, TempTable.field5,
Int(Now()) AS Expr1, 'something' AS Expr2 FROM TempTable;")

Do Until rs2.EOF
rs1.AddNew
rs1! Field1 = rs2!field1
rs1!Field2 = rs2!field2
rs1!Field3 = rs2!field3
rs1!Field4 = rs2!field4
rs1!Field5 = rs2!field5
rs1!ImportDate = rs2!Expr1
rs1!TextFile = rs2!Expr2
rs1.Update
rs2.movenext
Loop

Exit Function:
MyError:
IF Err.Number = 3022 THEN
Msgbox "My Custom Error message"
Resume Next
END IF


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights



--------------------
| From: (e-mail address removed) (Drew)
| Newsgroups: microsoft.public.access.queries
| Subject: Append query fails but no error produced
| Date: 19 Jul 2004 11:41:22 -0700
| Organization: http://groups.google.com
| Lines: 27
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 168.236.254.1
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1090262482 9316 127.0.0.1 (19 Jul 2004
18:41:22 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Mon, 19 Jul 2004 18:41:22 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!postnews2.google.com!not-fo
r-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:207318
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi All,
|
| I have an append query running from code (see SQL statement at
| bottom). The query has two calculated expressions; one is from a
| variable. The table field that is being assigned from a variable is
| indexed (no duplicates).
|
| The append query fails when the indexed field is a duplicate (good)
| but no error message comes up (bad). Ultimately, I want to be able to
| trap the duplicate error and present a custom msgbox. Is there any
| reason it would not give the error when I run it from VBA (the same
| query made in design view gives an error)?
|
| Code:
| 'Run the Append Query to add the record to the main table and add the
| file name and today's date
| Set db = CurrentDb()
| db.Execute "INSERT INTO survey_main ( Field1, Field2, Field3,
| Field4, Field5, ImportDate, TextFile )" & _
| " SELECT TempTable.field1, TempTable.field2, TempTable.field3,
| TempTable.field4," & _
| "TempTable.field5, Int(Now()) AS Expr1, '" & strFile & "' AS Expr2
| FROM TempTable;"
| Set db = Nothing
|
| Thanks,
| Drew
|

 

[Dale Fye]

Drew,

A couple of things you might try:

db.Execute strSQL, dbFailOnError

I cannot remember whether this kicks off an error that can be trapped or not

docmd.runsql strsql

HTH
Dale

 

[Van T. Dinh]

You need to use the dbFailOnError on the Execute statement to get the error.
Otherwise the Execute can fail silently (without giving any error / error
message).

 

[Drew]

You need to use the dbFailOnError on the Execute statement to get the error.

Otherwise the Execute can fail silently (without giving any error / error
message).

Thanks everyone - it works!

*********************************
Final solution for future searchers:

I added , dbFailOnError to the end of the SQL statement, allowing the
error statement to be produced. I used code (i.e. see Eric's message
above) in a separate sub to trap the error and create my own message
box. Cheers, Drew.

**********************************