Running query from MS Access to Lookout Direct database

[icsbrad]

Does anyone have any experience running querys on a Lookout Direct database from Access? I am using Access 2007 and have successfully made the ODBC connection from Access to the database but the only query that I can get to function is finding records based off of the "Date/Time" & "Interval" fields. Nothing seems to work when I try to develop a query for instance on the average, min, max, etc values of a field referencing a PLC tag. I have tried using the Query Wizard and also have manually typed commands in the SQL view, but to no avail. The errors I get all seem to be syntax related. I've tried moving brackets & quotation marks but still no luck. Below are a few examples of what works and what doesn't.

Example 1: Working query based off of "Interval" & "Time"
SELECT Traces.["Interval"], Traces.["LocalTime"], Traces.["\\DJCJMMG1\Dredgemaster_v01_00\DataLog\Scale1_TPH"]
FROM Traces
WHERE (((Traces.["Interval"])="0:1:0") AND ((Traces.["LocalTime"])>#4/1/2009 16:35:10#));

Example 2: Working query (unrelated file) based off of min value of "Field 1"
SELECT DISTINCTROW Min([Table1].[Field1]) AS [Min Of Field1]
FROM Table1;

Example 3: Non-Working query based off of min value of PLC value (Scale1_TPH)
SELECT DISTINCTROW Min([Traces].["\\DJCJMMG1\Dredgemaster_v01_00\DataLog\Scale1_TPH"]) AS [Min Of "\\DJCJMMG1\Dredgemaster_v01_00\DataLog\Scale1_TPH"]
FROM Traces;


Any help would be appreiciated as I haven't found much documentation on this subject on AD, Host or Nat. Instruments websites, thanks in advance for any help.

[Ryan.S]

First, we need to make sure if the Lookout Direct database is the NI Citadel 4 database. I'm not sure whether or not Automation Direct changed anything in it. So, let's assume they are the same.

Take a look at this KB.

http://digital.ni.com/public.nsf/allkb/C7D32F9A59D4637086256A7200692F30?OpenDocument

The SQL statements in KB are for the Lookout object. So the use of double quotes (") may be different if you use Access.

What you need to do is to give it a time interval when you query avg or min. And the "DISTINCTROW" may not be supported by Citadel 4.

For example, try the following SQL

SELECT LocalTime, "\\computername\process\Pot", "Avg{\\computername\process\Pot}"

FROM Traces
WHERE Interval = ' 1:00'
AND LocalTime > ' 2001-06-15 09:00:00'
AND LocalTime <= ' 2001-06-15 09:30:00'