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
[myprice]> 1000 AND
myname <> somename ORDER BY
, 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
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 ().
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)
PRICE = 1000 WHERE
id = > 5000Note:
Not all fields can be updated, like if the ID is defined as auto-increment and cannot be repeated nor changed
3 * FROM
USERS ORDER BY
, 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