Pages

Tuesday, December 13, 2016

Import data from SQL

Macro to import data from SQL using ADO connection string:

Sub Import_data_from_SQL()

' Tools -> References -> Microsoft Active Data object 2.0
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection

Dim sConnString As String

Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection

' create the connection
'Server=;UserID=myUsername;password=myPassword;
sConnString = "Provider=SQLOLEDB;Data Source=servername;" & _
              "Initial Catalog=NORTHWIND;" & _
              "Integrated Security=SSPI;"
'Open connection
cnn.Open sConnString

strQry = "SELECT * FROM ORDERS"
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open strQry, cnn
End With

'paste data
Sheets(1).Range("A1").CopyFromRecordset rs

'close
rs.Close
cnn.Close

End Sub




No comments:

Post a Comment