SQL Query Failing with Run-time error '3704: Operation is not allowed when the object is closed.

G

gedkins

New Member
Joined
Sep 30, 2014
Messages
15
I have a connection and query I need run against a MS SQL Server from EXCEL using VBA. Here is the code.


VBA Code: 
Dim objMyConn As ADODB.connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim iCols As Integer
        Dim results As ADODB.Recordset


        Set objMyConn = New ADODB.connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

        'Open Connection'
        objMyConn.ConnectionString = PDM
        objMyConn.Open

        'Set and Excecute SQL Command'
        Dim u As String
        Set objMyCmd.ActiveConnection = objMyConn

        objMyCmd.CommandText = "DECLARE @DateInput AS DATETIME SET @DateInput = '01/15/2023' SELECT p.Path, d.Filename, dc.ConfigurationName, v1.ValueText AS [PIHC Part Number], v2.ValueText AS [Description], v3.ValueText AS [Supplier],v4.ValueText AS [Supplier Part Number], v5.ValueText AS [Mfg], v6.ValueText AS [Mfg Part Number], v7.ValueText AS [Legacy Part #], v8.ValueText AS [Date Created], v9.ValueText AS [Item Type] FROM Documents d " & _
                                "LEFT JOIN DocumentsInProjects dip ON dip.DocumentID = d.DocumentID LEFT JOIN Projects p ON p.ProjectID = dip.ProjectID LEFT JOIN (SELECT d.DocumentID, MAX(drc.RevisionNo) AS [Version], dc.ConfigurationName, dc.ConfigurationID FROM DocumentRevisionConfiguration drc " & _
                                "LEFT Join Documents d ON d.DocumentID = drc.DocumentID LEFT JOIN DocumentConfiguration dc ON dc.ConfigurationID = drc.ConfigurationID WHERE (d.ExtensionID = 4 Or d.ExtensionID = 5) GROUP BY d.DocumentID, dc.ConfigurationName, dc.ConfigurationID) AS dc ON dc.DocumentID = d.DocumentID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 53 GROUP BY v.DocumentID, v.VariableID) AS vv1 ON vv.DocumentID = vv1.DocumentID AND vv1.maxrev = vv.RevisionNo AND vv1.VariableID = vv.VariableID) AS v1 ON d.DocumentID = v1.DocumentID AND v1.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 50 GROUP BY v.DocumentID, v.VariableID) AS vv2 ON vv.DocumentID = vv2.DocumentID AND vv2.maxrev = vv.RevisionNo AND vv2.VariableID = vv.VariableID) AS v2 ON d.DocumentID = v2.DocumentID AND v2.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 54 GROUP BY v.DocumentID, v.VariableID) AS vv3 ON vv.DocumentID = vv3.DocumentID AND vv3.maxrev = vv.RevisionNo AND vv3.VariableID = vv.VariableID) AS v3 ON d.DocumentID = v3.DocumentID AND v3.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 48 GROUP BY v.DocumentID, v.VariableID) AS vv4 ON vv.DocumentID = vv4.DocumentID AND vv4.maxrev = vv.RevisionNo AND vv4.VariableID = vv.VariableID) AS v4 ON d.DocumentID = v4.DocumentID AND v4.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 49 GROUP BY v.DocumentID, v.VariableID) AS vv5 ON vv.DocumentID = vv5.DocumentID AND vv5.maxrev = vv.RevisionNo AND vv5.VariableID = vv.VariableID) AS v5 ON d.DocumentID = v5.DocumentID  AND v5.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 46 GROUP BY v.DocumentID, v.VariableID) AS vv6 ON vv.DocumentID = vv6.DocumentID AND vv6.maxrev = vv.RevisionNo AND vv6.VariableID = vv.VariableID) AS v6 ON d.DocumentID = v6.DocumentID AND v6.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 112 GROUP BY v.DocumentID, v.VariableID) AS vv7 ON vv.DocumentID = vv7.DocumentID AND vv7.maxrev = vv.RevisionNo AND vv7.VariableID = vv.VariableID) AS v7 ON d.DocumentID = v7.DocumentID AND v7.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 61 GROUP BY v.DocumentID, v.VariableID) AS vv8 ON vv.DocumentID = vv8.DocumentID AND vv8.maxrev = vv.RevisionNo AND vv8.VariableID = vv.VariableID) AS v8 ON d.DocumentID = v8.DocumentID AND v8.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 56 GROUP BY v.DocumentID, v.VariableID) AS vv9 ON vv.DocumentID = vv9.DocumentID AND vv9.maxrev = vv.RevisionNo AND vv9.VariableID = vv.VariableID) AS v9 ON d.DocumentID = v9.DocumentID AND v9.ConfigurationID = dc.ConfigurationID " & _
                                "WHERE dc.DocumentID IS NOT NULL AND d.Deleted = 0 AND v8.ValueText > @DateInput ORDER BY d.DocumentID, v1.ValueText"




        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
       
        If objMyConn.State = 1 Then
            Debug.Print "Connected!"
        End If
  

        For Each f In objMyRecordset.GetRows
              Debug.Print f
        Next


The error I get comes up at the


VBA Code: 
For Each f In objMyRecordset.GetRows
              Debug.Print f
        Next

VBA is claiming the connection is not open but objMyConn.State = 1 is saying it is open. What am I missing?

Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
gedkins said:
I have a connection and query I need run against a MS SQL Server from EXCEL using VBA. Here is the code.


