VBA / SQL Problem. Probably very easy...

[Pilly170]

Hi,

Im trying to make some VBA Sql queries, this below one works ok.


Function Delete_Table_Mysql_XYZ()
Dim DelMysql As String

DelMysql = "DELETE mysql_dealer_10076.*, * from mysql_dealer_10076;"
DoCmd.SetWarnings warningsoff
DoCmd.RunSQL DelMysql
DoCmd.SetWarnings warningson

End Function


This is my updated version, i want to pass the variable over to the query so i dont have to write multiple functions.


Function Delete_Table_Mysql(Optional Custno)
Dim DelMysql As String

DelMysql = "DELETE mysql_dealer_" & Custno & ".*, * from mysql_dealer_" & Custno & ";"

DoCmd.SetWarnings warningsoff
DoCmd.RunSQL DelMysql
DoCmd.SetWarnings warningson
End Function


Is this an obvious mistake im making?? Can anyone point me to where Ive gone wrong?

 

[abaldwin]

What is not working. Are you trying to delete the contents of a table or the table itself?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Pilly170]

The contents.

the table name is dealer_(variablename)

 

[JerryKlmns]

Sub KlearTable(myTable As String)
CurrentProject.Connection.Execute "DELETE FROM " & myTable, , 129
End Sub

 

[bboffin]

I'm surprised the first example works since it has a syntax error. The ', *' is redundant.

However the DELETE statement in SQL (even Access SQL) is normally simplified to:

DELETE FROM tablename

There is no reason to repeat the table name with .* at the start.

Try:

DelMysql = "DELETE FROM mysql_dealer_" & Custno & ";"



Bob Boffin

 

[JerryKlmns]

There is not need for the [red];[/red].
But this [red], , 129[/red] is interpreted as options for the execution (adCmdText = 1 and adExecuteNoRecords=128), meaning that it is a textual command and do not return any records. Results to faster execution.