Geek asks and answers

Create a temp table in SQL Server 2008 by using the Excel macro (ADODB)

After many operations with Google, I got the following macro, which I hoped to connect to the database, delete 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 line NOCOUNTcomes from http://support.microsoft.com/kb/235340 (as well as much of the code above). I added IMEX=1to take into account that the order number can have several types, but I doubt that where this problem occurs.

Any help is much appreciated!

+5
source share
2 answers

recodset, recodset.

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)"
+5

, , . .

, . . . . -, , .

0

More articles:


All Articles