November 14, 2005 at 9:07 am
Hi all,
I got a Run-time error: Object variable or With block variable not set on the code statement "Set cmd1.ActiveConnection = CurrentProject.Connection" when I ran the following source code:
Sub InOutReturnSQLDB()
Dim cnn As Connection
Dim rst As Recordset
Dim str As String
Dim cmd As ADODB.Command
Dim rst1 As ADODB.Recordset
Dim Msg As String
Dim QTRSALES As Object
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
'Open recordset, and print some test records.
rst.Open "QTRSALES", cnn
Set cmd = New ADODB.Command
'Specify the Query
cmd.CommandText = "SELECT * FROM QTRSALES"
cmd.CommandType = adCmdText
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rst.Fields.Count - 1
Msg = Msg & "|" & rst.Fields(i).Name
Next
MsgBox Msg
'Loop Through and Display The Field Values for Each Record
Msg = " "
Debug.Print rst.Fields(0).Name; Spc(10); rst.Fields(1).Name; Spc(6); rst.Fields(2).Name
rst.MoveFirst
Do While (Not rst.EOF)
If rst.Fields(0).Value = "1995" Or "1996" Then
Debug.Print rst.Fields(0).Value, rst.Fields(1).Value, rst.Fields(2).Value
End If
rst.MoveNext
Loop
MsgBox ("Connection was successful.")
'Clean up objects.
'rst.Close
Set cmd1.ActiveConnection = CurrentProject.Connection
Set rst1 = cmd1.Execute(NumRecs)
' ---Instantiate command and set up for use with
' ---stored procedure
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "Year_in_@year_in_out_return"
' ---Set up a return parameter
Set prm1 = cmd1.CreateParameter("Return", adInteger, _
adParamReturnValue)
cmd1.Parameters.Append prm1
' ---Set up an output parameter
Set prm2 = cmd1.CreateParameter("sum_of_amount", _
adCurrency, adParamOutput)
cmd1.Parameters.Append prm2
' ---Create parameter, assign value, and append to command
Set prm3 = cmd1.CreateParameter("year", adChar, _
adParaInput, 4)
prm3.Value = "1995"
cmd1.Parameters.Append prm3
' ---Execute the command to recover the return value (cmd1(0))
' ---and the output parameter (cmd1(1))
Set rst1 = New ADODB.Recordset
Set rst1 = cmd1.Execute
Debug.Print "Results for " & prm3.Value
Debug.Print "Total Amount = " & FormatCurrency(cmd1(1))
Debug.Print "Total Years = " & cmd1(0)
' ---Print subset of result set from the command
'?-Debug.Print vbCr & "Partial List of Years for " & prm3.Value
'?-OpenSubsetOfCmd cmd1, 3
' ---Clean up objects
rst1.Close
Set rst1 = Nothing
cnn.Close
Set cnn = Nothing
Set rst1 = Nothing
End Sub
//////////////QRTSALES//////////////
Year Quarter Amount
-------------------------------