Write data to Microsoft Access with help of "ODBC Database" plug-in
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.
- Advanced Serial Data Logger (ASDL) Professional or a trial version;
- ASCII data parser and query plug-in for Advanced Serial Data Logger;
- DDE server plug-in for the data logger (optional);
- ODBC database plug-in for Advanced Serial Data Logger.
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:
- Parsing data from barcode scanner and writing to a log file with a date/time stamp (data at fixed positions, no data packet signatures);
- Processing data from barcode scanner (data at fixed positions, data packets with the end signature);
- Parsing data with help of regular expressions (complex data format, regular expressions, data filter);
- Receiving and parsing data from aircraft engine monitor (usage example, custom data parser);
- Processing data from the lab equipment (simple data format, regular expressions);
- Receiving and parsing data from Garmin Geko301 GPS receiver (simple data format, data at fixed positions, data packets with both signatures);
- Parsing data from the filling machine (simple data format, delimited data, data packets with the end signature, data filter);
- Parsing and processing data from PBX (simple data format, data at fixed positions, data packets with the end signature, data filter);
- Logging and parsing data from an intellectual pressure measuring device (sending requests to the device, simple data format, data at fixed positions, data packets with the end signature, data filter);
- Serial data acquisition in the Checkweigher Management Information System (complex data format, regular expressions, filtering data);
- Serial port data aggregation. Combine a data of two different com-ports (simple data format, data at fixed positions, data packets with the end signature, data filter);
- Weight data logger. Parsing weight data from scales (simple data format, data at fixed positions, data packets with the end signature, data filter).
- Send data to a port from a file (data requests, send data by a schedule).
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).
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 which you created above at pos. #1. Note that if you configured an ODBC alias after opening the configuration window, please click the "Refresh" button.
Fig.2. Export to MS Access. ODBC database data logger. Connection options.
You may configure the connection with your database like that:
- Create a fully configured ODBC alias (by clicking the "Setup"
button at the position #3 on Fig.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
- FVALUE (numeric);
- FDATE (date);
- 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.
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.
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.#6). 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 (Fig.4, pos.#2). 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.#7).
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.
Fig.5. Export to MS Access. ODBC database data logger. Selecting the table.
Each item on the binding page has a number of properties:
- 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;
- 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;
- 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.