Knowledge Base

To access a relational database in PcVue by means of an ActiveX ADO in VBA, you must first define a connection procedure including a connection string. Its syntax is complex and can vary between database suppliers.

Example

Here is the VBA code for doing so.

Private Sub Mimic_Open()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Request As String
Dim i As Integer, j As Integer

‘Open connection

Set cn = New ADODB.Connection
cn.ConnectionString = “Provider=MSDASQL.1;Persist Security Info=False;Data Source=database1_link;Initial Catalog=” & ThisProject.Path & “\TP\Database1”
cn.Open

‘ Provider=MSDASQL.1;Persist Security Info=False;Data Source=database1_link;Initial Catalog=D:\PcVue32\800VBA\USR\Stagevba\TP\Database1

‘Send Request

Request = “SELECT * FROM data”
Set rs = cn.Execute(Request)

‘Request treatment

rs.MoveFirst
i = 1

While rs.EOF = False

For j = 0 To (rs.Fields.Count – 1)

AIGrid1.CellText(0, j + 1) = rs.Fields.Item(j).Name
AIGrid1.CellText(i, j + 1) = rs.Fields(j).Value

Next

i = i + 1
AIGrid1.RowCount = i + 1
rs.MoveNext

Wend

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox “Read done”

End Sub

What to modify

The path in the line highlighted in bold depends on the context (for example):

cn.ConnectionString = “Provider=MSDASQL.1;Persist Security Info=False;Data Source=DATABASE1”

Where to find the connection string

1. In the Windows Control Panel, double-click on the Administrative Tools icon to open the Administrative Tools dialog.
2. Double-click on Data Sources (ODBC) to open the ODBC Data Source Administrator dialog.

1. Click on the Add button to open the dialog Create New Data Source. Select the driver for the data source that is to be created. Click on Finish to close the dialog.

1. Click on the Configure button to open the dialog ODBC Microsoft Access Setup.

1. Click on Select to open the Select Database dialog.

6. Navigate to the database to be used, select it then click on OK to return to the ODBC Microsoft Access Setup dialog. Click on OK to return to the ODBC Data Source Administrator dialog.
7. In the Windows Desktop, create a text file called (for example) ACCESS.UDL. Double-click on it to open the Data Link Properties dialog.
8. In the Provider tab, select Microsoft OLE Provider for ODBC Drivers.

1. In the Connection tab, use the first drop-down box to select the database link for DATABASE1.

10. To check the connection, click on the button Test Connection. A message confirms that it has succeeded. Click on OK to close the dialog.
11. Open the file ACCESS.UDL with a text editor (e.g. Windows Notepad).

12. Make a note of the connection string shown in it for use in your VBA code, for example:

Provider=MSDASQL.1;Persist Security Info=False;Data Source=DATABASE1