Querying MS Access linked tables fetched from SQL Server 2008

[resumes123]

I am using MS Access 2007
I had linked a table called tickdata_master from MS SQL Server 2008
I tried to perform a select query on the linked table after I had fetched data from SQL server
I got a SQL server error when I tried to Query in MS Access
While I am querying Linked tables, can I use MS Access syntax or should I always have to use SQL Server syntax

The table has 4 fields
Date, Time, Price, Volume
I am trying to fetch the min(time), max(time), max(price), min(price) for every day in a given date range

Please mention if it is incorrect to give a SELECT query in MS Access on Linked tables fetched from SQL server

Is there an alternate way of querying in MS Access
I am not too good in SQL server query

The SELECT query that I tried to execute is as below


"select v1.date, v1.open_time,v1.close_time,v1.low_price,v1.high_price, v1.volume_sum,v2.open_price , v3.close_price , ( v3.close_price - v2.open_price) as difference " _
& "from ( select date, min(time) as open_time, max(time) as close_time, min(price) as low_price,max(price) as high_price, sum(volume) As volume_sum from dbo_tickdata_master " _
& "where date between cdate('" & CDate(CDate(l1)) & "') and cdate('" & CDate(CDate(h1)) & "') and time between cdate('" & CDate(CDate(l2)) & "') and cdate('" & CDate(CDate(h2)) & "') group by date ) v1 , ( select date, min(time) as open_time, min(price) as open_price from dbo_tickdata_master " _
& "where id in ( select min(id) from dbo_tickdata_master where date between cdate('" & CDate(CDate(l1)) & "') and cdate('" & CDate(CDate(h1)) & "') " _
& "and time between cdate('" & CDate(CDate(l2)) & "') and cdate('" & CDate(CDate(h2)) & "') group by date ) group by date ) v2, (select date, max(time) as close_time, max(price) as close_price " _
& "from dbo_tickdata_master where id in ( select max(id) from dbo_tickdata_master where date between cdate('" & CDate(CDate(l1)) & "') and " _
& "cdate('" & CDate(CDate(h1)) & "') and time between cdate('" & CDate(CDate(l2)) & "') and cdate('" & CDate(CDate(h2)) & "') group by date ) group by date ) v3 where v1.Date = v2.Date and v1.date = v3.date "

 

[dhookom]

I would create a view on SQL Server and then select from the view. It isn't clear where l1, h1, l2, etc are coming from or their data types.

If you are querying against linked tables from Access the date delimiter is #. Also Date and Time are reserved words so you should enclose them in []s.

If you understand SQL Server syntax, you could create the proper SQL and use a pass-through query which would be much more efficient. You can change the SQL property of a saved query with code like:

Currentdb.QueryDefs("qsptMyPTQuery").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP