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.

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

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

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

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.

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.

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

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