How Can I Disable/enable Bartenders Connected Database Using Vbscript

Legacy Poster

• July 27, 2015 09:18

i need to temporarily disable and then reenable the database that is connected to a label format.

 

the database will be either excel, csv, text

 

i only need help in disabling/re-enabling the database, the vbscript to manipulate the data is not a problem for me.

 

i need to do this so that i can manipulate the database with VBScript probably in the postprint event, for example user will select the record as normal during prompting, user may alter some of the data in the prompts linked to the database fields, then when the postprint is activated the vbscript will disable/close the linked database connection, open the external database using vbscript (same database as the linked database), write the updated data back, close the vb connected database, then re-enable the linked database connection

 

i am thinking that it may be possible to do this via bartenders objects, such as Application or Format, but i can't find anything about the database object i.e. its object name, if it has one and if it has been published to allow access

 

any help appreciated

0

• 

  Ian Cummings

  Moderator

  ★ BarTender Hero ★

  • July 27, 2015 11:52

  It is not possible to disable the database connection via VB script inside a BarTender document.  Perhaps the below example might help point the way...

   

  BarTender, in terms of its database connectivity, is a read-only application.  This means that in order to write back a value to a printed database record we will need to use BarTender's built-in VB Scripting capability.  Attached you will find a simple example which will do just that.  In the Excel file there are two fields, one for a product and the other which flags when the product's record has been printed.

   

  In the BarTender label format I have made a connection to the Excel file, which assumes it will be found in the "C:\Seagull" folder.  It uses a query to only find and therefore possibly print those products (records) that do not have the word "YES" in the "Printed" column.  The select record at print time option is turned on allowing the user to select which of the records that have not been printed, are to be printed.

   

  During the print job the VB script contained in the object in red placed off the label, runs a section of code that connects to the Excel file, finds the record being printed and sets the "Printed" field to the value "YES" so that next time the user prints it will not be available in the select at print time dialog to choose.  The VB script is as follows.

   

  OnNewRecord event

   

  'Define the object names.

  dim objConn

  dim strConn

  dim rs

   

  'Define the database connection parameter constants.

  Const adOpenStatic = 3

  Const adLockOptimistic = 3

  Const adCmdText = &H0001

   

  'Connect to the Excel file.

  Set objConn = CreateObject("ADODB.Connection")

  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Fruits.xls';Extended Properties=""Excel 8.0;HDR=YES;"""

   

  'Create a record set of one record which is the current record being printed.

  Set rs = CreateObject("ADODB.Recordset")

  rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText

  rs.Find "Product = '" & Field("Sheet1$.Product") & "'"

  'Set the "Printed" field of the record to be "YES" thus flagging it as having been printed.

  rs.Fields("Printed") = "YES"

  rs.Update

   

   

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • July 29, 2015 01:28

  _{thanks Ian, this looks like what I was after}

  _{I have had a play with it this morning and with a few mods and adding a prompt for the product and a little change to the SQL I am able to update the product data with the value entered into the prompt.}

  _{this shows me that I can update the record via the prompt screen}

  _{I still need to add a new record, if the product name is not already one in the database, but but I think this should be possible and you have pointed me in the right direction}

   

  _{thanks again} 

  0

Please sign in to leave a comment.