SQL Syntax for Access database

[dacad]

This pertains to LV only due to the fact that LV requires a slightly different syntax for SQL statements for use with Access than what you will see in the Access Queries SQL view.  For instance, I discovered that if you use a  ?  for a character placeholder  (can be any single character) in Access, you need to use an  _  (underscore)  for this feature.  I also discoverd that you use a % sign instead of an *  (asterisk) for any number of characters.

The lat thing I am having trouble with is specifying a range of characters that could be in a certain position in the text field.  For instance, if the first character can be only an alpha between  A and Z, and the next two characters can be numbers anywhere from 1 to 12,  I would use   LIKE "[A-Z][1-12]*"      In LV, the double quotes are are replaced with single quotes but I cannot figure out what to replace the brackets with. 

The brackets do not return a syntax error but also do not return any results that should fit the pattern.

I have tried parenthesis, double quotes, using a TO in place of the dash, etc.

Any input on this is very much appreciated.

Doug

I guess sometimes writing the problem out helps one deduce the answer by taking a closer look at what has been tried and what has not.

So  LIKE "[A-Z][1-12]*"   in Access will be   LIKE ('[A-Z][01-12]%')  in LV.    I hadn't tried the pecent sign in place of the asterisk using the brackets.    Too bad this stuff isn't spelled out in detail somewhere.

[Danubio]

Hello Dacad,

I found this two links and thought that you could review them.

Utility for generating SQL statements for MS-ACESS
https://decibel.ni.com/content/docs/DOC-10091

Can I Use LabVIEW to Open an Access Database Without Using SQL Functions?

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

Best regards,

Daniel Cabezas

Applications Engineering

National Instruments

www.ni.com/support  

[mikeporter]

I'm confused by your initial statement. Why does the string need to be "slightly different" in LV? You aren't using the database connectivity toolkit are you? If so, that's problem number one. Check here. Check the last page of the thread for updated VIs.

Mike...

[dacad]

Thanks Danubio for the links.  The one for the SQL utility may be helpful as I go forward.

Mike,  yes I am using the connectivity toolkit.  It's what I cut my teeth on and up to this point, has not caused me any issues.   I rarely make changes after implementation but if I do, the use of typedef's keeps it pretty painless.  A brief read on the doc included in your referenced zip file confirms there are alternate methods to talks to the database tables  (I am not a one table programmer).  When time permits, I will do some trials and if warranted, will try some of your techniques at some point.  Simply not enough time currently.

As I posted intially, I actually solved my problem before there were any replies and included my solution in the first post.  Why there is a syntax difference going from LV to an mdb is an unknown to me but it exists nonetheless.

Thanks for the input on this

[mikeporter]

I understand, but it is this kind of differences that the DCT causes.

Mike...