'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
'recordset, command and connection variables
Dim Cnxn As ADODB.connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim strCnxn As String
Dim strSQLAuthors As String
Dim strSQLByRoyalty As String
'record variables
Dim intRoyalty As Integer
Dim strAuthorID As String
' Define a command object for a stored procedure
Set Cnxn = New ADODB.connection
strCnxn = "rovider=sqloledb;Data Source=localhost;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI;"
Cnxn.Open strCnxn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = Cnxn
' Set the criteria
strSQLByRoyalty = "byroyalty"
cmdByRoyalty.CommandText = strSQLByRoyalty
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
' Define the stored procedure's input parameter
intRoyalty = Trim(InputBox("Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
' Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()
' Open the Authors Table to get author names for display
Set rstAuthors = New ADODB.Recordset
strSQLAuthors = "Authors"
'rstAuthors.Open strSQLAuthors, strCnxn, , , adCmdTable
rstAuthors.Open strSQLAuthors, strCnxn, adOpenForwardOnly, adLockReadOnly, adCmdTable
'the above two lines of code are identical as the default values for
'CursorType and LockType arguments match those shown
' Print the recordset and add author names from Table
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
rovider=sqloledb;Data Source=localhost;" & _