CODEWITHSUNDEEP
sqldatabasevb.netms-accessoledbdataadapter
Thusitha Manathunga
Thusitha Manathunga

Reputation: 37

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

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...

Upvotes: 2

Views: 5731

Answers (2)

StuartLC
StuartLC

Reputation: 107277

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;

Upvotes: 7

Peter Schneider
Peter Schneider

Reputation: 2929

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

Upvotes: 5

Related Questions