Convert an Access Field in a Table to Barcodes

The following example MS Access function demonstrates how to fill up a picture field (an OLE object field) in an MS Access database table with barcodes.

The function reads through the entire table and takes the source text for each barcode from a text field named “BarCodeText” and passes it to B-Coder. Next, it generates the barcode and saves the resulting barcode to disk as a .wmf (Windows MetaFile). Finally it inserts the image into the picture field named “BarCodePicture” in the same record as each “Barcode.txt” field.

The function below assumes that a table named “table1” is available that has at least two fields, one being a text field named “BarCodeText” (containing our source barcode text data) and another empty OLE object field named “BarCodePicture” (the destination for all barcode graphics).

After you get all your barcodes into your Access table as picture objects, you can print them in reports or however you want. Since this procedure also places the barcodes into the table as Windows MetaFiles instead of as bitmaps so you will get a much higher resolution printout and each graphic will use much less space in your database.

 Function BarCodes()
  Dim Total As Long, Chan As Long, I As Long
  Dim BCDirectory As String, MyTable As String
  Dim MyBarCodeTextField As String, MyBarCodePictureField As String
  
  ' Directory where B-Coder lives
  BCDirectory = "C:\B-Coder\" 
  
  ' Name of Access table containing bar code data
  MyTable = "table1" 
  
  ' Field name containing the text for all bar codes
  MyBarCodeTextField = "BarCodeText" 
  
  ' Name of the OLE (Picture) object field
  ' where bar codes are to be inserted
  MyBarCodePictureField = "BarCodePicture" 
  
  On Error Resume Next 'set error trap
  
  ' Establish link.
  Chan = DDEInitiate("B-Coder", "System") 
  
  If Err Then ' If error occurs, B-Coder isn't running.
    Err = 0 ' Reset the error
	' launch B-Coder.
    I = Shell(BCDirectory + "B-Coder.exe", 7) 
    
	' If another error occurs then exit.
	If Err Then 
    Exit Function 
    ' otherwise Establish link and continue.
    Chan = DDEInitiate("B-Coder", "System")
End If

  ' Turn off B-Coder warnings
  DDEExecute Chan, "[MESSAGEWARNINGS=OFF]" 
  DDEExecute Chan, "[PRINTWARNINGS=OFF]"
  DoCmd.OpenTable MyTable, A_NORMAL ' Select our Access table
  ' Note: The syntax for all DoCmd statements in this function 
  ' is correct for Access 97/2000.
  ' If you are using Access 2.0, change the period after all DoCmd statements
  ' to a single space character. i.e.
  ' DoCmd OpenTable MyTable, A_NORMAL  Total = DCount("*", MyTable) 
  
  ' Go to the first record in the table
  DoCmd.GoToRecord A_Table, MyTable, A_FIRST 
  
  For x = 1 To Total ' Loop through each record
    ' Select message text field
    DoCmd.GoToControl MyBarCodeTextField 
    DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_COPY, , A_MENU_VER20
    
	' Copy to clipboard, Pass it to B-Coder & build a bar code
    DDEExecute Chan, "[Paste/Build/Copy]" 
	
	' Select the bar code picture field
    DoCmd.GoToControl MyBarCodePictureField 
    DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_PASTE, , A_MENU_VER20
    
	' Paste in the bar code image
    DoCmd.GoToRecord A_Table, MyTable, A_NEXT ' Goto next record
  Next ' Loop through all records
  
  DDETerminate Chan ' Terminate the DDE link - all done!
End Function