Question

用VBA从excel向access插入数据

Debug的时候.Update这里报错了。不知道为啥。求原因与修改方法。谢谢啦！！

Dim adoRT As Object
Dim strSQL As String

Set adoRT = CreateObject("ADODB.RecordSet")

strSQL = "SELECT * FROM Table1 Where False"

With adoRT

.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\Test.accdb"
.CursorLocation = 3
.CursorType = 1
.LockType = 3

.Source = strSQL

.Open

.AddNew

.Fields("Num").Value = Range("B1").Value
.Fields("Pname").Value = Range("B5").Value
.Fields("Sex").Value = Range("D5").Value

.Update

If .State = 1 Then
.Close
End If
End With

Set adoRT = Nothing

Answer 1

dim cn as adodb.connection
dim tsql as string
set cn=new adodb.connection
cn.open "driver={microsoft access driver (*.mdb)};uid=admin;pwd=;dbq=d:\test.mdb"
tsql="insert into table1(Num,Pname,Sex) values('" + range("B1").value + "','" + range("B5").value + "','" + range("D5") +"')"
cn.execute tsql
set cn=nothing