Write data to Microsoft Access. Tutorial.

Problem scenario:

I am taking a weight from a scale ever couple of seconds and I want the software to take that weight via the serial port and load it directly into a database with a date and time stamp on each entry. The database is located on a network drive and it will not be open at the time, unless the software has the ability to open Access.

Requirements:

  • Advanced Serial Data Logger Professional, Enterprise or Trial version
  • ASCII Data Query and Parser, DDE Server, ODBC Database

It is assumed that:

You've prepared parser items for export.

For this tutorial all items were prepared in the previous part.

Also you may read other examples:

Solution:

We are now ready for configuring of the ODBC database plug-in, that will export data to Access. This configuration process is not complex and might not be difficult for the unskilled user.

It is assumed, that you have access to the database and can manage it. First of all, create a table in a database.

Next, select the "ODBC database" plug-in from a list and open the configuration window of the ODBC database plug-in by clicking the "Setup" button below the list. A dialog window will appear on the screen (Fig.1).

ODBC Database Plugin

Export to MS Access. ODBC database. Enabling connection.

Fig.1. Export to MS Access. ODBC database. Enabling connection.

The radio button at pos. #1 enables writing to the database and the tick-box at pos. #2 specifies that the database is to be kept open. This serves to minimize the writing time, at the same time locking out other users from writing.

On the second page "Connection" (Fig.2) you can specify the parameters of your database. Here select your ODBC alias name for a Microsoft Access database (pos. #1). Note: If you have configured an ODBC alias after opening the configuration window, please click the "Refresh" button.

Export to MS Access. ODBC database data logger. Connection options

Fig.2. Export to MS Access. ODBC database data logger. Connection options.

You may configure the connection with your database like that:

  1. Create a fully configured ODBC alias (by clicking the "Setup" button at the position #3 on Fig.2);
  2. Create an ODBC alias and specify a database type (Microsoft Access) for this alias only and specify all other connection attributes in the module (pos. #2 on Fig.2).

In this example, we've selected second method, because this is most quick and easy and no additional attributes are specified, except the database file.

Because an Access database is placed in a file, then you should select this file in the corresponding field (Fig.2, pos.2). You should specify the full path and name here.

A few words about a database. You should create a database and a table in the database yourself with help of Microsoft Access. If you don't have Microsoft Access on your PC or didn't create Access databases before, then you can download a database sample here.

We've created a database with the "database.sample.mdb" name for this tutorial. The database contains a table with the "SERIAL_DATA" name. The table contains following fields:

  1. FVALUE (numeric);
  2. FDATE (date);
  3. FTIME (time).

For items #2 and #3 we had assigned functions Date() and Time() as default value, and we had got a date time stamp for an each new record.

On the third page "Errors handling" of "ODBC database" plug-in (Fig.3), specify how the software should react to errors which occur while writing to a database.

Export to MS Access. ODBC database data logger. Error handling

Fig.3. Export to MS Access. ODBC database data logger. Error handling.

In this example, the last option (pos. #1) was checked as the data is important and we require the program to operate regardless of the error. The other variants are normally used when testing the plug-in module with a new database.

The last page "Binding" (Fig.4) is very important. On this page the table name and the fields that you require to be bound to the variables names are specified.

Export to MS Access. ODBC database data logger. Binding

Fig.4. Export to MS Access. ODBC database data logger. Binding.

Because we've created the table with a date time stamp, then we should bind one colun only: the "FVALUE" column in the table with the "VALUE" variable.

You can create item by item on this page by pressing the "Add item" button (Fig.4, pos.#2). In this case, before adding an item, the program will ask you for the table name (Fig.4, pos.#1) and then the column name. The name of a table column should be same as that specified while designing the database. Adding each item separately is a tedious and long process. It is simpler to add all items at once by clicking the "Import" button (Fig.4, pos.#3).

However, before clicking the "Import" you should configure a database connection as described above. In this case a dialog window will appear (Fig.5 and you should select the necessary table name (Fig.5, pos.#1) and click the "OK" button.

Export to MS Access. ODBC database data logger. Selecting the table

Fig.5. Export to MS Access. ODBC database data logger. Selecting the table.

Each item on the binding page has a number of properties:

  1. Parser item name - is a parser variable name, which has been created in the parser configuration. Select the variable name from a drop-down list, which appears while clicking on a link for this property;
  2. Column data type - is a database column data type. The module will try to convert a variable's data type to a column data type, using standard system functions. If your variable has a data type other than "string", specify the variable data type here;
  3. Default value - this value is to be used when data does not contain a value for a column or the variable is empty.

Click the "OK" button to close the ODBC database plug-in configuration window as well as the "OK" button in the options window.

Okay, all settings have been completed and we are ready to capture weight data from scales to the database.

If you configured the ODBC module correctly, then while accessing the database the data logger will display messages in the drop-down box, at the bottom of the main window.