Connecting RSView HMI to external data?

I'm trying to customise an existing RSView HMI application to include a drop-down menu item that is populated from an external datasource. There appears to be no documentation regarding this that I can find. The customization enviroment is Visual Basic for Applications- can anyone confirm that it is possible to connect to a DSN/ODBC source from within VBA? I can add a Windows Native Listbox control, but it doesn't have the properties that I would normally use to bind the control to a datasource.

Some sample code would be great, if it exists.

What type of database are you using? I have worked with some RSView applications which connect to an Oracle database using ADO calls. These calls are used to populate drop-down lists with data from the Oracle data tables.

Unfortunately, I don't have authorization to post any example code from this proprietary system.

Hope this gets you started.

Rick

In this case, SQL server.

Where is my ADO code supposed to go? In the VBA associated with the display, I can process events, but the actual data binding of the control happens somewhere else. Where is that done?

One way I've seen it done is to use the ADO calls in the Display_AnimationStart subroutine.

Keep in mind, I am a relative VBA newbie; I can do a decent job interpreting code that I see, but I don't do much coding in my day-to-day job...

Rick

I'm not getting anywhere with this. Can you tell me if your example is similar to this?

Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
adoConn.Open "DSN=mydsn", "user", "password"
strSQL = "SELECT Job FROM production;"
adoRS.Open strSQL, adoConn
Do Until adoRS.EOF
strItem = adoRS.Fields("Job").Value
Me.ListBox1.AddItem strItem
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing

jsacksteder said:

I'm not getting anywhere with this. Can you tell me if your example is similar to this?

Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
adoConn.Open "DSN=mydsn", "user", "password"
strSQL = "SELECT Job FROM production;"
adoRS.Open strSQL, adoConn
Do Until adoRS.EOF
strItem = adoRS.Fields("Job").Value
Me.ListBox1.AddItem strItem
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing

Click to expand...

This is pretty close to how it's done in my example. I've copied your code and made a few changes. Basically, I'd try using the adoField object instead of the string item. See the modified code below (with changes in bold):

Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim adoField As ADODB.Field
Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
adoConn.Open "DSN=mydsn", "user", "password"
strSQL = "SELECT Job FROM production;"
adoRS.Open strSQL, adoConn
Set adoField = adoRS.Fields("Job")
Do Until adoRS.EOF
If adoField.Value <> "" Then ListBox1.AddItem adoField.Value
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing


Hope this helps!
Rick