Accessing Microsoft Access Using LabVIEW

Updated Aug 15, 2019

Reported In

Software

  • LabVIEW
  • LabVIEW Database Connectivity Toolkit

Issue Details

Access is a common database toolkit provided by Microsoft. This KB provides the basic operations including creating and connecting database, Insert, Delete, Update and Select records through LabVIEW, providing a better understanding to new users. 

Solution

1. Preparing the Microsoft Access Environment

Make sure the development computer has both Microsoft Access and Microsoft Access database Engine installed. User may download the related software from here. Most computer does not have the database engine installed by default, which may results in difficulties when creating UDL file. The option Microsoft Office 12.0 Access Database Engine OLE DB Provider may be missing and user unable to access .accdb extension files.
Note:   User must install 32 bits database engine or else error -2147467259 occurs, please refer to this KB . ​​​​​​

 

2. Basic concepts of database and terminology explanation

The database organizes and stores data in a hierarchical structure. An Access database file contains several tables, which are collections of a type of data. Each table has several fields (columns) representing a certain type of attributes such as ID, name and contact information. Each row is a record, which represents a unit, maybe a person or a piece of goods.  
Structured Query Language (SQL) is the standard language for manipulating databases. It includes four parts: data definition, data operation, data control, and transaction control. The first two categories are commonly used, such as creating a new table, query sorting, adding or deleting records.  

For an example: 
Select TOP 30 * FROM mytable WHERE [myprice]> 1000 AND myname <> somename ORDER BY myid ASC, mydate DESC
This statement selects first 30 records (represent by *) in the mytable table which myprice is greater than 1000 and the myname field is not equal to somename then they are sorted in ascending order in the myid field and descending order in the mydate field. 
Note: Words in database operation is not case-sensitive. For example, table and Table are the same table, id and ID represent the same field. The same result can be obtained by writing the SQL statement in lowercase, but for the sake of clarity and legibility, the SQL keyword usually written in capital letters.
   

3. Establish a UDL database link

The UDL file is the hub to connect LabVIEW and database. There are two ways to create a new UDL file.
  • The first method is to create a new text file, change the suffix name to .udl then double click to configure it. Please refer to this link.
  • The second method is to create UDL in LabVIEW. Select LabVIEW>>Tool>>Create Data Link to create UDL and configure database location. This method is recommended because user does not have to manually create new files and change the suffix name. Please refer to this link
 

4. Manipulating database in LabVIEW

LabVIEW's own database advanced operations are an encapsulation of SQL query, the core is to use SQL language to operate the database. Figure below shows direct execution of the SQL statement, it avoids the disadvantages of advanced operation being unable to sort display and difficulty in executing a complex nested statement. It also enables convenient and quick use of various functions of SQL. 


An example vi of direct execution of SQL in LabVIEW. 


Note: Fields have different types, such as numbers, strings, time and place. There are also different identifiers in the SQL statement:
  • The operation of the numeric type (whether integer or floating point) and the price data type, user can directly enter the number, such as id= 100
  • Strings need to be enclosed in single quotes, such as username= 'John'
  • Time needs to be enclosed with ##, such as #2010/1/1#. User can also use the type conversion function provided by Access CDate()
  • Some fields contain spaces need to be enclosed with [], otherwise, it will not be recognized properly, such as [saved level] = 10

1. Insert

INSERT INTO USERS (id, username, password) VALUES (123, 'John', '123fasdf')
User can add value to the field without the need to assign data type every time. 
Note: The field username is the same as the content type and should be enclosed in ().

2. Delete

DELETE FROM USERS WHERE time < #2010/1/1#
The function of WHERE: Indicates a qualification condition, such as greater than (>), less than (<), not equal to (<>), BETWEEN Value 1 AND Value 2, the keyword LIKE includes multiple possible values IN (value1, value2,...,valueN)

3. Update

UPDATE USERS SET PRICE = 1000 WHERE id = > 5000
Note: Not all fields can be updated. For example, the id in this example is defined as auto-increment and cannot be repeated nor changed. 

4. Select

SELECT top 3 * FROM USERS ORDER BY Price ASC, id DESC
In the statement above, ORDER BY operation is performed. Sorting can perform multiple operations with keywords ASC (ascending) and DESC (descending). The operation in the statement above sort Price in ascending order. For Price, the id numbers are sorted in descending order, as shown in figure below.
 


This link explains the use of SQL statement in Access.