Append query fails but no error produced

  • Thread starter Thread starter Drew
  • Start date Start date
D

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
 
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
|
 
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
 
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).
 
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).
Click to expand...

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.

**********************************
 
Back
Top