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

JayceW
JayceW used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
chnage this

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

with

db.Execute "DROP TABLE [" & tblName & "]"
Top Expert 2016
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

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016
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


Author

Commented:
Even though the code is more efficent sadly the same error is still returned
Top Expert 2016
Commented:
do a compact and repair, then in the vba window do Debug>compile
correct any errors raised

Author

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
Top Expert 2016
Commented:
what is the name of the module where you have the function?
change the name of the module to something like
 modiDate

Author

Commented:
The module was called modImportData and changing that to modiDate had no impact
Top Expert 2016
Commented:
when you get the error, which line is highlighted?

Commented:
the

.execute

section of code is highlighted

Author

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

Author

Commented:
I meant to close with Capricorn1's response being the correct closure option

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial