After lots of Googling, I've ended up with the following macro that I hoped would connect to a database, drop any existing temp table and then create a new one (populate it, and view the results).
Dim adoCn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim adoCm As ADODB.Command
Dim strSQL As String
Set adoCn = New ADODB.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;" & _
"Initial_Catalog=XXX;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Data Source=XXX;" & _
"Extended Properties='IMEX=1'"
.CursorLocation = adUseServer
.Open
End With
Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts"
.Execute
.CommandText = "CREATE TABLE #AgedProducts " & _
"(Source_Order_Number VARCHAR(255)) " & _
"INSERT INTO #AgedProducts VALUES ('AB-123-456') " & _
"SELECT * FROM #AgedProducts (NOLOCK) "
.Execute
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm
MsgBox "Recordset returned...", vbOKOnly
While Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
Wend
adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing
When I run the query I get the following error message:
Run-time error '-2147217887 (80040e21)':
The requested properties cannot be supported
The NOCOUNT
line comes from http://support.microsoft.com/kb/235340 (as does much of the above code). I've added IMEX=1
to take into account order number might have multiple types in there but I doubt that's where the problem is happening.
Any help is greatly appreciated!
Modify the way how the recodset is opened, move the select from the command to the recodset open method call.
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts"
.Execute
.CommandText = "CREATE TABLE #AgedProducts " & _
"(Source_Order_Number VARCHAR(255)) " & _
"INSERT INTO #AgedProducts VALUES ('AB-123-456') "
.Execute
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockBatchOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
End With
adoRs.Open "SELECT * FROM #AgedProducts (NOLOCK)"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With