1

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.

Improve this question
16
  • Is your table-name really Table? Commented Oct 25, 2017 at 14:41
  • 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. Commented Oct 25, 2017 at 14:44
  • @FunThomas, does that matter what table name is? Commented Oct 25, 2017 at 14:49
  • Where are you declaring Sheet1? Commented Oct 25, 2017 at 14:50
  • @VictorK, record count returns -1 Commented Oct 25, 2017 at 14:51

1 Answer 1

Reset to default
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

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.