VBA CONNECT SQL SERVER

Sub GetDataFromADO()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = “foxtube.com.au” ‘ Enter your server name here
Database_Name = “SQLTEST” ‘ Enter your database name here
User_ID = “sa” ‘ enter your user ID here
Password = “yxxxxx” ‘ Enter your password here
SQLStr = “SELECT * FROM dbo.SQLTB” ‘ Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open “Driver={SQL Server};Server=” & Server_Name & “;Database=” & Database_Name & _
“;Uid=” & User_ID & “;Pwd=” & Password & “;”

rs.Open SQLStr, Cn, adOpenStatic
‘ Dump to spreadsheet
With Worksheets(“sheet1”).Range(“a1:z500”) ‘ Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With
‘ Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

添加 工具-引用 microsoft activex data 2.0

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *