Calling Access Queries that contain Parameters from the LabVIEW Database Toolkit

 

Every so often I need to accomplish a task where I think “gee, that will be easy” and every so often I am dead wrong. Recently, I had such a task where I needed to call queries saved in an Access database using the LabVIEW Database Toolkit. I know, I should never use Access but this project involves legacy code that depends heavily on Access databases and there’s no time or budget to rewrite all of the code. So, in the LabVIEW extensions to the legacy code I needed to call what were identified in the legacy code as “stored procedures”. In truth, these were not stored procedures and were simply parameterized SQL queries saved in an Access database.

First I went to the NI support and community pages. I found some interesting links about calling stored procedures and tried implementing one of those approaches (http://digital.ni.com/public.nsf/allkb/07FD130746083E0686257300006326C4). No dice. Since I’m not a database expert I turned to Google and found that I was not really calling a stored procedure at all and instead I was trying to accomplish a different command. But what command? And with what syntax?

The best lead I found was this link – https://support.microsoft.com/en-us/kb/200190 – “How To Call a Parameterized Query to an Access Database with ADO” OK –I’m on the right track – I’m calling an Access query that contains parameters.  So I implemented this piece of code in LabVIEW using direct COM calls to the Microsoft Active Data Objects COM library. Once this code was executing properly, I examined the contents of the ADODB (Active Data Objects Data Base) command object created. I then examined the command object created by the Database Toolkit and tweaked the arguments to the parameterized query VIs and determined what the input should be to get the desired output.

VI Snippet

So, the short story is that one simply prepends the “exec” keyword to the name of the Access query, builds the parameter list, defines the arguments to the parameters, and executes the query. Do not add (?) for arguments in the query string. Do not set the “stored procedure” parameter to true on the DB Tools Create Parameterized Query VI. See the code snippet for details.

I know this example is very specific and may not be of interest except to those poor souls condemned to use Access, but I could not find a working example on the internet so I think this may prove useful to someone.

Mark