Import File

[Kirk P.]

I've got the following code that automates an import process:

Function Importcsv()
On Error GoTo Importcsv_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelExpense", acViewNormal, acEdit
DoCmd.TransferText acImportDelim, "OPT_DT_GAAP_DETAIL_A Import
Specification", _
"tblExpense", "\\oprdgv1\depart\Finance\_Function
Standardization\" & _
"2006 Product & Function Alignment\OPT_DT_GAAP_DETAIL_A.csv",
True, ""
DoCmd.OpenQuery "qupdGLString", acViewNormal, acEdit
DoCmd.OpenQuery "qdelJETemplate", acViewNormal, acEdit
DoCmd.OpenQuery "qappJETemplate", acViewNormal, acEdit
DoCmd.OpenQuery "qupdNewGLString", acViewNormal, acEdit
DoCmd.OpenQuery "qupdCurrent_Correct", acViewNormal, acEdit
DoCmd.SetWarnings True
MsgBox "Import Successful!", vbInformation, "Import Status"

Importcsv_Exit:
Exit Function

Importcsv_Err:
MsgBox Error$
Resume Importcsv_Exit

End Function

The trouble is with the append query "qappJETemplate". I know I've turned
the warnings off, but this append query is generating a primary key error
that I WANT to be notified of - it currently is NOT giving me any
notification, so it appends zero records then displays the MsgBox "import
successful". I was under the impression that even with Set Warnings = False
that I would still be notified of primary key errors. What's the deal?

 

[Douglas J. Steele]

Best would be to use the Execute method of the QueryDef object, with an
option of dbFailOnError.

CurrentDb().QueryDefs("qappJETemplate").Execute dbFailOnError

That will raise a trappable error: change Importcsv_Err to react
appropriately to that specific error.