Importing values from SQL Server to Excel

Question

I can't import values values from SQL Server to Excel. Can someone please help:

Dim oConn As ADODB.Connection
Private Sub ConnectDB()

    Set oConn = New ADODB.Connection
    oConn.Open "Provider=SQLOLEDB; " & _
            "Data Source=ServerName; " & _
            "Initial Catalog=MyDB;" & _
            "Trusted_Connection=yes;"
End Sub
Public Sub ExportDataToDB()
    Dim rs As ADODB.Recordset
    ConnectDB
    Set rs = New ADODB.Recordset
    Dim strSql As String

    strSql = "select t.col1, t.col2 from Table t"

    rs.Open strSql, oConn

    Sheet1.Range("A2").CopyFromRecordset rs

    CloseDBConn
End Sub

Private Sub CloseDBConn()
    oConn.Close
End Sub

Connection is successful. No errors. Query is not empty, but nothing is returned into sheet.

How do you determine if the query is not empty? What would ?rs.RecordCount return? Also might be worth checking if Sheet1 belongs to where you think belongs. — Victor K
– Victor K, Commented Oct 25, 2017 at 14:44

Victor K · Accepted Answer · 2017-10-25 15:12:01Z

1

In cases where you would like to just read a set of records from the database, such as writing current state of the records to the worksheet you should specify CursorType property of the ADODB.RecordSet as adOpenStatic Here is the link to Microsoft documention of it: https://learn.microsoft.com/en-us/sql/ado/guide/data/types-of-cursors-ado

answered Oct 25, 2017 at 15:12

Victor K

1,0492 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing values from SQL Server to Excel

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related