SQL statement DELETE with variable as table

[kk45]

Hi,

I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :banghead::

Private Sub cmdRunCheck_Click()

Dim strSQL As String
Dim strTempTbl As String

strTempTbl = "tblCheckDoubles"

strSQL = "DELETE * FROM " & "'" & strTempTbl & "'"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

What is clear is that if I use this line the Sub is working smoothly

strSQL = "DELETE * FROM tblCheckDoubles"

Please do not tell me that it is not possible to use a variable as Table parameter....

Thanks for your help
kk

 

[plog]

Syntax error in query.

So what's the query you are running? Now don't spit code back at me, tell me the exact SQL that code is trying to execute. You know what the a good one is, so what's the difference between that one and the one that fails?

Debugging 101, don't assume you know what the computer is doing. Verify.

 

[kk45]

So what's the query you are running? Now don't spit code back at me, tell me the exact SQL that code is trying to execute. You know what the a good one is, so what's the difference between that one and the one that fails?

Debugging 101, don't assume you know what the computer is doing. Verify.

Sorry plog but I do not get your point. The exact SQL is what it is written in the string: delete all the record from the table <<tblCheckDoubles>>. As far as I can see the difference is just this part of the code << & "'" & strTempTbl & "'">> which means the variable which identify the table <<tblCheckDoubles>>.

Thanks for your patience
kk

 

[pr2-eugin]

Private Sub cmdRunCheck_Click()
    Dim strSQL As String
    Dim strTempTbl As String

    strTempTbl = "tblCheckDoubles"

    strSQL = "DELETE * FROM [COLOR=Red][B]" & strTempTbl[/B][/COLOR]

    CurrentDb.Execute strSQL, dbFailOnError
End Sub

 

[plog]

I was so close to teaching kk how to fish, pr2.

kk--I wanted you to spit out your strSQL value so you could see what it actually was with your own eyes. When you did that you would see that the SQL you were trying to execute was this:

DELETE * FROM 'tblCheckDoubles'


Then you would have seen that your failed SQL had single quotes in it where your valid SQL did not. Again, never assume the computer is doing what you are thinking it is doing. When your code fails and you know where, but not why--spit out the relevant variable values to see if they match what you expect them to be. When they are not, you've moved one step closer to fixing the issue.

 

[kk45]

Private Sub cmdRunCheck_Click()
    Dim strSQL As String
    Dim strTempTbl As String

    strTempTbl = "tblCheckDoubles"

    strSQL = "DELETE * FROM [COLOR=Red][B]" & strTempTbl[/B][/COLOR]

    CurrentDb.Execute strSQL, dbFailOnError
End Sub

Thanks a lot indeed pr2-eugin!!! I lost a lot of time trying to move/add " and ' arond the statement without any result and it was so..... clean & easy.

Thanks a lot again for your time!!
kk

 

[kk45]

I was so close to teaching her a valuable lesson pr2.

kk--I wanted you to spit out your strSQL value so you could see what it actually was with your own eyes. When you did that you would see that the SQL you were trying to execute was this:

DELETE * FROM 'tblCheckDoubles'


Then you would have seen that your failed SQL had single quotes in it where your valid SQL did not. Again, never assume the computer is doing what you are thinking it is doing. When your code fails and you know where, but not why--spit out the relevant variable values to see if they match what you expect them to be. When they are not, you've moved one step closer to fixing the issue.

Now I get your point . To be honest I'm getting confused when I need to use variables and controls in SQL statement because of " and ' position. I think I learned the lesson anyway so do not go too hard on pr2... and thanks to you too for trying. I promise your efforts won't go wasted

Cheers!
kk

 

[pr2-eugin]

I was so close to teaching kk a valuable lesson pr2.

I would have not done anything, but if you had given him/her some links on what debugging is or how to debug, then the OP could have made a discovery of the err and made the correction. There was just a statement

Debugging 101, don't assume you know what the computer is doing. Verify.

Sorry not trying to be a J£rk, You could have show how to verify?

Also the response given back makes a very clear statement of how far the OP understood what you meant all about Debugging. So I just gave the code ! No comments. Sorry if I stole the thunder.

 

[pr2-eugin]

Thanks a lot indeed pr2-eugin!!!

You're welcome, but as plog suggested you need to learn how to debug your code. Here is the very basic debugging technique, using the Immediate window (http://www.baldyweb.com/ImmediateWindow.htm)

Here are some more : http://www.cpearson.com/excel/DebuggingVBA.aspx

.......so do not go too hard on pr2...

I stand corrected ! However I gave my explanation of why I did that. We learn from each other ! Good luck.