SQL error handling

[wjburke2]

Does anyone know where I can find a list of SQL error codes for Access? I am trying to set up error checking routines for a batch process that will load records onto a database. I also have a process that will export records from Access to a text file. I have been on the WEB all morning trying to find a site or even a book that would give me all the error codes SQL can return with on luck.

 

[MSAccessRookie]

Does anyone know where I can find a list of SQL error codes for Access? I am trying to set up error checking routines for a batch process that will load records onto a database. I also have a process that will export records from Access to a text file. I have been on the WEB all morning trying to find a site or even a book that would give me all the error codes SQL can return with on luck.

I do not know where to find one, but the article below tells you how you can make one, if this is what you want.

http://support.microsoft.com/kb/268721

 

[gemma-the-husky]

i have this to create an error table

Function AccessAndJetErrorsTable() As Boolean
    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim rst As Recordset, lngCode As Long
    Dim strAccessErr As String
    Const conAppObjectError = "Application-defined or object-defined error"

    On Error GoTo Error_AccessAndJetErrorsTable 'handled
    ' Create Errors table with ErrorNumber and ErrorDescription fields.
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
    Set fld = tdf.CreateField("ErrorCode", dbLong)

tdf.Fields.Append fld
    Set fld = tdf.CreateField("ErrorString", dbMemo)
    tdf.Fields.Append fld

    dbs.TableDefs.Append tdf
    ' Open recordset on Errors table.
    Set rst = dbs.OpenRecordset("AccessAndJetErrors")
    ' Loop through error codes.
    For lngCode = 0 To 3500
        On Error Resume Next    'handled
        ' Raise each error.
        strAccessErr = AccessError(lngCode)
        DoCmd.Hourglass True
        ' Skip error numbers without associated strings.
        If strAccessErr <> "" Then

' Skip codes that generate application or object-defined errors.
            If strAccessErr <> conAppObjectError Then
                ' Add each error code and string to Errors table.
                rst.AddNew
                rst!ErrorCode = lngCode
                ' Append string to memo field.
                rst!ErrorString.AppendChunk strAccessErr
                rst.Update
            End If
        End If
    Next lngCode
    ' Close recordset.
    rst.Close
    DoCmd.Hourglass False
    RefreshDatabaseWindow
    MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
    Exit Function

Error_AccessAndJetErrorsTable:
    Call adsStdErrMessage(Title:="Error Table")
    AccessAndJetErrorsTable = False
    Resume Exit_AccessAndJetErrorsTable
End Function

 

[raskew]

Hi Gemma -

I was in the process of creating something similar when I saw your post.

Copied it directly, but when called:

Error_AccessAndJetErrorsTable:
Call adsStdErrMessage(Title:="Error Table")

Compile error. Sub or Function not defined.

When commented this line out, it took me to the next line and returned "False" with no table being created.

Any thoughts?

Bob

 

[MSAccessRookie]

Hi Gemma -

I was in the process of creating something similar when I saw your post.

Copied it directly, but when called:

Error_AccessAndJetErrorsTable:
Call adsStdErrMessage(Title:="Error Table")

Compile error. Sub or Function not defined.

When commented this line out, it took me to the next line and returned "False" with no table being created.

Any thoughts?

Bob

I tried what you did (Cut/Paste - Comment out the resulting Error), and a table was created that looks right to me.

NOTE: I also tried the example from the MicroSoft Help and Support that I described above, and it works without any errors or confusion. You just need to create an empty table first, because it does not create one for you.

 

[gemma-the-husky]

sorry thats just a sub i have to popup a msgbox for a access error

sub adsstderrormessage(title as string)
msgbox("Error: " & err & " Desc: " & err.description,,title)
end sub

i actually have some other parameters in this sub to change the displayed message etc, but this one just needs the title.

 

[raskew]

Thanks to you both.

I ended up reconstructing it, first just with table creation, then when that was working fine moved to populating the table. Left out the error handling and works just great.

Bob

 

[gemma-the-husky]

useful to see them all isnt it - i hate just seeing a message and no code number - the code is strangely comforting!