I have a connection and query I need run against a MS SQL Server from EXCEL using VBA. Here is the code.
The error I get comes up at the
VBA is claiming the connection is not open but objMyConn.State = 1 is saying it is open. What am I missing?
Thanks.
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
NextThe error I get comes up at the
VBA Code:
For Each f In objMyRecordset.GetRows
Debug.Print f
NextVBA is claiming the connection is not open but objMyConn.State = 1 is saying it is open. What am I missing?
Thanks.
