Monday, May 4, 2009

Scan & Store: Scan Barcodes directly into any Database

Are you looking for a basic inventory solution or you are just interested in registering items based on their barcode?

The data acquisition software TWedge provides an elegant solution - TWedge captures data from arbitrary devices (like a serial or USB barcode scanner) and can be configured to store captured data directly into any ODBC database.

Additional database software is NOT required! 

Smart Software Wedge

Configuration of TWedge is extremely flexible: The data acquisition functionality can be configured to meet your exact requirements. TWedge is able to perform read/write access to files, databases and may also simulate keystrokes.

To implement the required functionality you need:

  • A serial (or USB) barcode scanner. The used COM port as well as the interface parameters are important (most likely it will be 9600,N,8,1 - meaning 9600 bits per second, no parity, 8 data bits, 1 stop bit)
  • TWedge (download)

Determine Bluetooth COM Port

For this example I use a Bluetooth bar code scanner (Metrologic Voyager). The scanner provides scanned data via COM3. This serial interface was adjusted manually in the services dialog of the available Bluetooth Devices on Microsoft Windows:

Configure TWedge Interface

Now start TWedge and configure the interface parameters. Adjust COM3 as displayed in the Bluetooth dialog above:

Test Scanner Communication

The next step is to test the interface configuration: just start communication in TWedge. Whenever you scan a barcode the number should appear in the protocol window:

Provide a Database

Now its time to connect the barcode scanner with a database. For demonstration purposes we use an empty database which was pre-installed by the setup application of TWedge:  A so-called System DSN which is named TWedge_Sample.

On 32 bit Windows you can check out the installed DSN by opening the Data Sources (ODBC) administration (Windows Control Panel - Administrative Tools - Set up data sources (ODBC)). If you are running Vista x64 then execute the command c:\windows\SysWOW64\odbcad32.exe to view this dialog:

On Vista systems the database is usually stored in the folder C:\ProgramData\TEC-IT\TWedge\2.1\Examples\Database. When you open this database with Microsoft Access you see two tables. The example below stores captured data in the initial empty table tbl_Scans:

Configure Software Wedge

In order to configure TWedge open the script editor and select the predefined template which is named Database - Write. Double click it and close the script editor. This script reads data from the configured interface, determines the current date and time and enters the scanned data in the database mentioned above.

Data Acquisition

Start device communication again and scan some barcodes:

Finished! Barcodes are stored in Database

That's all! TWedge connects to the barcode scanner via the configured COM3. It waits for scans - and whenever a barcode was scanned it enters the barcode number into the database. And this is the result– the scanned barcodes are stored with some additional information like date/time and hostname: