SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access 2003 adp: Run-time error ''91'': Object variable or with block variable not set


Access 2003 adp: Run-time error ''91'': Object variable or with block variable not set

Author
Message
Scott H. Chang
Scott  H.  Chang
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 65

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
-------------------------------
 
   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,

Scott Chang


Marc Bosgraaf
Marc Bosgraaf
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 82

Hi Scott,

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,

Marc


Scott H. Chang
Scott  H.  Chang
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 65

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.

Thanks again,

Scott Chang


R. Brush
R. Brush
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1455 Visits: 365
If you post a script of your stored procedure someone may be able to help. It sounds like you are not referencing it or it's parameters correctly.
Scott H. Chang
Scott  H.  Chang
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 65

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

FROM dbo.QTRSALES

WHERE (Year = 1995)

ORDER BY Quarter

///////////////////////////////////////////////

I corrected it and executed the new stored precedure:

ALTER PROCEDURE dbo.Year_In_@year_In_out_return

AS SELECT Year, Quarter, Amount

FROM dbo.QTRSALES

WHERE (Year = 1995)

ORDER BY Quarter

==================================================

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.

Thanks again,

Scott Chang


Cory Perkins
Cory Perkins
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 6
ALTER PROCEDURE dbo.Year_In_@year_In_out_return

@year char(4)

AS SELECT Year, Quarter, Amount

FROM dbo.QTRSALES

WHERE (Year = @year)


I think this is what you are looking for.
ORDER BY Quarter

I think this is what you are looking for.
R. Brush
R. Brush
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1455 Visits: 365

Scott,

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:

' ---Create parameter, assign value, and append to command
Set prm3 = cmd1.CreateParameter("year", adChar, _
adParaInput, 4)
prm3.Value = "1995"
cmd1.Parameters.Append prm3

You cannot pass an input parameter to a SP unless the SP is expecting one.


Scott H. Chang
Scott  H.  Chang
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 65

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:

HOW TO: Rotate a Table in SQL Server

View products that this article applies to.

Article ID

:

175574

Last Review

:

February 14, 2005

Revision

:

3.1

This article was previously published under Q175574

On This Page

SUMMARY

Sample Query to Rotate the Table

Query for Large Tables

REFERENCES

APPLIES TO

SUMMARY

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
   -------------------------------
 
   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
                               



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

Sample Query to Rotate the Table

Here is the query that you would use to rotate the table:

SELECT YEAR,
       Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
  Q.YEAR),0),
       Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
  Q.YEAR),0),
       Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
  Q.YEAR),0),
       Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
  Q.YEAR),0)
     FROM QTRSALES Q
     GROUP BY YEAR
                               



Back to the top

Query for Large Tables

For large tables, this query will be faster:

SELECT year=q.year,
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 
you can.
Thanks again,
Scott  Chang

Cory Perkins
Cory Perkins
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 6
ALTER PROCEDURE dbo.Year_In_@year_In_out_return
SELECT year=q.year,
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
WHERE year = @year
GROUP BY year

This will modify the stored proc so that it will return a recordset that
has fields year,q1,q2,q3,q4

Now the vb code should look something like this

Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim rst as New ADODB.Recordset

cnn.Open "Provider=SQLOLEDB;Data Source=;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"

With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "dbo.Year_In_@year_In_out_return"
.Parameters.Refresh
.Parameters("@year") = 1995
End With

With rst
Set .ActiveCommand = cmd
.Open
End With

debug.print rst!year & " " & rst!q1 & " " & rst!q2 & " " & rst!q3 & " " & rst!q4

rst.close
cnn.close
set cmd = nothing
set rst = nothing
Set cnn = nothing


That should be pretty close, I didn't check any of the code but give it a try and I hope it helps.
Scott H. Chang
Scott  H.  Chang
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 65

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

SELECT year=q.year

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

WHERE year = @year

GROUP BY 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.

Thanks,

Scott Chang


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search