基本的excel vba sql连接 [英] basic excel vba sql conection
问题描述
我的SQL VBA连接出现问题。你能帮忙解决下面的代码吗？我想访问一个表并将表放在A1中。
im having problems with my SQL VBA connection. can you help with the code below? i want to access a table and put the table in A1.
这是我的代码：
Sub DataExtract（）
$
Dim cnData As ADODB.Connection
设置cnData =新ADODB .Connection
Dim strConn As String
'使用SQL Server OLE DB提供程序。
strConn =" ; OLEDB;提供商= SQLOLEDB.1;" &安培; _
"数据来源= ***。com \ SQL01,1433;" &安培; _
$
" Password = ****; Persist Security Info = True;" &安培; _
"用户ID = ****;"
$
cnData.Open strConn
Dim rsData As ADODB.Recordset
设置rsData =新ADODB.Recordset
使用rsData
'分配Connection对象。
.ActiveConnection = cnData
'提取所需记录。
。打开"SELECT * FROM *
'
复制
记录到Sheet1上的单元格A1。
Sheet1.Range（QUOT; A1"）CopyFromRecordse。 t rsData
'整理上$
b $ b。关闭
结束与
< br $>
cnData.Close
设置rsData =无什么b $ b设置cnData =无
Sub DataExtract()
Dim cnData As ADODB.Connection
Set cnData = New ADODB.Connection
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "OLEDB;Provider=SQLOLEDB.1;" & _
"Data Source=***.com\SQL01,1433;" & _
"Password=****;Persist Security Info=True;" & _
"User ID=****;"
cnData.Open strConn
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
With rsData
' Assign the Connection object.
.ActiveConnection = cnData
' Extract the required records.
.Open "SELECT * FROM *
'
Copy
the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsData
' Tidy up
.Close
End With
cnData.Close
Set rsData = Nothing
Set cnData = Nothing
End Sub
推荐答案
嗨Parabolam，
Hi Parabolam,
你不能使用SQL语句 " SELECT * FROM *"。
You cannot use the SQL statement "SELECT * FROM *".
它
应该类似于"SELECT * FROM MyTable"。用要从中获取数据的表名替换MyTable。
如果这不是您遇到问题的代码行，请告诉我们。您只需在宏屏幕上按F8即可逐行运行应用程序。按F8键，每行都会执行。
Let us know if that's not the codeline you're facing problems. You can run your application line by line simply pressing F8 on your macro screen. Press F8 and every line will be executed.
Rgds
这篇关于基本的excel vba sql连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
