UC知道快速与Access创建数据连接
来源：百度知道 编辑：UC知道 时间：2024/06/18 01:36:49
Sub 快速与Access创建数据连接()
'使用实例文件 F_Data.Accdb F_tb101
Dim mycnc1 As String
Dim mycnc2 As String
Dim mycnc3 As String
Dim mycmd As String
Dim myFilename As String
Dim myTblename As String
Worksheets.Add
myFilename = "F_Data.accdb" '读入文档
myTblename = "F_Tb101" '读入表
mycnc1 = "ODBC;"
mycnc2 = "DBQ=" & ThisWorkbook.Path & "\" & myFilename & ";"
'指定驱动程序
mycnc3 = "Drive={Microsoft Access Driver (*.accdb)};"
'使用SQL来读入资料
mycmd = "SELECT * FROM " & myTblname
With ActiveSheet.QueryTables.Add_
(connection:=myCnc1 & mycnc2 & mycnc3, Destination: =Range("A1))
.CommandText = mycmd
.Refresh BackgroundQuery:=False
End With
End Sub
大家帮帮忙，为什么(connection:=myCnc1 & mycnc2 & mycnc3, Destination: =Range("A1))这段是错的？
'使用实例文件 F_Data.Accdb F_tb101
Dim mycnc1 As String
Dim mycnc2 As String
Dim mycnc3 As String
Dim mycmd As String
Dim myFilename As String
Dim myTblename As String
Worksheets.Add
myFilename = "F_Data.accdb" '读入文档
myTblename = "F_Tb101" '读入表
mycnc1 = "ODBC;"
mycnc2 = "DBQ=" & ThisWorkbook.Path & "\" & myFilename & ";"
'指定驱动程序
mycnc3 = "Drive={Microsoft Access Driver (*.accdb)};"
'使用SQL来读入资料
mycmd = "SELECT * FROM " & myTblname
With ActiveSheet.QueryTables.Add_
(connection:=myCnc1 & mycnc2 & mycnc3, Destination: =Range("A1))
.CommandText = mycmd
.Refresh BackgroundQuery:=False
End With
End Sub
大家帮帮忙，为什么(connection:=myCnc1 & mycnc2 & mycnc3, Destination: =Range("A1))这段是错的？
你的数据库连接有问题，可以参考使用下列方法：
首先在【工程】-【引用】中添加 Microsoft ActiveX Data Objext 2.x 引用
Dim conn As New ADODB.connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim connstr As String
Dim dbPath As String
Dim tableName As String
dbPath="d:\A.mdb" '数据库路径
tableName="B" '表名
connstr = "provider=microsoft.jet.oledb.4.0;data source=" & dbPath
sql = "select * from " & tableName & " where ......
conn.open (connstr)
rs.open sql, conn, 3, 3
你的一定是
mycnc2 = "DBQ=" & ThisWorkbook.Path & "\" & myFilename & ";" 这一行有问题,一步一步调试一下,到这行,看看ThisWorkbook.Path 和myFilename 的值,
或直接把数据库拷贝到C:\下,直接改
mycnc2 = "DBQ="c:\库文件 ";"
把源程发给我！我来调试一下！