Recordset error with Query

[gpurger]

Hi again,

I now have the ability to select a query and have a form open on that record set.

The problem is that some of the queries generate the error message : Invalid SQL statement; expected 'DELETE',INSERT','PROCEDURE','SELECT' or 'UPDATE'

Code is
Private Sub Combo0_Click()

sQuery = Combo0.Column(0)

DoCmd.OpenForm "frmFilter"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open sQuery, CurrentProject.Connection
Set Forms("frmFilter").Recordset = rstSuppliers
Forms("frmFilter").UniqueTable = sQuery

End Sub

THe queries are the same type.

 

[Tim L]

Sounds like the SQL has been corrupted or that you have copied a multiple line SQL statement from somewhere. Access doesn't like carriage return line feeds (CLRF) in SQL statements behind the scenes (although it lovingly puts them in when we view the SQL for a query). If you have copied the SQL from a query and put it in a table for selection and use try doing so again but first replace all the CRLFs with spaces.

HTH

Tim

 

[gpurger]

Hi Tim,
Sorry for being thick but5 I do not really understand what you mean.
Yes, it was copied and modified from MS Help.
I am using 2000.

Original code:
Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
Forms("Suppliers").UniqueTable = "Suppliers"
End Sub

Cheers

Gordon

 

[Tim L]

Sorry Gordon, I didn't mean the code but the SQL/Queries, which I presume that you have stored in a table to which Combo0 refers in it's RecordSource property (or a query based on it).

Is the first row of the recordset for Combo0 valid SQL? Do the queries work outside of the code? - - - Can you paste the text in any of the first columns of the recordset directly into a query (SQL view) and does it work? Open the RecordSet/Table, highlight the SQL (which will probably start with "SELECT") press CTRL+C and then create a new query, don't add any tables, go to SQL view and press CTRL+V to paste the SQL in. If the SQL is valid you will then be able to switch between Design, Datasheet and SQL view (although Design view is not available for a UNION query Datasheet still will be).

Tim

 

[gpurger]

Hi Tim,
Thanks for that but I solved the problem.
It was the Query names that had the effect.
The Queries that worked were normal but those that did not had hyphens in them i.e.

QryCDFConfigBaseline Worked
QryChangeList-2-P9B Didn't.


Thanks for your time it was useful and gave more info.

Cheers

Gordon

 

[Tim L]

Let that be a lesson to us all to stick to naming conventions

Tim