Public Sub 导入数据到ACCESS()
Dim mydata As String, mytable As String, SQL As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsx As ADODB.Recordset
Dim i As Long, n As Long
mydata = ThisWorkbook.Path & "\成绩.mdb" '指定数据库
mytable = "成绩" '指定数据表
n = Sheets("Sheet1").Range("A65536").End(xlUp).Row '指定要保存数据的记录数(行数)
'建立与数据库的连接
Set cnn = New ADODB.Connection
With cnn
.Provider = "microsoft.jet.oledb.4.0"
.Open mydata
End With
'建立查询
mysql = "select* from " & mytable
Set rs = New ADODB.Recordset
rs.Open mysql, cnn, adOpenKeyset, adLockOptimistic
For i = 2 To n
'查询是否已经存在了某条记录
SQL = "select * from " & mytable & " where 学号='" & Cells(i, 1).Value & "'"
Set rsx = New ADODB.Recordset
rsx.Open SQL, cnn, adOpenKeyset, adLockOptimistic
If rsx.RecordCount = 0 Then
rsx.AddNew
For j = 1 To rsx.Fields.Count
rsx.Fields(Cells(1, j).Value) = Cells(i, j).Value
Next j
rsx.Update
Else
For j = 2 To rsx.Fields.Count
rsx.Fields(j - 1) = Cells(i, j).Value
Next j
rsx.Update
End If
Next i
MsgBox "数据保存完毕!", vbInformation + vbOKOnly
rs.Close
cnn.Close
Set rs = Nothing
Set rsx = Nothing
Set cnn = Nothing
End Sub作者: zhengjialon 时间: 2009-8-5 09:47
请说出你的问题在哪里?作者: 宏魔法师 时间: 2009-8-5 11:44
问题就是:我想把一个excel 文件里的数据导入Access里,但不知道 VBA代码如何写?作者: 宏魔法师 时间: 2009-8-5 16:12
各位高手帮忙解答下吧!作者: 宏魔法师 时间: 2009-8-5 20:43
请高手们点拨下吧作者: 宏魔法师 时间: 2009-8-6 19:49
我试了下这段代码,可以实现Access读取excel数据的作用,但关键是只能重复的导入,遇见同一关键号,不能更新,请高手想想办法吧!
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "temp", "c:\temp.xls", yes
End Sub作者: toad13 时间: 2010-10-21 23:22
能否有高人出个办法呵呵{:2_84:}作者: wuwu200222 时间: 2022-9-24 16:12
学习