RE: Append query fails but no error produced

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Eric Butts [MSFT] (ebutts_at_online.microsoft.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 22:16:12 GMT

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
ebutts@online.microsoft.com
"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: drew_71_@hotmail.com (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: <6271e49d.0407191041.6a9ad1c5@posting.google.com>
| 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: groups-abuse@google.com
| 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
|

Quantcast