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 ConnectionDim rst As RecordsetDim str As StringDim cmd As ADODB.CommandDim rst1 As ADODB.RecordsetDim Msg As StringDim QTRSALES As ObjectDim cmd1 As ADODB.CommandDim prm1 As ADODB.ParameterDim prm2 As ADODB.ParameterDim prm3 As ADODB.Parameter
'Create a Connection object after instantiating it,'this time to a SQL Server database.Set cnn = New ADODB.Connectioncnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _ "Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.Set rst = New ADODB.Recordsetrst.CursorType = adOpenKeysetrst.LockType = adLockOptimistic'Open recordset, and print some test records.rst.Open "QTRSALES", cnnSet cmd = New ADODB.Command'Specify the Querycmd.CommandText = "SELECT * FROM QTRSALES"cmd.CommandType = adCmdText
'Loop Through and Display The Field NamesMsg = " "For i = 0 To rst.Fields.Count - 1 Msg = Msg & "|" & rst.Fields(i).NameNextMsgBox Msg'Loop Through and Display The Field Values for Each RecordMsg = " "Debug.Print rst.Fields(0).Name; Spc(10); rst.Fields(1).Name; Spc(6); rst.Fields(2).Namerst.MoveFirstDo 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.MoveNextLoopMsgBox ("Connection was successful.")'Clean up objects.'rst.Close
Set cmd1.ActiveConnection = CurrentProject.ConnectionSet rst1 = cmd1.Execute(NumRecs)
' ---Instantiate command and set up for use with' ---stored procedureSet cmd1 = New ADODB.Commandcmd1.ActiveConnection = CurrentProject.Connectioncmd1.CommandType = adCmdStoredProccmd1.CommandText = "Year_in_@year_in_out_return"
' ---Set up a return parameterSet prm1 = cmd1.CreateParameter("Return", adInteger, _ adParamReturnValue)cmd1.Parameters.Append prm1
' ---Set up an output parameterSet prm2 = cmd1.CreateParameter("sum_of_amount", _ adCurrency, adParamOutput)cmd1.Parameters.Append prm2
' ---Create parameter, assign value, and append to commandSet 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.RecordsetSet rst1 = cmd1.ExecuteDebug.Print "Results for " & prm3.ValueDebug.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 objectsrst1.CloseSet rst1 = Nothing
cnn.CloseSet cnn = Nothing
Set rst1 = NothingEnd Sub
Year Quarter Amount
1995 1 125,000.90
1995 2 136,000.75
1995 3 212,000.34
1995 4 328,000.82
1996 3 728,000.35
1996 2 422,000.13
1996 1 328,000.82
Please help and tell me why I got this error and how to correct this problem.
Thanks in advance,
I think the error occurs because you try to set properties of cmd1 before initiating it. Try putting 'Set cmd1 = new adodb.command' before the offending line.
Hope this helps. Greetz,
Hi Marc, Thanks for your help.
I did add that statement as you suggusted. And that problem was solved.
I proceeded to execute the project and modified the code statement " Set prm3 = cmd1.CreateParameter("year", adChar, adParaInput, 4)" to "Set prm3 = cmd1.CreateParameter(year", adInteger, adParamOutput)" after I got a Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Then I continued to execute the project and I got a new Run-time error '-2147217900(80040e14)': Could not find stored procedure 'Year_In_@year_In_out_return' on the code statement "Set rst1 = cmd1.Execute". I tried and created a regular stored procedure in the "Queries" section for solving that new Run-time error. But it did not work for me. I am completely lost now. Do you have any ideas about solving this new Run-time error? Please help and advise me.
Hi R. Brush, Thanks for your response.
I found an error in the script of my stored procedure in Access 2003 Queries:
ALTER PROCEDURE dbo.[Year_In_@year_In_out-return]
AS SELECT Year, Quarter, Amount
WHERE (Year = 1995)
ORDER BY Quarter
I corrected it and executed the new stored precedure:
ALTER PROCEDURE dbo.Year_In_@year_In_out_return
I got a new Run-time error '-2147217900(80040e14)':
Procedure Year_In_@year_In_out_return has no parameters and arguments were supplied.
Please review the corrected script and the new error message, and tell me how to solve this problem.
What Cory added to your stored procedure was the @year input parameter. This input paramter is needed as part of the stored procedure if you are wanting to pass the year (1995) to the SP. That is what your VB code is doing in the following lines:
You cannot pass an input parameter to a SP unless the SP is expecting one.
Gentlemen, Thank you for your responses and help.
I mimicked the programming code statements in Pages 605-607 of the Book "Programming Microsoft Office Access 2003" by Rick Dobson to do this Access 2003 adp, ADODB.Command, T-SQL programming in order to get a object/file of a Rotated Table/Pivot Table by the technique stated in the article from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574:
View products that this article applies to.
February 14, 2005
This article was previously published under Q175574
Sample Query to Rotate the Table
Query for Large Tables
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):
Year Quarter Amount
Now, suppose you want to rotate the table so that you can see the data in the following format:
YEAR Q1 Q2 Q3 Q4
1995 125,000.90 136,000.75 212,000.34 328,000.82
1996 328,000.82 422,000.13 728,000.35 0.00
The query that you would use to rotate the table is in the next section of this article.
Back to the top
Here is the query that you would use to rotate the table:
Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
FROM QTRSALES Q
GROUP BY YEAR
For large tables, this query will be faster:
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year
I do not know how to handle the following code statements:
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "Year_In_@year_in_out_return
and Set rst1 = cmd1.Execute.
I am lost completely now. Please help and advise me again if
Hi Cory, Thanks for your kind response and valuable code for doing the "Rotate a Table" in Access 2003.
I tried to do it as you instructed. First I did write the VBA code in the Module and then tried to set up the Stored Procedure Year_In_@year_In_out_return that I encountered difficulties:
(Problem#1) On the code statement .Parameter ("@year") = 1995, I got the following error: Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.
I tried to write a Stored Procedure Year_In_@year_In_out_reurn in the Query, I got (Problem #2): Microsoft Office Access-ADO error: Incorrect syntax near the keyword 'FROM'. or ADO error: Incorrect syntax near the keyword "SELECT". Line 3: Incorrect syntax near 'SUM', wheh I tried to complete the following: ALTER PROCEDURE dbo.Year_In_@year_In_out_return
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3
FROM QTRSALES q
WHERE year = @year
I feel that I can not set up the above-mentioned Stored Procedure in the Query of Access 2003 because Access 2003 is so limited and it is not designed as the Query Analyst of SQL Server 2000. Am I right in this aspect? If I am wrong in this aspect, please give me more instructions for setting up the desired Stored Procedure in the Query of Access 2003.
I have one more question to ask you: What is the 'year=q.year' in the code statement "SELECT year=q.year"?
Please help and respond.