VBA Code: 
Dim objMyConn As ADODB.connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim iCols As Integer
        Dim results As ADODB.Recordset


        Set objMyConn = New ADODB.connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

        'Open Connection'
        objMyConn.ConnectionString = PDM
        objMyConn.Open

        'Set and Excecute SQL Command'
        Dim u As String
        Set objMyCmd.ActiveConnection = objMyConn

        objMyCmd.CommandText = "DECLARE @DateInput AS DATETIME SET @DateInput = '01/15/2023' SELECT p.Path, d.Filename, dc.ConfigurationName, v1.ValueText AS [PIHC Part Number], v2.ValueText AS [Description], v3.ValueText AS [Supplier],v4.ValueText AS [Supplier Part Number], v5.ValueText AS [Mfg], v6.ValueText AS [Mfg Part Number], v7.ValueText AS [Legacy Part #], v8.ValueText AS [Date Created], v9.ValueText AS [Item Type] FROM Documents d " & _
                                "LEFT JOIN DocumentsInProjects dip ON dip.DocumentID = d.DocumentID LEFT JOIN Projects p ON p.ProjectID = dip.ProjectID LEFT JOIN (SELECT d.DocumentID, MAX(drc.RevisionNo) AS [Version], dc.ConfigurationName, dc.ConfigurationID FROM DocumentRevisionConfiguration drc " & _
                                "LEFT Join Documents d ON d.DocumentID = drc.DocumentID LEFT JOIN DocumentConfiguration dc ON dc.ConfigurationID = drc.ConfigurationID WHERE (d.ExtensionID = 4 Or d.ExtensionID = 5) GROUP BY d.DocumentID, dc.ConfigurationName, dc.ConfigurationID) AS dc ON dc.DocumentID = d.DocumentID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 53 GROUP BY v.DocumentID, v.VariableID) AS vv1 ON vv.DocumentID = vv1.DocumentID AND vv1.maxrev = vv.RevisionNo AND vv1.VariableID = vv.VariableID) AS v1 ON d.DocumentID = v1.DocumentID AND v1.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 50 GROUP BY v.DocumentID, v.VariableID) AS vv2 ON vv.DocumentID = vv2.DocumentID AND vv2.maxrev = vv.RevisionNo AND vv2.VariableID = vv.VariableID) AS v2 ON d.DocumentID = v2.DocumentID AND v2.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 54 GROUP BY v.DocumentID, v.VariableID) AS vv3 ON vv.DocumentID = vv3.DocumentID AND vv3.maxrev = vv.RevisionNo AND vv3.VariableID = vv.VariableID) AS v3 ON d.DocumentID = v3.DocumentID AND v3.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 48 GROUP BY v.DocumentID, v.VariableID) AS vv4 ON vv.DocumentID = vv4.DocumentID AND vv4.maxrev = vv.RevisionNo AND vv4.VariableID = vv.VariableID) AS v4 ON d.DocumentID = v4.DocumentID AND v4.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 49 GROUP BY v.DocumentID, v.VariableID) AS vv5 ON vv.DocumentID = vv5.DocumentID AND vv5.maxrev = vv.RevisionNo AND vv5.VariableID = vv.VariableID) AS v5 ON d.DocumentID = v5.DocumentID  AND v5.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 46 GROUP BY v.DocumentID, v.VariableID) AS vv6 ON vv.DocumentID = vv6.DocumentID AND vv6.maxrev = vv.RevisionNo AND vv6.VariableID = vv.VariableID) AS v6 ON d.DocumentID = v6.DocumentID AND v6.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 112 GROUP BY v.DocumentID, v.VariableID) AS vv7 ON vv.DocumentID = vv7.DocumentID AND vv7.maxrev = vv.RevisionNo AND vv7.VariableID = vv.VariableID) AS v7 ON d.DocumentID = v7.DocumentID AND v7.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 61 GROUP BY v.DocumentID, v.VariableID) AS vv8 ON vv.DocumentID = vv8.DocumentID AND vv8.maxrev = vv.RevisionNo AND vv8.VariableID = vv.VariableID) AS v8 ON d.DocumentID = v8.DocumentID AND v8.ConfigurationID = dc.ConfigurationID " & _
                                "LEFT JOIN (SELECT vv.ConfigurationID, vv.VariableID, vv.DocumentID, vv.ValueText FROM VariableValue vv INNER JOIN (SELECT v.VariableID, v.DocumentID, MAX(v.RevisionNo) AS [maxrev] FROM VariableValue v WHERE v.VariableID = 56 GROUP BY v.DocumentID, v.VariableID) AS vv9 ON vv.DocumentID = vv9.DocumentID AND vv9.maxrev = vv.RevisionNo AND vv9.VariableID = vv.VariableID) AS v9 ON d.DocumentID = v9.DocumentID AND v9.ConfigurationID = dc.ConfigurationID " & _
                                "WHERE dc.DocumentID IS NOT NULL AND d.Deleted = 0 AND v8.ValueText > @DateInput ORDER BY d.DocumentID, v1.ValueText"




        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
      
        If objMyConn.State = 1 Then
            Debug.Print "Connected!"
        End If
 

        For Each f In objMyRecordset.GetRows
              Debug.Print f
        Next


The error I get comes up at the


VBA Code: 
For Each f In objMyRecordset.GetRows
              Debug.Print f
        Next

VBA is claiming the connection is not open but objMyConn.State = 1 is saying it is open. What am I missing?

Thanks.
Click to expand...
I solved this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
am getting a run time error '3704' Operation is not allowed when the object is closed.
Replies
2
Views
1K
jimmy2times
J
E
  • Question
Run-Time error 9
Replies
1
Views
714
mole999
mole999
V
  • Question
SQL via VBA error: Operation is not allowed when the object is closed
2 3
Replies
25
Views
67K
raiyanb
R
J
  • Question
VBA SQL Run time error
Replies
4
Views
735
jimmy_p84
J

Forum statistics

Threads
1,214,918
Messages
6,122,243
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top