Select specific fields from Access table per Label
Hello, I am new to Bartender and tasked with managing our manual label printing system.
One complaint I hear from users is, most times, when they open a label they have to remove the datasource (Which is an Access database) and add it back in. They typically have to do that this everyday, for almost all the labels.
However, I notice when re-establishing the database, there is a SQL statement generated automatically (on the SQL tab) and it chooses a field from the table that is specific to the label. Where is this setup to know which fields to select?
I have looked in vbscript and see nothing in any of the labels.
Thank you for your help!
"One complaint I hear from users is, most times, when they open a label they have to remove the datasource (Which is an Access database) and add it back in. They typically have to do that this everyday, for almost all the labels." - that sounds very odd. Is the name of the database changing or structure etc as you shouldn't need to do this?
When a database is linked to the label all the fields will be available you just need to select/link them from within the individual data fields on the label. Those fields will then appear in the SQL tab along with any filters/database queries you have created ( Where statements rather than Select)0
The name of the database does not change, occasionally a new field may be added to the structure but I notice the reconnect will happen everytime I open a label, even if its close/reopen. It is odd.
When I copy/pasted the labels on a different server I am not having the same issue of having to reconnect. My goal is to automate the labels from our ERP, so I have added a .txt datasource to work with the Access database on the other server.
Also, when removing/adding the datasource, I go to the Sort tab to choose the field needed for sorting. I go to the SQL tab and that has a select statement that selects only a few specific fields. This is important because we use one database that have fields for many customers, so the specific SQL statement will choose all the same fields plus one specific to the customer. I do have 1 label that works and shows their specific customer field in the SQL tab but a few of the others don't have same result.
I know confusing, I guess what I would like to know is there a place in the database setup or the label setup (generally speaking) that we can specify which fields to show in the 'Select Records' when manually generating labels? How does this SQL statement get generated?
Thank you again for your help!0
I take it you are selecting the record to print from the dropdown list rather than a filter/query. To select which fields to show in this list the easiest way is go to Print Preview and when the table appears then right click on the table and remove the columns you do not want to show. After you have previeed the label then save it and that should now just those columns in future.
The SQL statement is generated automatically unless you stick the Custom box and amending it yourself. If you link a new database field to a field on the label you will see item will be add to the Select list,
In the current manual process, the user chooses 'Print', 'Select Records' - a grid filled screen comes up showing the select fields (from SQL tab) - the user chooses how many labels for each part she needs. I can click 'Show All Fields' at the top but then I will get every field. I only want this labels customer specific field. Some of the labels are fine; others are not. I just don't know where the SQL statement is coming from.
I tried the Print Preview but I am not seeing any table appear?0
The table I meant is the grid you refer to. Which version of BarTender are you using as this may not be available in older releases?
Rather than using the select from the grid/table would it be simpler to add a query/filter and have the users enter a value to perform a search for the customer name for example? You can even use "begins with" or "contains" as the operator so they just need to enter part of the name and if you leave the "Selected Records" on the Print screen as Selected at Print Time you will then get a smaller grid appearing that just shows the matching records.0
Yes, I can probably do that. I just dont understand why the labels are losing connection to the database and how it specifies the select statement when adding the db back in?0
The select statement, I believe, it populated dependent on the fields on the label itself and which fields in the database they are linked to.
Not sure why the database link would be dropping out. As a test, you could remove the Access database and add it back in as a generic ODBC link as you can specify a user name and password to use (from the Advanced button) to read from the file in case there is some security/user rights issues causing the dropout.0
I thought that too, about the select statement. But the label I was looking at had the customer specific field on it but that field was not in the select statement.
Its a mystery! :)
I am in process of automating the labels to print from our ERP; so this is a temporary issue, I hope. I just hope we don't run across the lost connection will it is in an automated phase. But we will proceed slowly, handful labels automated at a time!
Thanks so much for your time and help. Much appreciated.0