Syntax error problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Davies - 30 Jul 2005 11:52 GMT

Dear experts

I have quite a complex SQL statement on my vb6 form to link to an Access
data source
Instead oF going through the tedious task of writing it direct I constructed
a query in Access and cut and pasted the SQL into vb6 made a slight
modification to the WHERE part to filter using the boundtext from comboboxes
on my vb form. But I am getting a syntax error.
My SQL is below.

strSQLNCClass = "SELECT tNC.NCIndex, 'kS ' & [tNCArea].[KS] & ' ' &
[SubjectDesc] & ': ' & [NCArea] & ' - ' & [TopicDesc] AS NCTopic "
strSQLNCClass = strSQLNCClass & "FROM (tKS INNER JOIN (tSubject INNER JOIN
(tNCArea INNER JOIN tNC ON tNCArea.AreaIndex = tNC.AreaIndex) "
strSQLNCClass = strSQLNCClass & "ON tSubject.SubjectNo = tNCArea.SubjectNo)
ON tKS.KS = tNCArea.KS) INNER JOIN tYear ON tKS.KS = tYear.KS "
strSQLNCClass = strSQLNCClass & "Where (((tSubject.SubjectNo) = " &
cbdSubject.BoundText & ") And ((tYear.YrIndex) =" & cbdYear.BoundText & ") "
strSQLNCClass = strSQLNCClass & "And ((tNC.StatusID) = 1) And
((tSubject.StatusID) = 1) And ((tNCArea.StatusID) = 1)) "
strSQLNCClass = strSQLNCClass & "ORDER BY tSubject.SubjectNo, tYear.YrIndex,
tSubject.SubjectDesc, tNCArea.KS, tNCArea.NCArea, tNC.NCIndex;"

1) Can anyone see what is wrong

2) Is there some other modification that has to be made to make SQL from
Access work in VB

3) I normally just use ADO to link to the query direct in Access but in this
case I need it to include the WHERE clause which takes data from the VB
form. Is there a way to pass the items selected in the combobox on VB forms
to an Access query?

could I do remove the WHERE from my Access query and pass it later when
opening the record set in VB i.e. something like

strMyAccessSQL = "SELECT * From MyAccessQuery"
adoMyRS.Open strMyAccessSQL & "WHERE fieldname = " & Mycombobox.boundtext &
, db, adOpenStatic, adLockOptimistic

Regards Ian

Reply to this Message

Pásztor, Zoltán - 30 Jul 2005 15:40 GMT

Comments inline ...

> Dear experts
>
[quoted text clipped - 21 lines]
> tYear.YrIndex, tSubject.SubjectDesc, tNCArea.KS, tNCArea.NCArea,
> tNC.NCIndex;"

^^^

> 1) Can anyone see what is wrong

Try removing the semicolon from the end - ADO does not like it afaik.

If the type of the tSubject.SubjectNo or the tYear.YrIndex fields
happens to be 'Text' (the name suggests otherwise though), then you should
grame the passed values with single quotes.

> 2) Is there some other modification that has to be made to make SQL
> from Access work in VB

It was a long time since I worked with Jet DB, but I used to remove the
excess parentheses and brackets - just for readability. Also I remember
having transformed JOIN constructs to WHERE conditions - but it was for the
sake of generality, when Oracle did not support the ANSI join syntax.

This does not apply to your particular query, but I always transform any
string value passed from program variables to replace any occurrence of the
single quote character to two single quotes. (Not required if you use the
ADODB.Command object for parameter passing)

> 3) I normally just use ADO to link to the query direct in Access but
> in this case I need it to include the WHERE clause which takes data
> from the VB form. Is there a way to pass the items selected in the
> combobox on VB forms to an Access query?

You can use the ADODB.Command object for passing parameters, something like
this (air code):

'-------------------------------------------------------------
Dim sQuery As String
Dim oRS As ADODB.Recordset
Dim oCmd As ADODB.Command
Dim oPar As ADODB.Parameter

' Build your query, marking the variable parameters with special
placeholders
' (simplest case: use the question mark character)
sQuery = "SELECT <fixed part of your query ...> " & _
" WHERE tSubject.SubjectNo = ? AND tYear.YrIndex = ? " & _
" AND <more conditions, non-parametrized> "

Set oCmd = New ADODB.Command
With oCmd
Set .ActiveConnection = oConn ' supposing you have an
open connection
.CommandText = sQuery
Set oPar = .CreateParameter(, adInteger, adParamInput, ,
cbdSubject.BoundText)
.Parameters.Append oPar

Set oPar = .CreateParameter(, adInteger, adParamInput, ,
cbdYear.BoundText)
.Parameters.Append oPar
End With

Set oRS = New ADODB.Recordset
With oRS
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open oCmd, , adOpenStatic, , adCmdText
End With

' and if everything went OK, you have here your recordset populated

'-------------------------------------------------------------

I also remember having used some other Jet-specific method which involved
parametric queries saved in the database, with a special syntax where the
actual parameters were prepended to the query text, but the Command object
serves me well, so I'd abandoned that solution.

> could I do remove the WHERE from my Access query and pass it later
> when opening the record set in VB i.e. something like
>
> strMyAccessSQL = "SELECT * From MyAccessQuery"
> adoMyRS.Open strMyAccessSQL & "WHERE fieldname = " &
> Mycombobox.boundtext & , db, adOpenStatic, adLockOptimistic

Of course you can - this is just string manipulation deferred until the
actual call - the expression is evaluated before the result is passed as
call argument.

> Regards Ian

hth

Signature

Reply to this Message

VB Forum / Database Access / July 2005

Syntax error problem