SQL string works, but not w/ ADO to open qry from frm 1

[colossalUW]

Hi all,

I have a form with which the user can build a query by selecting criteria from various list boxes and AND/OR conditions with radio buttons. Upon clicking OK, the query opens. Also, the user can click to export to Excel.

This works great. I use the parent table Grants and several linked lookup tables so that the info is displayed rather than the PKID.

One set of information, the Section, is set up a little differently though. Each grant can have more than one section, so this info is stored in tblSections by the Grant # and SecID. Another table, GIMSections is the lookup table for sections.

In the query builder, I have no problem setting the lookup relationship from Grants through tblSection to GIMSections in order to display the section itself rather than its PKID.

When I transfer this SQL statement to my code in order to have the form open the query, I get the error: Method 'Command' of object 'View' failed. I've troubleshot this down to the sections bit of the SQL causing the error. Why will this not work? The SQL is valid. All help greatly appreciated.

Simplified version of code for sending SQL to query:

Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim qry As ADOX.View
Dim strSQL as String

'Build SQL statement
strSQL = "SELECT Grants.[GC1#], tblSections.SecID, GIMSections.Section " & _
"FROM Grants INNER JOIN (GIMSections INNER JOIN tblSections ON GIMSections.SecID = tblSections.SecID) ON Grants.[GC1#] = tblSections.[GC1#];"

'Apply the SQL statement to qrySecCk
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qrySecCk").Command
cmd.CommandText = strSQL
Set cat.Views("qrySecCk").Command = cmd
Set cat = Nothing
DoCmd.OpenQuery "qrySecCk"

 

[RoyVidar]

List of reserved words in Access 2002 and Access 2003 and List of Microsoft Jet 4.0 reserved words lists it as a reserved word that shouldn't be used as names of ..., you might get around with ... GIMSections.[Section] ... (but, but, but - altering stored queries programatically with ADO/ADOX ...)

Roy-Vidar

 

[colossalUW]

You nailed it, Roy. The brackets solved the problem. I did not know 'Section' was reserved.

Thanks!