Using Bar Tender with Microsoft Access
Home > Barcode Basics > Application Notes > AppNote006

MS Access is a powerful and flexible database system that is a standard component of the Microsoft Office Professional Edition. It is an excellent tool for organizing and manipulating information, and ease of use makes it possible to build small systems very quickly. It is especially easy to create on-screen forms that can be used to automate routine tasks.

When MS Access and Bar Tender are used in combination, they provide a powerful set of tools for automating the printing of labels. MS Access is excellent for organizing information, creating data entry screens, and automating the whole process. Bar Tender has a number of important advantages when it comes to designing and printing labels.

• The label design process is easier and more precise
• Label designs can be modified without exposing MS Access components to risk of inadvertent changes
• Barcodes are precisely rendered for reliable scanning
• All standard barcodes (including 2D) are supported
• Barcode checksum calculation and formatting are automatic
• High-performance thermal-transfer label printers are supported

It is very easy for an MS Access application to use Bar Tender as a "print slave" to generate labels. Here are the steps required:

Create a database to hold the temporary print data

Create a new database that will hold the temporary print data that will be passed from your MS Access application to Bar Tender. For example, create a database named PRINTDAT.MDB. This database will contain one table named TempPrintData which will hold all of the variable information needed for the labels. This table MUST be in a database separate from your main application; the reason is that MS Access and Bar Tender cannot share a database simultaneously.

Your MS Access application will need to fill in this table automatically during routine operations. The most convenient way to do this is with a Make Table Query, which can gather information and create the TempPrintData table in a single step. When you design the Make Table Query you can specify the name of the database (PRINTDAT.MDB) and the table (TempPrintData); if an old copy of TempPrintData exists, it will be overwritten automatically.

Create your Make Table Query (or use another method if you prefer) and run it to generate TempPrintData with at least one data record; you will need this table when you design the label. It is a good idea to include every piece of information that you think you will need on any of your labels. For example, even though a batch code might be needed on only one type of label, include it in TempPrintData. When designing a label, you can pick and choose the information you want to use.

Design the label(s)

Open a new label in Bar Tender and select Input Data File Setup from the File menu. For type of file, select Named ODBC Data Source; click on the Select button and choose MS Access Database. Then you will be able to browse and find your PRINTDAT.MDB database. Select TempPrintData at the Table field, and set Record Selection to All.

On the label design view, add text, barcodes, and graphics as needed. When setting the data source for each field, you will be able to select any of the fields from TempPrintData.

For the quantity of labels to print, the default setting is one label per data record. If your TempPrintData table will include a field which specifies the number of duplicate labels needed for each record, go to the File menu and select Print. You will see that the Identical Copies of Label field is set to 1; click on the Options button to the right of the field and choose the radio button labeled Set by Data Source. Then click on the Data Source property page tab, select Input File Data as the source, and select the specific field which contains the number of duplicates needed. You can then close the Print Dialog window without actually printing; your new settings will be saved.

Set up your application to run Bar Tender automatically

At some place in your MS Access application there will be a button labeled "Print Labels." This button should be attached to a Basic subroutine that controls the entire printing process. Here is a sample of what this subroutine might look like. The SetWarnings False command suppresses warnings to the operator that we are about to create a table and write information to it; there is no need to pester the operator. We are using a query named "CollectPrintData" to gather information and create the TempPrintData table; remember that your query can also pull in information from operator entry fields on a screen form. We are then using the Shell command to run Bar Tender. You can copy the pathname for the Bar Tender program from the Windows shortcut that was created when you installed the program. The /F option specifies that we will be printing the label Carton.btw which we have saved in the C:\Labels directory. The command line also includes options to print immediately (/P) and to exit when done (/X).

Sub PrintLabels_OnClick()
Dim RetVal as Variant

DoCmd.SetWarnings False
DoCmd.OpenQuery "CollectPrintData"
RetVal = Shell("C:\ProgramFiles\bartend\bartend.exe /F=C:\Labels\Carton.btw /P /X")
DoCmd.SetWarnings True
End Sub

When the operator clicks the Print Labels button, the MS Access application gathers information, writes it to TempPrintData, and runs Bar Tender. Bar Tender retrieves the label design file (carton.btw), opens the TempPrintData table table (as specified in the label design), and prints the labels using the variable information that it finds there. When it is done printing, Bar Tender exits leaving the operator back at the MS Access screen form.

Now an important technical note: we simplified the example above by pretending that the pathname for the Bar Tender program did not include any space characters. In reality, the "C:\Program Files" folder does include a space, and unless we put the pathname in quotation marks we will get a "file not found" error when we try to run this bit of code. The ASCII value of the quotation character is 34, so we can insert it into our string using the Chr(34) function. This is what the Shell() function call should look like:

RetVal = Shell(Chr(34) & "C:\ProgramFiles\bartend\bartend.exe" & Chr(34) & " /F=C:\Labels\Carton.btw /P /X")

The demonstration version of Bar Tender includes complete on-line help; look under Command Line for details about the command line options. The only difference between the demonstration and real versions of Bar Tender is that the demo substitutes one data character in every barcode or data field that it prints. This means that you can try using Bar Tender and MS Access together with just the demonstration version.

Need more information about Bar Tender? Click here!