Using the calander function to select and read tables data from MS access?

[khsay87]

Hi,

I am being thrown into learning database in a short time. I am using Labview 9 to create a table, base on a the format ddmmyy hhmmss. I can write data into the table in access. The table will exist for 24 hours before a new table will be created. However, i find that this concept is not very gd as time goes by, I will have a lot of tables eg after 6 mths.

1.I would want to create a calander(similar to the system clock) in labview, whereby the user can click and select, which will then open and retreive the data. Inorder to do that i think i need to create some queries and relationship in the MS access right? can any1 advice? Pls give as much details as possible.

2.I can retrieve data from access and display it in labview. However, i would like to display the data and plot into the graph. Eg, display  coloum b, c  and plot them on the graph in an instance for analyst.

3. How do it generate a report to an email in an  document file regarding the daily data inserted. Can i do it in labview?

Any help would be deeply appreciated.  If you have sample codes and don't mind sharing, i would be very thankful.

Thanks a million 

[Dennis_Knutson]

Creating a table each time the date changes seems very silly. Normally, the date/time would be a single column in a single table and you would not need to create anything - just add a new row.

You can use a time stamp control for selecting a date/time. While the relationships would be created with Access, your query would be created in the LabVIEW code. Something like SELECT * from tablename WHERE columnname > '01-01-21010'.

Graphing data will depend on what the actual data is. You would need to convert the query results to numerics. I use LabSQL that returns the query results as a 2D string array. Not sure what the LabVIEW toolkit does.

LabVIEW has built-in email functions. You can send attachments if that is what you are asking.

The database toolkit has examples as does the email functions. I would strongly recomend that you search the net for a SQL tutorial.

[khsay87]

hi dennis,

I still dun understand the following

>>>your query would be created in the LabVIEW code. Something like SELECT * from tablename WHERE columnname > '01-01-21010'.

Are you referring to changing the relationship in the access file. After which then try to use labview to view the quequires? Can i set the relationship in labview to access?

[Dennis_Knutson]

Have you tried to find a SQL tutorial? Do you have any experience at all with databases?

No, of course I am not referring to changing any relationship. I'm talking about executing a query on a database. You asked how to read a table. That is called a query and what I posted is an example of a query based on a date/time which is something you also asked about. LabVIEW would be used to construct the query and view the results.Don't confuse Access with the actual jet database. And don't confuse queries that you would construct in Access with what you use in LabVIEW. You could use Access to design and test a query and then show the SQL code. The SQL code would then be pasted into LabVIEW.

The relationships between the different tables in a database would be defined at design time. You can create a table and define the relationships with SQL commands or you would use the built-in tools of Access. Typically, the creation of tables in a database is a one time thing so you would use whatever tool you are most comfortable with. Unless you are very familiar with SQL, the design tool in Access is going to be easier.

[khsay87]

Hi sorry... i am really new to databases. Thk for the info. I found this site which shows how to write queries. I tried running the program but it doesnt work. It is a project file. I tried linking the udl file provided in the zip file and it shows the connection error. Somehow it cannot recognise the path file to the udl file. http://decibel.ni.com/content/docs/DOC-4932 I am studying the example program to see how it work before i make changes to my current program. Pls advice. thks in advance

[Dennis_Knutson]

Did you edit the udl file to have the current path to the mdb file on your pc? Just double click on the udl file in windows explorer.

[khsay87]

Hi, thks. I edited the Database1.udl which comes along with the zip folder. The file is run in a proj consisting of serveral subVI. When i change the file directory in the main program. It shows me a conncection error which show that it cannot connect to the udl file. Can you try and advice?

I am suspecting the prgm itself had some bugs.

[Dennis_Knutson]

No, I was able to run the example without any problems. I changed the udl to point to the mdb file and then in the VI browsed to point to the udl file location.

[khsay87]

Thk i found it. However i  still cant link it to another access file even i declared the coloum and  accordinly... still looking into it... thks for ur help....

[khsay87]

Hi Dennis,

I am very thankful for the help you have render for the past few post. I have indeed learn alot using labview writing to MS access. Previously, I change the udl file properties and it works. However, I would still admit that the example given is not frienly and beginner to head start.Therefore, i still have some diffculties trying to learn the concepts used.

Pls correct me. This program uses the labview to write SQL commands to MS access and do a 'search' on the data in the table based on the time frame using the calander. It will not create and produce a query list in MS access. I had trouble doing a query search on my own access files and the VI does not read the file. Further changes are made to the subVI so that i can add more parameter and coloums. Not really sure if there is a better example out there t for learning!