Error trapping on RunSQL

  • Thread starter Thread starter JumboShrimps
  • Start date Start date
J

JumboShrimps

Trying to get a list of files not able to be
appended into my tblBdata. These linked .csv
files come from the field and while they <should>
be in the specific format, they are not always.
If there is something wrong with the file, error msg
at the DOCMD.runSQL statement is something like
"INSERT Statement contains the following...."
This is ok, but I would
A) Have the code continue (On error resume next)
to the next tdf,
and just not import that paticular file.
B) capture the name of the offending file from
StrTblName and save it(append StrTblName to
a "tblBadFile" table so I know which files
did not get imported.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()

For Each tdf In db.TableDefs
StrTblName = tdf.Name
tblAtt = tdf.Attributes

strSQL = "INSERT INTO tblBData SELECT " & [StrTblName]
& ".* FROM " & [StrTblName] & ";"
DoCmd.RunSQL (strSQL)
Next
 
If you want error trapping, don't use RunSQL.

Instead, use the Execute method of the Database object.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()

For Each tdf In db.TableDefs
StrTblName = tdf.Name
tblAtt = tdf.Attributes

strSQL = "INSERT INTO tblBData SELECT " & [StrTblName]
& ".* FROM " & [StrTblName] & ";"
db.Execute strSQL, dbFailOnError
Next
 
.... and if you want to know the offending records run INSERTs line by line
like this:
Sub AppendData()
Dim db As Database, tdf As TableDef
Dim rs As Recordset
Dim StrTblName As String
On Error GoTo ErrH:

Set db = CurrentDb()
For Each tdf In db.TableDefs
StrTblName = tdf.Name
tblAtt = tdf.Attributes
strSQL = "SELECT * FROM " & [StrTblName]
Set rs = db.OpenRecordset(strSQL)
While Not rs.EOF
db.Execute "INSERT INTO tblBData VALUES " & GetValues(rs)
rs.MoveNext
Wend
Next
Exit Sub

ErrH:
' suppose rs(0) contains record id
db.Execute "INSERT INTO tblErrors(RecID,TblName,ErrDescription) values(" & _
rs(0) & ",'" & StrTblName & "','" & Err.Number & " " & Err.Description &
"')"
Resume Next
End Sub


Function GetValues(rs As Recordset)
Dim ix As Integer
Dim tmp As Variant
GetValues = "("
For ix = 0 To rs.Fields.Count - 1
If IsNull(rs(ix)) Then
tmp = Null
Else
tmp = "'" & rs(ix) & "'"
End If
GetValues = GetValues & tmp & ","
Next
Mid(GetValues, Len(GetValues), 1) = ")"
End Function

Douglas J. Steele said:
If you want error trapping, don't use RunSQL.

Instead, use the Execute method of the Database object.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()

For Each tdf In db.TableDefs
StrTblName = tdf.Name
tblAtt = tdf.Attributes

strSQL = "INSERT INTO tblBData SELECT " & [StrTblName]
& ".* FROM " & [StrTblName] & ";"
db.Execute strSQL, dbFailOnError
Next



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



JumboShrimps said:
Trying to get a list of files not able to be
appended into my tblBdata. These linked .csv
files come from the field and while they <should>
be in the specific format, they are not always.
If there is something wrong with the file, error msg
at the DOCMD.runSQL statement is something like
"INSERT Statement contains the following...."
This is ok, but I would
A) Have the code continue (On error resume next)
to the next tdf,
and just not import that paticular file.
B) capture the name of the offending file from
StrTblName and save it(append StrTblName to
a "tblBadFile" table so I know which files
did not get imported.

Dim db As Database, tdf As TableDef
Set db = CurrentDb()

For Each tdf In db.TableDefs
StrTblName = tdf.Name
tblAtt = tdf.Attributes

strSQL = "INSERT INTO tblBData SELECT " & [StrTblName]
& ".* FROM " & [StrTblName] & ";"
DoCmd.RunSQL (strSQL)
Next
Click to expand...
Click to expand...
 
Back
Top