how to delete import errors tables from Access?

[deadeye123]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: postcell"] I am not a VBA expert. what I am trying to do is to import multiple csv files into access. access is creating bunch of import error tables after i import the files. i am using the code below to delete the error tables after i run a macro to import the csv files but it's not working. it's telling me user defined type not defined. any help is appreciated. I really appreciate if you can help me fix that code or find a different one
Sub dropImportError()
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim tbl_name As DAO.TableDef, str As String
With CurrentDb
For Each tbl_name In .TableDefs
str = tbl_name.Name
If InStr(str, "ImportErrors") <> 0 Then
str = "DROP TABLE" & str & ""
DoCmd.RunSQL str
End If
Next
End With</code>End Sub


[/TD]

</tbody>

</body>

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: postcell"] I am not a VBA expert. what I am trying to do is to import multiple csv files into access. access is creating bunch of import error tables after i import the files. i am using the code below to delete the error tables after i run a macro to import the csv files but it's not working. it's telling me user defined type not defined. any help is appreciated. I really appreciate if you can help me fix that code or find a different one
Sub dropImportError()
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim tbl_name As DAO.TableDef, str As String
With CurrentDb
For Each tbl_name In .TableDefs
str = tbl_name.Name
If InStr(str, "ImportErrors") <> 0 Then
str = "DROP TABLE" & str & ""
DoCmd.RunSQL str
End If
Next
End With</code>End Sub


[/TD]

</tbody>

 

[xenou]

Your string has no spaces between the words.
When you build a raw SQL command string use string concatenation you MUST look at the string to make sure it is correct. If you did this would see instantly the problem:

DROP TABLEImportErrors

you want instead:

DROP TABLE ImportErrors

To test your code without running it and viewing the commands you create use Debug.Print (or a msgbox):

Sub dropImportError()
Dim tbl_name As DAO.TableDef, str AsString
With CurrentDb
  ForEach tbl_name In.TableDefs
    str = tbl_name.Name
    If InStr(str,"ImportErrors")<>0Then
      str ="DROP TABLE"& str &""
      Debug.Print str
      '//DoCmd.RunSQL str
    EndIf
  Next
EndWith
End Sub