Knowledge Base

1. Create a mimic containing a grid control. Find the name of the grid using the Graphic Explorer. The default is AIGrid1.2. Insert the following VBA code in the Mimic_Open event for the mimic.

Public oCnx As New ADODB.Connection

Private Sub Mimic_Open()
Dim oCmd As New ADODB.Command
Dim oRec As New ADODB.Recordset
Dim iRowIndex As Integer

On Error GoTo eOnError
iRowIndex = 0
‘Initialise AIGrid1 content (clear rows and populate fields header)
AIGrid1.ClearAll
AIGrid1.CellText(iRowIndex, 1) = “Chrono”
AIGrid1.CellText(iRowIndex, 2) = “Name”
AIGrid1.CellText(iRowIndex, 3) = “Value”
AIGrid1.CellText(iRowIndex, 4) = “Quality”

‘Customize your connection string
oCnx.ConnectionString = “Provider=SQLNCLI11.1;Integrated Security=””””;Persist Security Info=False;User ID=sa;Password=arcinfo;Initial Catalog=testSGBD_DATABASE1;Data Source=.\SVSQLSERVER”

‘Open connection
oCnx.Open
oCmd.ActiveConnection = oCnx

‘Send SQL Request
oCmd.CommandText = “SELECT * FROM [TrendTable1]”
Set oRec = oCmd.Execute

‘Read answer and populate AIGrid1 rows
If oRec.EOF = False Then
oRec.MoveFirst

While oRec.EOF = False
iRowIndex = iRowIndex + 1
AIGrid1.CellText(iRowIndex, 1) = oRec.Fields(0).Value
AIGrid1.CellText(iRowIndex, 2) = oRec.Fields(1).Value
AIGrid1.CellText(iRowIndex, 3) = oRec.Fields(2).Value
AIGrid1.CellText(iRowIndex, 4) = oRec.Fields(3).Value

oRec.MoveNext
Wend
End If

‘Close connection

oCnx.Close

Exit Sub

eOnError:
MsgBox “Error: ” & Err.Description
oCnx.Close

End Sub

3. Using the VBA Editor add the Microsoft ActiveX DataObjects 6.0 Library reference to the mimic.

4. Customize the connection string in the VBA script. If you don’t know the string you can use the following trick to find it.

a. Create a text file (.txt) and change its extension to .udl.
b. Double click on the file to open a Data Link Properties dialog. Configure a connection with the SQL Server and database. Note that when PcVue creates a database it prefixes the configued name of the database with the project name. Example: KB838_DATABASE1.
c. Use Test Connection to make sure you have everything right.

d. Close the Data Link Properties dialog.
e. Change the extension back to .txt and open the file with Notepad.
d. You should see a line that looks similar to the connection string in the example VBA code. Copy and paste it to the VBA code.
Example: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=KB838_DATABASE1;Data Source=TESTER-PC\SQLEXPRESS

5. Save and execute the VBA script by closing and opening the mimic. If everything works it might look something like this.

6. Remember that you can request and manipulate data before you insert it in the grid. For example:

Truncate result
SELECT top (20) * FROM [TrendTable1] order by Chrono DESC

Change the field order
SELECT Name, Chrono,Quality, Value FROM[TrendTable1]

Request an SQL Server view
SELECT * FROM[MyView]

Request using a WHERE clause
SELECT * FROM[TrendTable1] WHERE QUALITY = 192