LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

calling module (Function) in database toolkit

Solved!
Go to solution

Hi, I have been using the database toolkit a lot but I have never used a function from my Access database before.

I have a function called "Work_Days" that takes two dates as Inparameters and calculate how many working days that is. This funtion is stored in the Modules section in Access.

I have a working query that I can run within Access that looks like this:

SELECT tblActiveCCU.ID, tblActiveCCU.DateReceived, tblActiveCCU.DateTransferred, Work_Days([DateReceived],[DateTransferred]) AS NetDays
FROM tblActiveCCU
WHERE (((tblActiveCCU.ID)=97));

 

The output looks something like this:

ID	DateReceived	DateTransferred	NetDays
97	3/16/2016	3/30/2016	11

Now to the question, how do I implement this query in LabView?

 

In my attached code I got a "Syntax error in FROM clause"

0 Kudos
Message 1 of 17
(3,525 Views)

Can you share the database file and down convert the VI to 2012?  Smiley Frustrated

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 2 of 17
(3,500 Views)

OK, here it is...

Download All
0 Kudos
Message 3 of 17
(3,493 Views)

Rather than create a duplicate query in Labview, just call the query within the database from Labview.  It works like a stored procedure in SQL.  See attached.  

 

I added a parameter which can be referenced in the database/query to allow Labview to choose the ID.  In design view for the query, there should be a parameters button where you can add a prompt for value.  Once you have this created, the criteria for the ID field will be "=[param_name]".

 

I am not sure about the function call.  It doesn't work for me either.  Says that it's an undefined function.  A quick search says that this might be fixed in Visual Basic runtime 6.0.  

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 4 of 17
(3,482 Views)

Aputman,

sending the query name seems to be a good solution, however I can't get it to work.I have an error  regarding the SQL statement.

0 Kudos
Message 5 of 17
(3,474 Views)

Using the attached files, you should get an array of values back with an ID of what you type in the control.  You'll need to make sure the connection information is setup properly because it is coded for my PC at the moment.  

 

I had to delete the column with the custom function Work_Days from the query because of what I mentioned previously.  If you update to VB runtime 6.0, that might fix this issue.

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 6 of 17
(3,446 Views)

I don't get an array of values back - I still get the same error (see earlier post - error.png). Smiley Frustrated

0 Kudos
Message 7 of 17
(3,436 Views)

I'm not sure what to tell you.  I found this reference to the error message but I don't see anything in your example that would cause this.  It works on my machine.

 

http://digital.ni.com/public.nsf/allkb/22A45DF5C85AB3F286256CC30073C5AC

 

Try removing the insert data statement and see if you can query data back.  

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 8 of 17
(3,428 Views)

Actually, it looks like Description is a reserve word.  Try changing that field name to something else.

https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9...

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 9 of 17
(3,420 Views)

I changed the reserved word (Description) - I still get the error, I have used this in the previous code I am using so maybe it is OK because I have Access 2010.

I can read back the parameters out (test, Long (I32), Input, 3). The error comes in the EXE VI.

If I put a value in NetDays this values is still written into the database in the NetDays column though.

So you haven't changed anything in the column string array?

What do you mean by: "Try removing the insert data statement and see if you can query data back."

I removed the whole Insert data VI and the surrounding strings, the error is still the same.

0 Kudos
Message 10 of 17
(3,410 Views)