在 VBA 中从 SQL Server 获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5674187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data fetching from SQL Server in VBA
提问by SQLfanatic
Hi Below is my code, i am not able to fetch the data from my SQL server, its throwing error as
嗨下面是我的代码，我无法从我的 SQL 服务器获取数据，它的抛出错误为
Compiler error : object required.
There is no problem with the connection, connection is sucessful.
连接没有问题，连接成功。
please correct my code, help me with this thing
请更正我的代码，帮我解决这个问题
Private Sub CommandButton1_Click()
Set SQLConn = CreateObject("ADODB.Connection")
SQLConn.Open "provider =sqloledb; Data Source = xxxx; Initial Catalog = jjjj; User Id = yyyy; Password = zzzz"
MsgBox "Connection Succesful"
Set SQLData = CreateObject("ADODB.Recordset")
With SQLData
' Assign the Connection object.
.ActiveConnection = SQLConn
' Extract the required records.
.Open "select invoice_num, invoice_date, invoice_amount from im_invoice where billing_account = 'HS0076A' and invoice_date ='01-apr-2011'"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset SQLData
' Tidy up
.Close
End With
SQLConn.Close
Set SQLData = Nothing
Set SQLConn = Nothing
End Sub
Thank you
谢谢
thank you its working .... :)
谢谢它的工作...... :)
回答by Tim Williams
Missing "Set"...
缺少“设置”...
' Assign the Connection object.
Set .ActiveConnection = SQLConn
回答by Satyarth Rao
I am writing this query when you want to establish a connection between Excel & SQL Server in VBA using OLEDBConnecion. And yes it's for Windows authentication. Solution is mentioned below. You need to add 'Microsoft.ActiveX Object Library 2.8'.
当您想使用 OLEDBConnecion 在 VBA 中建立 Excel 和 SQL Server 之间的连接时，我正在编写此查询。是的，它用于 Windows 身份验证。解决方法如下。您需要添加“ Microsoft.ActiveX 对象库 2.8”。
Sub GetDataFromADO()
'Declare variables'
Set objMyconn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
Dim rc As Long
'Open Connection'
objMyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=SAXAM\SQLEXPRESS;Initial Catalog=AdventureWorks2012; Integrated Security=SSPI;"
objMyconn.Open
'Set and Excecute SQL Command'
Set objMyCmd.ActiveConnection = objMyconn
objMyCmd.CommandText = "select * from [Person].[BusinessEntity] "
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyconn
objMyRecordset.Open objMyCmd
'Copy Data to Excel'
'ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
Application.ActiveCell.CopyFromRecordset (objMyRecordset)
rc = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(rc + 1, 1).Select
'Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
objMyconn.Close
End Sub
Thanks!
谢谢！
