创建RecordSet对象以将查询结果输出到Excel单元格

Question

我正在远程服务器上查询数据库，并在Excel电子表格中显示我的结果。在列A中说。

因此，我创建了一个按钮以允许“随意”操作并开始设置我的ADODB对象。

到数据库的连接是好的，但它是非常不清楚我如何建立.Recordset对象（MyOutput中）输出我的查询的A列下面的结果是我的代码：

Private Sub RunQuery_Click() 

Dim MyOutput As ADODB.Recordset 

Dim cnn As New ADODB.Connection         
Dim myCommand As ADODB.Command         

Dim stringSQL As String           
Dim stringConn As String           

cnn.Provider = "Microsoft.Jet.OLEDB.4.0;" 
cnn.Properties("Jet OLEDB:System database") = "My path" 
stringConn = "Data Source=\'my path';User Id='';Password='';" 

cnn.Open stringConn            

Set myCommand = New ADODB.Command        
myCommand.ActiveConnection = cnn         

stringSQL = " My query"   
myCommand.CommandText = stringSQL 
myCommand.Execute              
cnn.Close              
Set cnn = Nothing 
End Sub  

我可以在这里得到一些帮助吗？

非常感谢您的时间！

Answer 1

您可以使用类似下面这样做：

Public Sub RunQuery_Click() 

Dim oDB As ADODB.Connection 
Dim oCM As ADODB.Command 
Dim oRS As ADODB.Recordset 
Dim strConn As String 

Set oDB = New ADODB.connectoin 

With oDB 
    .Provider = "Microsoft.Jet.OLEDB.4.0;" 
    .Properties("Jet OLEDB:System database") = "My path" 
    strConn = "Data Source=\'my path';User Id='';Password='';" 
    .Open strConn 
End With 

    Set oCM = New ADODB.Command 

With oCM 
    .ActiveConnection = oDB 
    .CommandText = "My Query" 
    .CommandType = adCmdText 
    Set oRS = .Execute 
End With 

Sheets(1).Range("A1").CopyFromRecordset oRS 

oRS.Close 
Set oRS = Nothing 
oDB.Close 
Set oDB = Nothing 

End Sub 

Alternativly，如果你想返回的字段名称，以及，你可以使用：

Public Sub RunQuery_Click() 

Dim oDB As ADODB.Connection 
Dim oCM As ADODB.Command 
Dim oRS As ADODB.Recordset 
Dim strConn As String 
Dim iCols As Long 

Set oDB = New ADODB.connectoin 

With oDB 
    .Provider = "Microsoft.Jet.OLEDB.4.0;" 
    .Properties("Jet OLEDB:System database") = "My path" 
    strConn = "Data Source=\'my path';User Id='';Password='';" 
    .Open strConn 
End With 

    Set oCM = New ADODB.Command 

With oCM 
    .ActiveConnection = oDB 
    .CommandText = "My Query" 
    .CommandType = adCmdText 
    Set oRS = .Execute 
End With 

For iCols = 0 To oRS.Fields.Count - 1 
Sheet(1).Cells(1, iCols + 1).Value = oRS.Fields(iCols).Name 
Next 

Sheets(1).Range("A2").CopyFromRecordset oRS 

oRS.Close 
Set oRS = Nothing 
oDB.Close 
Set oDB = Nothing