Syntax Error in From Clause
I am trying to figure out why I am having problems with an SQL statement that I am passing to my DB. In one form, I set a string variable to contain the SQL statement that I want to execute. Then I proceed to load another form, where in the Form_Load sub I open my Record Set. The code is as follows:
aString = "SELECT * FROM aDATA WHERE intArticleCreator=002"
aString is a public variable that I have placed in a module.
Set rs = New ADODB.Recordset
rs.Open aString, Conn, adOpenKeyset, adLockOptimistic, adCmdTable
I have no problems with this code when I simply assign the table name to aString (when I want to view all records in the table), so I assume that the error originates in some way with my sql statement.
Does this sound accurate, or am I way off base with this?
[828 byte] By [
Broodmdh] at [2007-11-19 7:21:35]
# 4 Re: Syntax Error in From Clause
try this
rs.Open aString, Conn, adOpenKeyset, adLockOptimistic, adCmdText
# 7 Re: Syntax Error in From Clause
THE SQL STATEMENET SHD BE LIKE BELOW (U MUST ADD SINGLE QUOTE - '002')
aString = "SELECT * FROM aDATA WHERE intArticleCreator='002'"
I think an error would be generated if you enclose the criteria of an integer field...
I am right, 002 would be converted to 2, I just tried it...
Try
aString = "SELECT * FROM aDATA WHERE intArticleCreator=cint(002)"
or
aString = "SELECT * FROM aDATA WHERE intArticleCreator=val(002)"
d-u at 2007-11-9 20:51:04 >
# 8 Re: Syntax Error in From Clause
Continuing Dmorley
this description from MSDN
adCmdUnspecified Does not specify the command type argument.
adCmdText Evaluates CommandText as a textual definition of a command or stored procedure call.
adCmdTable Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc Evaluates CommandText as a stored procedure name. adCmdUnknown Default. Indicates that the type of command in the CommandText property is not known.
adCmdFile Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with
adCmdTableDirect.
