Troubleshoot "Run Time error '3295' Syntax error in DROP table or DROP index

I have added the below code to remove spurious table in my access database created when am import script is executed. Sadly when I try and run my function I get an error stating

"Run Time error '3295' Syntax error in DROP table or DROP index" I don't know why this is happening and am looking for some assiatance in troubleshooting it

Public Function DeleteImportErrorTbls()
'************************************************************************************************
' This function stops the database getting cluttered up by removing any import error tables
'************************************************************************************************
Dim tdf As TableDef
Dim db As Database
Dim tblName As String

Set db = CurrentDb
    For Each tdf In CurrentDb.TableDefs
        If Right(tdf.Name, 12) = "ImportErrors" Or Right(tdf.Name, 13) = "ImportErrors1" Then
            'DoCmd.DeleteObject acTable, tdf.Name
            tblName = tdf.Name
            db.Execute "DROP TABLE " & tblName & ";"
            'DoCmd.DeleteObject acTable, tdf.Name

            End If
    Next tdf
End Function
JayceWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
chnage this

db.Execute "DROP TABLE " & tblName & ";"

with

db.Execute "DROP TABLE [" & tblName & "]"
0
Rey Obrero (Capricorn1)Commented:


Dim tdf As TableDef
Dim db As Database
Dim tblName As String

Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Right(tdf.Name, 12) = "ImportErrors" Or Right(tdf.Name, 13) = "ImportErrors1" Then

            tblName = tdf.Name
           db.Execute "DROP TABLE [" & tblName & "]"


            End If
    Next tdf
0
JayceWAuthor Commented:
Thanks for the speedy response Capricorn1 but sadly no joy. Now I get the error "Run-time error '3709': The search key was not found in any record

Even though the value populating the tblName variable does exist in the database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:

Dim tdf As TableDef
Dim db As Database
Dim tblName As String

Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Instr(tdf.Name, "ImportErrors")  Then

            tblName = tdf.Name
           db.Execute "DROP TABLE [" & tblName & "]"


            End If
    Next tdf


0
JayceWAuthor Commented:
Even though the code is more efficent sadly the same error is still returned
0
Rey Obrero (Capricorn1)Commented:
do a compact and repair, then in the vba window do Debug>compile
correct any errors raised
0
JayceWAuthor Commented:
Ok so I am attempting what you suggested and the only error that I am getting is one regarding another function ehere to be honest I am a little confused as to why an error is being returned. Can you help?
The error being returned is:
"Expected function or variable"

Public Function iDate()
   Dim db As DAO.Database, tb As DAO.Recordset, x As Variant
   Set db = CurrentDb
   Set tb = db.OpenRecordset("tblPM_all")
   Set x = db.Execute("SELECT TOP 1 last_modified_date FROM tblPM_all Order by last_modified_date DESC;")
End Function
0
Rey Obrero (Capricorn1)Commented:
what is the name of the module where you have the function?
change the name of the module to something like
 modiDate
0
JayceWAuthor Commented:
The module was called modImportData and changing that to modiDate had no impact
0
Rey Obrero (Capricorn1)Commented:
when you get the error, which line is highlighted?

0
JayceWAuthor Commented:
the

.execute

section of code is highlighted
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JayceWAuthor Commented:
I have changed to

.OpenrecordSet and it seems no longer returns an Error in the debug menu.

And the original error is also resolved.

Thanks for your assistance
0
JayceWAuthor Commented:
I meant to close with Capricorn1's response being the correct closure option
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.