Syntax error: strSQL_Drop1 = "DELETE " & strCriteria & ".* FROM " & strCriteria & ";"

[skydivetom]

Hello:

I currently using the following to drop a table before re-importing the XLS.

        Dim strSQL_Drop1 As String
        strSQL_Drop1 = "DELETE tblSource1.* FROM tblSource1;"
        CurrentDb.Execute strSQL_Drop1

In this example, the table name "tblSource1" is referenced two (2) times in my strSQL_Drop1 (2nd line). Thus, in case the table name changes, I need to update it in 2 places.

Instead, I'd like to use generic strCriteria statement. At the present time, however, I'm getting a syntax error... I'm sure it's missing a double-quote somewhere (or has one too many ").



The proposed code looks like this:

        Dim strCriteria As String
        strCriteria = tblSource1
        strSQL_Drop1 = "DELETE " & strCriteria & ".* FROM " & strCriteria & ";"        
        CurrentDb.Execute strSQL_Drop1

Again, I currently encountering a run-time error '3075' ("Syntax error (missing operator) in query expression '.*'.

What's the correct syntax for making this change?

Thank you,
Tom

[ranman256]

why not just use a query? It wont get the syntax wrong.
then its: docmd.openquery "qdDelQry"

why not:
strSQL_Drop1 = "DELETE * FROM tblSource"




you need quotes around the tablename
strCriteria = "tblSource1"

[skydivetom]

Never mind... I solved it. Problem was NOT w/ the SQL statement but the DIM of tblSource1.

I changed it from
strCriteria1 = tblSource1
to
strCriteria1 = "tblSource1"
... and it worked.

[Minty]

You do know you can remove the first table reference?

strSQL = "Delete * FROM tb_MyTable"
db.Execute strSQL

[orange]

The DROP TABLE statement is used to drop an existing table in a database - Data structure and the data.

The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;

[skydivetom]

Copy all -- I ended up a different process. See solution below. Expert Welshgasman assisted w/ the solution.

Sub DeleteRecordsFromTables()
Dim tdf As TableDef
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.TableDefs
    If Left(tdf.Name, 3) = "tbl" Then
        strSQL = "DELETE * FROM " & tdf.Name
        Debug.Print strSQL
        db.Execute strSQL
    End If
Next

End Sub

[Minty]

Just as a heads up if you ever move this to SQL server you can use the Truncate command to delete the entire contents of the table but leaving its structure intact.

TRUNCATETABLE tb_flibble

The beauty of this is that if you are deleting tonnes of rows in SQL using DELETE * ... SQL keeps recording a transaction log of each row being deleted.
If you have 10000 rows this takes a while (figuratively speaking).

Truncate does away with this, it literally removes all the records in one fell swoop, really quickly.

[skydivetom]

Thank you, Minty... that's good feedback. Appreciate your input/recommendations.