Jet OLEDB 'LIMIT' clause

[8bitbanger]

Hi Folks,

I'm monitoring an access database for new records using LV2009 + the Database connectivity toolset(not 8.5.1 as in sig). Anyway the number of rows could be on the order of 200,000+, so I want to limit my query to only one record, the last. Unable to get the LIMIT clause to function, I searched and came across this: http://office.microsoft.com/en-us/access/HP010322501033.aspx. It states:

Microsoft Jet SQL does not support the following ANSI SQL features:

• DISTINCT aggregate function references. For example, Microsoft Jet SQL does not allow SUM(DISTINCT columnname).
• The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE Clause to limit the scope of a query.

Is this the problem I'm going up against? The reason I use Jet is because I can more easily programatically load individual databases (and not have to mess around with UDL files).

Any thoughts on a work-around?

Happy Wiring,

Jamie

[8bitbanger]

Ughh... I selected the LabVIEW forum, not sure how it got here though.... mods please move?

[8bitbanger]

Potential workaround: may not be the most efficient solution though (my only exp. w/database access is from PHP/mySQL many years back!). Comments welcome. 🙂

[Brad_Turpin]

Hi Jamie,

I've frequently used DISTINCT in SQL queries run against a JET db (Access).  I've infrequently but successfully used LIMIT in SQL queries run against a JET db.  Most of my experience is with running DIAdem VBScripts to return records from data bases, but it really shouldn't matter if LV or DIAdem is sending the SQL string to the db to execute.

What is the exact query string you are sending with LV?

Brad Turpin

DIAdem Product Support Engineer
National Instruments