Question

求一个完整的vba连接access数据库的案例

Answer 1

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset

cn.Provider = "microsoft.jet.oledb.4.0"
cn.Open (ThisWorkbook.Path & "\数据库\class.mdb")

rs.ActiveConnection = cn
rs.Open ("select * from class"), , adOpenDynamic, adLockPessimistic
If Not rs.EOF Then
MsgBox ("成绩已经存在于数据库中")
Else
For i = 3 To 18
rs.addnew
rs!Name = Sheet1.Cells(i, 1)
rs!chinese = Sheet1.Cells(i, 2)
rs!Math = Sheet1.Cells(i, 3)
rs!english = Sheet1.Cells(i, 4)
Sheet1.Cells(18, 2) = Sheet1.Cells(18, 2) + Sheet1.Cells(i, 2)
Sheet1.Cells(18, 3) = Sheet1.Cells(18, 3) + Sheet1.Cells(i, 3)
Sheet1.Cells(18, 3) = Sheet1.Cells(18, 4) + Sheet1.Cells(i, 4)
If i = 18 Then

rs!chinese = Sheet1.Cells(18, 2)
rs!english = Sheet1.Cells(18, 4)
rs!Math = Sheet1.Cells(18, 3)
rs!平均分 = CInt((rs!chinese + rs!english + rs!Math) / 3)
rs!总分 = rs!chinese + rs!english + rs!Math
Sheet1.Cells(18, 5) = rs!平均分
Else
rs!总分 = CInt(Sheet1.Cells(i, 2)) + CInt(Sheet1.Cells(i, 3)) + CInt(Sheet1.Cells(i, 4))
rs!平均分 = CInt(rs!总分 / 3)
Sheet1.Cells(i, 5) = rs!平均分
End If

rs.addnew
rs!Name = Sheet1.Cells(i, 7)
rs!chinese = Sheet1.Cells(i, 8)
rs!Math = Sheet1.Cells(i, 9)
rs!english = Sheet1.Cells(i, 10)

Sheet1.Cells(18, 8) = Sheet1.Cells(18, 8) + Sheet1.Cells(i, 8)
Sheet1.Cells(18, 9) = Sheet1.Cells(18, 9) + Sheet1.Cells(i, 9)
Sheet1.Cells(18, 10) = Sheet1.Cells(18, 10) + Sheet1.Cells(i, 10)

If i = 18 Then
rs!chinese = allchineseB
rs!english = allenglishB
rs!Math = allmathB
rs!平均分 = CInt((rs!chinese + rs!english + rs!Math) / 3)
Sheet1.Cells(18, 11) = rs!平均分
rs!总分 = rs!chinese + rs!english + rs!Math
Else
rs!总分 = CInt(Sheet1.Cells(i, 8)) + CInt(Sheet1.Cells(i, 9)) + CInt(Sheet1.Cells(i, 10))
rs!平均分 = CInt(rs!总分 / 3)
Sheet1.Cells(i, 11) = rs!平均分
End If
rs.Update

rs.addnew
rs!Name = Sheet1.Cells(i, 13)
rs!chinese = Sheet1.Cells(i, 14)
rs!Math = Sheet1.Cells(i, 15)
rs!english = Sheet1.Cells(i, 16)

Sheet1.Cells(18, 14) = Sheet1.Cells(18, 14) + Sheet1.Cells(i, 14)
Sheet1.Cells(18, 15) = Sheet1.Cells(18, 15) + Sheet1.Cells(i, 15)
Sheet1.Cells(18, 16) = Sheet1.Cells(18, 16) + Sheet1.Cells(i, 16)

If i = 18 Then
rs!chinese = allchineseC
rs!english = allenglishC
rs!Math = allmathC
rs!平均分 = CInt((rs!chinese + rs!english + rs!Math) / 3)
rs!总分 = rs!chinese + rs!english + rs!Math
Sheet1.Cells(i, 17) = rs!平均分
Else
rs!总分 = CInt(Sheet1.Cells(i, 14)) + CInt(Sheet1.Cells(i, 15)) + CInt(Sheet1.Cells(i, 16))
rs!平均分 = CInt(rs!总分 / 3)
Sheet1.Cells(i, 17) = rs!平均分
End If
rs.Update
Next

rs.Close
MsgBox ("成绩已经成功写入数据库")
End If

Set cn = Nothing
Set rs = Nothing
End Sub
这是一个简单的列子，就是把Excel中的信息存放到数据库表中！！

Answer 2

我有，全部真实数据。增加，删除，修改，查询。Excel+VBA+Access
适当收费的