error on SQL using Excel VBA

[Conrado Natac]

'this is the error code'


rs.Open "SELECT Distinct [Inv #], [Cust #], [Date],[Due Date], [Amount] as InvBal FROM [data$] WHERE [data$].[Cust #]=" & "'" & cmbcustno.Text & "'" _
& "group by [data$].[Apply to]", acnn, adOpenKeyset, adLockOptimistic

'this is the connection

Option Explicit

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Public Sub OpenDB()


If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name


cnn.Open
End Sub

Public Sub closeRS()
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
End Sub

 

[Colin Legg]

What is the actual error message?

By the way, you shouldn't use ADO to query the workbook the code is in: querying open workbooks with ADO causes a memory leak.