How to execute complex SQL queries using Common Table Expressions on MS Access via OleDb

Question

I'm having trouble executing a complex SQL query in VB.NET using OledbDataAdapter. This Sql query works fine in the W3school's SQL Tryit Editor. Following is my existing VB.NET code and is there any way to execute that kind of a SQL query directly in VB.NET or can anyone change this query to work with VB.NET with same results?.

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = C:\database.mdb"
    con.ConnectionString = dbProvider & dbSource
    con.Open()
    sql = "With query1 as (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='0') " _
        ",Query2 as (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='1') " _
        "select query1.val as 'TT348',Query2.val as 'TT358',Query2.DateAndTime as 'DateAndTime' " _
        "From query1,Query2 "_
        "where query1.DateAndTime=Query2.DateAndTime"
    da = New OleDb.OleDbDataAdapter(sql, con)

    da.Fill(ds, "Log")
    con.Close()

    DataGridView1.DataSource = ds

When I run this code snippet it gives an error telling

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

Greatly appreciate any help and thank you...

StuartLC · Accepted Answer · 2015-05-09 18:49:42Z

7

Further to Peter's point of the lack of CTE support in Access, since neither of the the referenced CTE's use advanced features like recursion, you can easily change the CTE's to Derived Tables with the same aliases, and retain the flow of the original query. Also, it is considered better practice use a JOIN to join tables, not WHERE clauses. It's as simple as:

SELECT query1.val as 'TT348',Query2.val as 'TT358',Query2.DateAndTime as 'DateAndTime' 
 FROM 
 (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='0') AS query1
 INNER JOIN
 (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='1') AS Query2 
 ON query1.DateAndTime=Query2.DateAndTime;

answered May 9, 2015 at 18:49

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HansUp Over a year ago

Use query1.val as 'TT348' if you want those quotes included in the aliased name. Otherwise, use query1.val as [TT348] or query1.val as `TT348`

BWhite Over a year ago

I didn't know you could join with a random select statement. Neat trick.

Peter Schneider · Accepted Answer · 2015-05-09 17:21:33Z

5

The problem is that the Access database cannot handle Common Table Expression (CTE) Queries. Either use SQL Server or try to change your query.

answered May 9, 2015 at 17:21

Peter Schneider

2,9591 gold badge16 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to execute complex SQL queries using Common Table Expressions on MS Access via OleDb

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related