下面是我的代码,想把表里的记录录入到Excel中
代码一:
Dim strconn As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\测试.mdb"
conn.Open strconn
rs.Open "select * from 合同", conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Range("a17").CopyFromRecordset rs
End If
代码二:
Dim RS1 As Recordset
Dim DB1 As database Set DB1 = OpenDatabase(ThisWorkbook.Path & "\" & "测试.mdb")
Set RS1 = DB1.OpenRecordset(Name:="合同", Type:=dbOpenDynaset)
错误提示:均出现“找不到工程或库”这是什么原因?
代码一:
Dim strconn As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\测试.mdb"
conn.Open strconn
rs.Open "select * from 合同", conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Range("a17").CopyFromRecordset rs
End If
代码二:
Dim RS1 As Recordset
Dim DB1 As database Set DB1 = OpenDatabase(ThisWorkbook.Path & "\" & "测试.mdb")
Set RS1 = DB1.OpenRecordset(Name:="合同", Type:=dbOpenDynaset)
错误提示:均出现“找不到工程或库”这是什么原因?
2、有些控件添加在excel表中会提示“不能创建对象”的错误,添加在用户窗体中会提示“未经正确授权”的错误;
3、如果用excel自带的query,如何对数据库进行操作呢(添加、修改、删除数据)
请大侠多多指点!
代码一:
Dim strconn As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\测试.mdb"
conn.Open strconn
rs.Open "select * from 合同", conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Range("a17").CopyFromRecordset rs
End If 引用:
在vba中 工具——〉引用——〉microsoft activex data objects 2.8
你的连接有问题,修改一下
测试了Private Sub CommandButton1_Click()
Dim strconn As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\date.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\测试.mdb;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open strconn
rs.Open "select * from 合同", conn, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Range("a17").CopyFromRecordset rs
End If
End Sub
我用你的方法并在vba中添加了“microsoft activex data objects 2.8”控件,仍然会出现“找不到工程或库”的错误。
我发现在“工具->引用->可使用的引用”中有一项“丢失:Microsoft ADO Data Control 6.0(OLEDB)”会不会是这个影响的,如果是如何解决?
我用的是office2007应该没什么不一样吧!
和Microsoft ADO Data Control 6.0(OLEDB)应当没有关系
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\测试.mdb"改成:strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & ThisWorkbook.Path & "\测试.mdb'"
或者:
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=''" & ThisWorkbook.Path & "\测试.mdb''"
Set DB1 = OpenDatabase(ThisWorkbook.Path & "\" & "测试.mdb") 改成:
Set DB1 = OpenDatabase("'" & ThisWorkbook.Path & "\" & "测试.mdb'")
或者:
Set DB1 = OpenDatabase("''" & ThisWorkbook.Path & "\" & "测试.mdb''")
看你的错误提示,似乎是路径没有写对,所以找不到数据库
代码二要引用DAO
我写了上面的代码并且在VBA中“工具-》引用-》可使用的引用”中勾选了“Microsoft ActiveX Data Objects 2.0 Library”还需要做哪些操作?
请赐教!