Can I Use Two Databases Independently Without Joining? Follow

0
Avatar
Legacy Poster

Is there a way I can use a view from access and an excel spreadsheet in a label without joining them (I cant as there is no common data).

 

I can connect to the access database no problem but what I want to do is run a query prompt which looks up data in the other excel database and returns the value in a QR code.

 

I have them running independently, the access one adds in different field data in different positions on the label template and the other picks up an rfid tag in a query prompt as a keyboard entry and looks up the persons name in the excel spreadsheet and adds it to a QR code.

 

What I need to do is combine the two to produce my desired label.

 

Hope I have explained clear enough.

 

2 comments

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

Unfortunately, currently we only support related tables/databases in the main database connection configuration for a BarTender document.

 

However, it sounds like you just need to configure a database look-up for this item.  This can be achieve through the use of a VB script for the source of the form field instead of embedded data.  Here is an example of the code you would use to populate a list control in the BarTender data entry form from an Excel file.

 

*Note that you'll need to change folder/file/sheet/field names for your own purposes.

 

 
Functions and Subs
 
dim objConn
dim strConn
dim rs
 
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\items and item numbers.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
 
Public Function GetNames()
    Dim strNames
 
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open strConn
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = objConn.Execute("SELECT * FROM [Sheet1$]")
 
    strNames = ""
    rs.MoveFirst()
    do while NOT rs.EOF
        strNames = strNames + rs.fields("Item") + vbCrLf
        rs.MoveNext()
    loop
 
    GetNames = strNames
 
End Function
 
 
OnFillList
 
Value = GetNames()
0
Avatar
Legacy Poster
Comment actions Permalink

I am in a similar situation, but retrieving records from a SQL Server database using a System DSN.    The query is working correctly when i run it directly against the database, so there must be an issue somewhere else.  Any ideas what the issue is?

 

 

'Connect to the system DSN
Value = Value


Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.open "IMFTest905", "Corp\svc_epicor", "Norgren01"


'get the view with usercode values that we need
Dim strSQL
strSQL = "select Number01 from v_labelcodes where v_labelcodes.CodeTypeID = 'ARTPAM' AND v_labelcodes.CodeID = 'F'"
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, conn

 

'make sure we have one record, and if so, set the value to Number01
if rs.RecordCount = 1 then
 Value = rs("Number01")
else
 Value = Value
end if

 

'close the connection, clean up
conn.close
set conn = Nothing

Please sign in to leave a comment.