Invalid SQL Statement Error 1

[Blakey100]

Do you have nay ideas why a query that works OK by itself, fails when opened as a recordset in VB?

I have an MS Access query that references some fields on a form as criteria.

When I run the query directly (as a query), with the form open, it performs perfectly.

When I attempt to open the same query as a recordset in VB, using an ADODB connection, it works fine until I add a reference to a field on the form, when I then get error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The SQL (generated by MS Access design view) that works fine by itself, but fails when opened as a recordset is included below. If I remove the condition

((EvnLog.TimeDate) Between [forms]![frmMain].[dteDayFirst] And [forms]![frmMain].[dteDayLast])

from the query, the recordset opens OK.

SQL
---

SELECT EvnLog.Code, EvnLog.Loc, EvnLog.TimeDate, DEV.TNA, COMPANY.Company, COMPANY.Name, tblCompanyCodes.Type, Year([TimeDate]) AS [Year], Month([TimeDate]) AS [Month], Day([TimeDate]) AS [Day], [Forms]![frmMain].[dteDayFirst] AS Test
FROM (((COMPANY INNER JOIN ([NAMES] INNER JOIN (EvnLog INNER JOIN CARDS ON (EvnLog.Code = CARDS.Code) AND (EvnLog.Loc = CARDS.LocGrp)) ON (NAMES.ID = CARDS.NameID) AND (NAMES.LocGrp = CARDS.LocGrp)) ON (COMPANY.Company = NAMES.Company) AND (COMPANY.LocGrp = NAMES.LocGrp)) INNER JOIN DEV ON EvnLog.Dev = DEV.Device) INNER JOIN Evn ON EvnLog.Event = Evn.Event) LEFT JOIN tblCompanyCodes ON (COMPANY.LocGrp = tblCompanyCodes.LocGrp) AND (COMPANY.Company = tblCompanyCodes.Company)
WHERE (((EvnLog.TimeDate) Between [forms]![frmMain].[dteDayFirst] And [forms]![frmMain].[dteDayLast]) AND ((DEV.TNA)="I" Or (DEV.TNA)="O") AND ((EvnLog.Event)=8))
ORDER BY EvnLog.Code, EvnLog.TimeDate, Year([TimeDate]), Month([TimeDate]), Day([TimeDate]);

 

[cmmrfrds]

Show your ADO code right before and when you open the recordset.

 

[Blakey100]

Wow, that was a quick response!

I've included all the code leading up to opening the recordset. Most of it is just fiddling around with form labels etc to make things look pretty.

Thanks!

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    
    'cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbReports.mdb"

    Dim rstqryCodeEvnLog As New ADODB.Recordset
    Dim rsttblCodeHoursDaily As New ADODB.Recordset
    Dim rstqryLastEvnLog As New ADODB.Recordset
    
    Dim strTNA, strTNAPrevious As String
    Dim dteEventDate, dteEventTime, dteEventDatePrevious, dteEventTimePrevious, dteLastTimeDate As Date
    Dim dblCode As Double
    Dim dblHours As Double
    Dim lngCount, lngRecords As Long
    Dim strSQLqryCodeEvnLog As String
    
    Dim dteMidnight As Date
   
    'Display "Extracting" information
    
    Me.lblIntro.Visible = False
    Me.lblExtract.FontBold = True
    Me.lblExtract.ForeColor = RGB(255, 0, 0)    'red
    Me.lblExtract.FontSize = 12
    Me.lblExtract.Visible = True
    
    ActiveXCtl7.Visible = True
    ActiveXCtl7.Min = 1
    ActiveXCtl7.Max = 100            'set max for progress bar
    ActiveXCtl7.Value = 70           'set initial value
    
    Me.lblProcess.FontBold = False
    Me.lblProcess.ForeColor = RGB(0, 0, 0)    'red
    Me.lblProcess.FontSize = 10
    Me.lblProcess.Visible = True
    
    Me.Repaint
    
    'Open recordsets
    
    rstqryCodeEvnLog.Open "qryCodeEvnLog", cnn, adOpenStatic, adLockReadOnly

 

[cmmrfrds]

What is happening is that the Open method does not recognize your query name. Check the 5th parameter of the open method since it indicates what the open is to expect, such as, a table name or an sql string etc... The default is a sql string which you are not providing. Here is a way to resolve the query as an sql string using the adox catalog to reference the query.

''- Microsoft ADO Ext. 2.1 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
'- the example is the current connection but you will need to refer to your connection.

Dim vn As View
'- set to specific view name
Set vn = cg.Views("query1")
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

 

[Blakey100]

Thanks. Unfortunately ADOX is not installed on the server I'm using. I'll pursue that separately!

Further investigation shows that there seems to be a fundamental problem every time I attempt any type of join between tables. Single table queries work OK.

I created the code below and always get the error:

Run-time error '-2147467259(80004005)
Method 'Open' of object '_Recordset' failed

Obviously my larger multi-table query will never work with this type of problem!!

I've asked a few people and they can't see why the code below should fail. Any left-field sugggestions? There is obviously some problem with Access not recognising the second table or understanding the join.

The code has been pasted from an Access query, which runs OK, and if I open the query using DoCmd.Openquery, that works OK too. It's only trying to open a recordset that fails.

Sub test()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Dim strSQL As String

'Create the connection

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'Define the SQL

strSQL = "SELECT COMPANY.Company, COMPANY.Name, NAMES.LName FROM COMPANY INNER JOIN [NAMES] ON (COMPANY.LocGrp = NAMES.LocGrp) AND (COMPANY.Company = NAMES.Company);"

'Open the recordset

rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly

rst.Close

End Sub

 

[Blakey100]

I've done some furhter debugging and found that the NAMES table was the cause of the problem. By enclosing every reference to NAMES in square brackets, Access is no longer confused. As it's not my database so I can't rename the tables. USe of aliases may be OK too; I didn't try.

 

[cmmrfrds]

If the query works directly in Access from the Query Grid, then ADO must have NAMES as a reserved word in its object model, but that is just a guess.