Defined string variable as SQL statement errors. Why?

[gmaxey]

Can anyone explain why I get an error in the following code:


Dim strSQL as String
On Error Resume Next
'Define the statement.
strSQL = "INSERT INTO tblNumSchemes VALUES ('Test', False, '0', '0', '0');"
'This errors. Why?
m_Conn.Execute strSQL
If Err.Number <> 0 Then Debug.Print Err.Number & " " & Err.Description
On Error GoTo 0
'When what appears to be the very same thing spelled out does work!
m_oConn.Execute "INSERT INTO tblNumSchemes VALUES ('Test', False, '0', '0', '0');"

It appears that I am missing something very simple, but I just can't determine what it is. Thanks.

[Doug Robbins]

Hi Greg,

What type of data is each field expecting?

Assuming that the Test goes into a Text field, the False goes into a Yes\No field and that the 0s go into Numeric fields, use:

strSQL = "Insert into tblNumSchemes Values('Test'," & False & ", " & 0 & ", " & 0 & ", " & 0 & ");"


Regards,
Doug

[gmaxey]

Hi Doug,

This works:
m_oConn.Execute "INSERT INTO tblNumSchemes VALUES ('Test', False, '0', '0', '0');"

I just can't figure out why it works and this desn't:
strSQL = "INSERT INTO tblNumSchemes VALUES ('Test', False, '0', '0', '0');"
'This errors. Why?
m_Conn.Execute strSQL

They both appear to be one in the same.

[gmaxey]

Solved. I fell like a dolt! It errored because:

m_Conn.execute strSQL
should have been:
m_oConn.Excute strSQL

Sorry.