Accessing Microsoft Access Using LabVIEW

Microsoft Access is a common database toolkit provided by Microsoft. This KB provides the basic operations for interfacing with an Access via LabVIEW including creating and connecting a database and performing the Insert, Delete, Update and Select operations on records.

1. Preparing the Microsoft Access Environment

Make sure the development computer has both Microsoft Access and Microsoft Access database Engine installed - the necessary software can be found on Microsoft's site. Most computers do not have the database engine installed by default, which can result in difficulties when creating a UDL file. The option Microsoft Office 12.0 Access Database Engine OLE DB Provider may be missing, and users may be unable to access .accdb extension files.
Note:   User must install the 32 bit database engine or else error -2147467259 occurs.

 

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.  

As an example: 
Select TOP 30 * FROM mytable WHERE [myprice]> 1000 AND myname <> somename ORDER BY myid ASC, mydate DESC
This statement selects the first 30 records (represent by *) in the mytable table where myprice is greater than 1000 and the myname field is not equal to somename. They are sorted in ascending order in the myid field and descending order in the mydate field. 
Note: Words in database operation are not case-sensitive. For example, table and Table are the same table, and 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 the UDL in LabVIEW. Select LabVIEW>>Tool>>Create Data Link to create a UDL and configure database location. This method is recommended because the user does not have to manually create new files and change the suffix name.
• The second method is to create a new text file, change the suffix name to .udl, then double click to configure it.

 

4. Manipulating A 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. The 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, like if the ID 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.
 


More information on the use of SQL statements in Access can be found here.