Private Sub Form_Load()

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;Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=<username>;Pwd=<strong password>"
    '.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=<username>;Pwd=<strong password>"
    .CursorLocation = adUseServer
    .Open
End With

Set adoCm = New adoDb.Command
With adoCm
    Set .ActiveConnection = adoCn
    .CommandType = adCmdText
    .CommandText = "if exists (select * from sysobjects " & _
                            "where id = object_id('dbo.spADOTempTest') " & _
                            "and sysstat & 0xf = 4) " & _
                            "drop procedure dbo.spADOTempTest"
    .Execute
    .CommandText = "Create procedure spADOTempTest " & _
                            "as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
                            "INSERT INTO #test(field1) SELECT fname FROM Employee " & _
                            "SELECT * FROM #test go"
    .Execute
    .CommandType = adCmdStoredProc
    .CommandText = "spADOTempTest"
    'the default for Prepared statements is false.
    '.Prepared = False
End With
    
Set adoRs = New adoDb.Recordset
With adoRs
    Set .ActiveConnection = adoCn
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    'Uncomment the next line with the SQLOLEDB provider to fix the error.
    '.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc

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

End Sub