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

Dim cn As New adodbConnection

Dim rs As New adodbRecordset

cnProvider = "microsoftjetoledb40"

cnOpen (ThisWorkbookPath & "\数据库\classmdb")

rsActiveConnection = cn

rsOpen ("select from class"), , adOpenDynamic, adLockPessimistic

If Not rsEOF Then

MsgBox ("成绩已经存在于数据库中")

Else

For i = 3 To 18

rsaddnew

rs!Name = Sheet1Cells(i, 1)

rs!chinese = Sheet1Cells(i, 2)

rs!Math = Sheet1Cells(i, 3)

rs!english = Sheet1Cells(i, 4)

Sheet1Cells(18, 2) = Sheet1Cells(18, 2) + Sheet1Cells(i, 2)

Sheet1Cells(18, 3) = Sheet1Cells(18, 3) + Sheet1Cells(i, 3)

Sheet1Cells(18, 3) = Sheet1Cells(18, 4) + Sheet1Cells(i, 4)

If i = 18 Then

rs!chinese = Sheet1Cells(18, 2)

rs!english = Sheet1Cells(18, 4)

rs!Math = Sheet1Cells(18, 3)

rs!平均分 = CInt((rs!chinese + rs!english + rs!Math) / 3)

rs!总分 = rs!chinese + rs!english + rs!Math

Sheet1Cells(18, 5) = rs!平均分

Else

rs!总分 = CInt(Sheet1Cells(i, 2)) + CInt(Sheet1Cells(i, 3)) + CInt(Sheet1Cells(i, 4))

rs!平均分 = CInt(rs!总分 / 3)

Sheet1Cells(i, 5) = rs!平均分

End If

rsaddnew

rs!Name = Sheet1Cells(i, 7)

rs!chinese = Sheet1Cells(i, 8)

rs!Math = Sheet1Cells(i, 9)

rs!english = Sheet1Cells(i, 10)

Sheet1Cells(18, 8) = Sheet1Cells(18, 8) + Sheet1Cells(i, 8)

Sheet1Cells(18, 9) = Sheet1Cells(18, 9) + Sheet1Cells(i, 9)

Sheet1Cells(18, 10) = Sheet1Cells(18, 10) + Sheet1Cells(i, 10)

If i = 18 Then

rs!chinese = allchineseB

rs!english = allenglishB

rs!Math = allmathB

rs!平均分 = CInt((rs!chinese + rs!english + rs!Math) / 3)

Sheet1Cells(18, 11) = rs!平均分

rs!总分 = rs!chinese + rs!english + rs!Math

Else

rs!总分 = CInt(Sheet1Cells(i, 8)) + CInt(Sheet1Cells(i, 9)) + CInt(Sheet1Cells(i, 10))

rs!平均分 = CInt(rs!总分 / 3)

Sheet1Cells(i, 11) = rs!平均分

End If

rsUpdate

rsaddnew

rs!Name = Sheet1Cells(i, 13)

rs!chinese = Sheet1Cells(i, 14)

rs!Math = Sheet1Cells(i, 15)

rs!english = Sheet1Cells(i, 16)

Sheet1Cells(18, 14) = Sheet1Cells(18, 14) + Sheet1Cells(i, 14)

Sheet1Cells(18, 15) = Sheet1Cells(18, 15) + Sheet1Cells(i, 15)

Sheet1Cells(18, 16) = Sheet1Cells(18, 16) + Sheet1Cells(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

Sheet1Cells(i, 17) = rs!平均分

Else

rs!总分 = CInt(Sheet1Cells(i, 14)) + CInt(Sheet1Cells(i, 15)) + CInt(Sheet1Cells(i, 16))

rs!平均分 = CInt(rs!总分 / 3)

Sheet1Cells(i, 17) = rs!平均分

End If

rsUpdate

Next

rsClose

MsgBox ("成绩已经成功写入数据库")

End If

Set cn = Nothing

Set rs = Nothing

End Sub

这是一个简单的列子，就是把Excel中的信息存放到数据库表中！！

请看下面的代码，有注释的！

Private Sub CommandButton5_Click()

Dim Conn As New ADODBConnection '定义ADODB连接对象

Dim ConnStr As String '定义连接字符串

Dim xiao As String

xiao = ComDataText

'对于SQL 2008，如果以IP方式访问服务器，必须要启动SQL Server Browser服务，如果是Express版本，要以“IP(计算机名)\实例名”方式访问

ConnStr = txtDataText

Dim Records As New ADODBRecordset '定义ADODB对象的记录集

Dim Sheet As Worksheet '定义工作表

Set Sheet = ThisWorkbookWorksheets("Sheet2") '给变量Sheet赋值，注意：是工作簿中索引为1的那个表（通常一个新的工作簿Sheet1的索引为1）

'SheetName = "Data" '把Sheet名称改为Data

SheetCellsClear '清空表中原有的数据

'连接状态是打开就不在进行Open *** 作

ConnOpen ConnStr

可以的，我就做过，在安装了oracle的机子上搜这三个库文件（我用的是oracle11g），ocidll、orannzsbb11dll、oraociei11dll，拿出来，要用到它们，然后把这三个文件放在程序根目录就可以了，注意与服务器的编码问题，如果编码不一致，中文可能出现乱码

你数据对不上是少数据吗你的语句中用了like,但没有通配符,试试修改

sqls = "SELECT FROM NDYX where FIELD(0) like'" & a & "'" '定义SQL查询命令字符串"

以上就是关于求一个完整的vba连接access数据库的案例全部的内容，包括:求一个完整的vba连接access数据库的案例、求助excel vba 连接到OBD数据库后的问题、EXCEL2007在win7下通过VBA无法连接ORACLE数据库(客户端为12c64位），求解等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！