Access Database Data Source 追蹤

0
Avatar
Legacy Poster

Hello everybody,

 

I have a question about a non-static Access Data Source.

 

We have a Label Template File (.BTW) setup to print from an Access Data Source with a query parameter and it is automated via VB.net application. We encounter what is called versioning with our data source files where the BTW template file is the same but the Access Data Source file changes in between jobs.

With Text files I could do BundleFormat.DatabaseConnections("TextFileName"),).Filename = "<file location.txt>" and then pass a different text file via variable to that database connection but the query results were too slow with a range of 30,000 - 500,000 record source files so we switched to Access data sources so we can index the query column.

 

Is there a way I can change which .accdb file is the data source for a BTW file from VB.NET?

 

I looked at the .NET sdk documentation/help from inside bartender and all I could find was how to submit different queries to a data source but it appears that just queries the data source that is already saved within the BTW file itself. I can add multiple data source files but I get errors about a join. I ignore those errors and then in the code I used

 

for each connection as DatabaseConnection in bundleformat.databaseconnections

MsgBox(String.Format("Connection '{0}' is of type '{1}'", connection.Name, connection.type))

Next connection

 

to get each of those data source names and types returned to me, but can I tell Bartender which one to use in order to print labels? Or is this just to join across multiple data sources to fill in label data?

 

If I can't get this to work, we would have to create a new copy of the BTW file for each version of the Data Source and save each one of them out with the different Data Sources set. which is a bit cumbersome.

 

Thanks for any information you can offer on this.

-Andrew

4 意見

0
Avatar
Legacy Poster
評論操作 永久連結

I think I may have found the solution to this if I can do it by creating an OLEdb connection and passing it to the bartender format with this: btFormat.DatabaseConnections.SetDatabaseConnection(<database file>)

 

similar to what is in the documentation below:

 

Public Sub Demo()
   ' Initialize a new BarTender print engine.
   Using btEngine As New Engine()
      ' Start the BarTender print engine.
      btEngine.Start()

      ' Create an OLEDB object to define a database connection.
      Dim oledb As New OLEDB("<database file>")

      ' Set the user ID and password for the connection.
      oledb.UserID = "Admin"
      oledb.SetPassword("newpassword")

      ' Initialize the format document
      ' (create a format with query prompts to use this example).
      Dim btFormat As LabelFormatDocument = btEngine.Documents.Open("C:\FormatWithQueryPrompts.btw")

      ' Set the database connection.
      btFormat.DatabaseConnections.SetDatabaseConnection(<database file>)

      ' Print the format.
      btFormat.Print()

      ' Close the current format without saving.
      btFormat.Close(SaveOptions.DoNotSaveChanges)

      ' Stop the BarTender print engine.
      btEngine.Stop()
   End Using
End Sub

 

 

I will post a reply if this works, I need to make a program split as we have a production copy currently because I went with the cumbersome workaround for now.

0
Avatar
Ian Cummings
版主
評論操作 永久連結

Sorry, but it is not possible to change the database source via automation, only to specify queries etc.  I believe you will need to go via the multiple BarTender documents route.

 

How did you get on with your possible solution?

0
Avatar
Legacy Poster
評論操作 永久連結

We ended up using multiple bartender files. it seems to not be too big of an issue/time sink to do it that way so it is working quite well. The only issue I have currently with the entire system is that sometimes the bartender query print job will all of a sudden take 6+ seconds to run compared to a consistent half second. It might be outside processes causing this issue, I haven't been able to put my finger on it.

0
Avatar
Legacy Poster
評論操作 永久連結

Due to issues querying the access data files from across the network we were getting the random slow prints. Since I can't 'programmatically' change the database source file location to a local folder after copying the .accdb file to a local temp folder I had to go a completely different route. I switched to using SQL, importing the access database files into SQL tables I then use an ODBC File DSN trick to make the bartender file look at the 'local' SQL instance instead of the network SQL instance the label was created from. when the data prep users create the label files they use a file DSN named ScanAndPrint that points to the Master SQL database on the network and an SSIS package replicates the data from the Master SQL database out to each of the production machines. Each one of those machines has the same DSN named ScanAndPrint except theirs points to the (local) database instead of the master. I have officially 'tricked' Bartender.

登入寫評論。