RecordSet Reference Problem

[LouiseJ]

I have the following code which is producing an error at ‘RecordSet.Open’ with the following message;

Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELRCT’, or ‘UPDATE’.

I am assuming that an SQL statement is expected instead of “QrySalesEnquiries&Customer". However I can find plenty of sample code formatted in exactly the same way using the name of a query.

What am I doing wrong?

Thanks

LouiseJ




Private Sub cmdOK_Click()

Dim LocalConnection As ADODB.Connection
Dim RecordSet As New ADODB.Recordset

Set LocalConnection = CurrentProject.Connection

RecordSet.Open "QrySalesEnquiries&Customer", LocalConnection, adOpenKeyset, adLockOptimistic, adCmdText


End Sub


SQL of Query as follows;

SELECT [QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]
FROM [QrySalesEnquiries&CustomerSorted]
GROUP BY [QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]
HAVING ((([QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]) Like [Forms]![FrmAvalibleAccountNumbersNext]![txtAccountNumber

 

[Remou]

The best way to build the SQL string is in the Query Design window. You can then cut the SQL from SQL view and use it in your code. For example:

[tt]strSQL="SELECT [QrySalesEnquiries&CustomerSorted].[Customer Account Number] " _
& "FROM [QrySalesEnquiries&CustomerSorted] " _
& "GROUP BY [QrySalesEnquiries&CustomerSorted].[Customer Account Number] " _
& "HAVING ((([QrySalesEnquiries&CustomerSorted].[Customer Account Number]) = "
& [Forms]![FrmAvalibleAccountNumbersNext]![txtAccountNumber][/tt]

Assuming that [Customer Account Number] is numeric.

'Recordset' is a reserved word and should not be used to name objects.

Have you considered DAO? it is often easier than ADO when working with Access.

 

[LouiseJ]

Thanks Remou for the reply

The SQL posted was cut from the design window and the query seems to work OK.

From your answer can I assume that I cannot use the reference to QrySalesEnquiries&Customer in the RecordSet.Open criteria but must use an SQL statement (whether previously assigned to variable ‘sql’ or otherwise)? As mentioned I have seen many code examples that do directly use the query name as opposed to the SQL.

Again I originally tried to use DAO but kept getting an error ‘User type not defined’. I could also not find a VBA reference for it.

Thanks for pointing out the reserved word use of RecordSet. Will amend accordingly.

LouiseJ

 

[Remou]

You can set a reference to the Microsoft DAO 3.x Object Library under Tools->References in the Code Window. Once this is done, you should be able to use DAO.