StarQuest Technical Documents

Sample Visual Basic ADO Code to Access Static SQL

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ045


Below is sample code written in Visual Basic 6.0 that demonstrates how to access a static SQL statement embedded in a static package bound on the host. This method accesses the statement directly by statement name, rather than the more typical technique, which relies on the StarSQL driver to match SQL statements in the code to those in the package.


First, make sure your StarSQL data source has "enabled matching" and that the appropriate .sbp file is specified under Static SQL options. This is crucial to ensuring static SQL works properly. For more details on creating and binding the static SQL package, see the StarSQL Help, Configuring SQL Options.

Second, to use this code, do the following:

  1. Create a form named Form1.
  2. Add command button named Command1.
  3. Add a listbox called List1.
  4. Add "Microsoft ActiveX Data Objects 2.0 Library" to Project | References.
  5. Add the code below to the command button's click event.
  6. Modify the connect string to match your data source name and uid and pwd.
  7. Modify the CALL STATEMENT to reflect the statement in your .sbp file that you wish to match (this statement should already be recorded; the static package should be saved and bound -- refer to the StarScribe Package Editor help for details.)
  8. Modify the parameter properties as appropriate to the SQL statement you are accessing in the .sbp file

Sample Code

Private Sub Command1_Click()

On Error GoTo Command3_ErrorProc:
Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim objErr As ADODB.Error
Dim adoParm As ADODB.Parameter
Dim adoRsc As ADODB.Recordset
Dim RecordsAffected As Long
Dim SqlStmt As String
Dim j As Integer

Me.MousePointer = 11

SqlStmt = "CALL STATEMENT00001(?)"

adoConn.CursorLocation = adUseClient

adoCmd.Name = "TestParm"
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = SqlStmt
adoCmd.CommandType = adCmdText
adoCmd.CommandTimeout = 15

Set adoParm = New ADODB.Parameter
adoParm.Name = "MY_PARM"
adoParm.Type = adVarChar
adoParm.Size = 15
adoCmd.Parameters.Append adoParm

adoParm.Value = "TEST"

Set adoRsc = adoCmd.Execute(RecordsAffected)
For j = 1 To adoRsc.RecordCount
List1.AddItem "[" & adoRsc(0) & "] " & Len(adoRsc(0))

Set adoCmd = Nothing
Set adoConn = Nothing
Me.MousePointer = 0
Exit Sub

Me.MousePointer = 0

If Not adoCmd Is Nothing Then
Set adoCmd = Nothing
End If

If Not adoConn Is Nothing Then
Set adoConn = Nothing
End If

MsgBox Err.Description

Exit Sub

End Sub


The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.