ADO ACCESS数据库程序编写

 Option Explicit                             '变量显示
'----------------------------------
'          变量定义申明
'----------------------------------
'数据库路径及数据库、连接字符串
Public ADOcon As New ADODB.Connection           '链接声明
Public ADOrs As New ADODB.Recordset             '记录集声明
'----------------------------------
'          过程、函数定义
'----------------------------------
'----------------------------------
'           链接数据库
'----------------------------------
Public Function ADOconOpen()
    Dim strAccess As String                 '相对路径
    Set ADOcon = New ADODB.Connection       '定义连接变量
    strAccess = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & App.Path & "\data\基于物联网的智能路灯控制系统.mdb;Persist Security Info=False" '相对路径
    ADOcon.Open strAccess                   '连接 智能路灯控制系统 数据库
End Function
'----------------------------------
'          断开链接数据库
'----------------------------------
Public Function ADOconClose()
    ADOcon.Close                            '关闭连接 智能路灯控制系统 数据库
    Set ADOcon = Nothing                    '从内存中清除
End Function
'----------------------------------
'           End Of File
'----------------------------------

添加

Call ADOconOpen                             '建立与数据库连接
                                                '打开和建立一个记录集
    Set ADOrs = New ADODB.Recordset             '记录集定义
    strSQL = "Select * From 街道情况 Where 街道名称 ='" & frmStreet.txtStreetName.Text & "'"
    '从数据库中查找数据
    ADOrs.Open strSQL, ADOcon, adOpenKeyset, adLockPessimistic '打开通道
   
    If (ADOrs.EOF = False Or ADOrs.BOF = False) Then
        If MsgBox("街道已存在,请重新输入街道名!", vbOKOnly + vbInformation, "信息") = vbOK Then
           frmStreet.txtStreetName.SetFocus
            Exit Sub
        End If
    Else
        If (ADOrs.EOF = True And ADOrs.BOF = True) Then
            strSQL = "Insert Into 街道情况(行政区,街道名称)"
            strSQL = strSQL & "Values('" & frmStreet.cboArea.Text & "','" & frmStreet.txtStreetName.Text & "')"
            ADOcon.Execute strSQL                   '执行strSQL
            If MsgBox("已成功添加新街道", vbOKOnly + vbInformation, "信息") = vbOK Then
                ADOrs.Close
                Set ADOrs = Nothing
                Call ADOconClose                    '关闭与数据库连接
                frmStreet.txtStreetName.Text = ""   '清空暂存信息
                Exit Sub
            End If
        End If
    End If

删除

Call ADOconOpen                         '建立与数据库连接
                                            '打开和建立一个记录集
    Set ADOrs = New ADODB.Recordset         '记录集定义
    strSQL = "Select * From 街道情况 Where 街道名称 ='" & Me.txtStreetName.Text & "'"
    '从数据库中查找数据
    ADOrs.Open strSQL, ADOcon, adOpenKeyset, adLockPessimistic '打开通道
   
    If (ADOrs.EOF = True And ADOrs.BOF = True) Then
        If MsgBox("该街道不存在,请重新输入街道名!", vbOKOnly + vbInformation, "信息") = vbOK Then
            frmStreet.txtStreetName.SetFocus
            Exit Sub
        End If
    Else
        If (ADOrs.EOF = False Or ADOrs.BOF = False) Then
            strAreaName = "" & (ADOrs.Fields("行政区"))          '获得行政区名
            strStreetName = "" & (ADOrs.Fields("街道名称"))      '获得街道名称               
            If MsgBox("确定要删除该街道吗?", vbYesNo + vbQuestion, "信息") = vbYes Then
                ADOcon.Execute "Delete From 街道情况 Where 街道名称 ='" & Me.txtStreetName.Text & "'"
                If MsgBox("该街道已成功删除", vbOKOnly + vbInformation + vbDefaultButton1, "信息") = vbOK Then
                    ADOrs.Close
                    Set ADOrs = Nothing
                    Call ADOconClose                            '关闭与数据库连接
                    frmStreet.txtStreetName.Text = ""           '清空暂存信息
                    Exit Sub
                End If
            End If
        End If
    End If

读取

 Call ADOconOpen                         '建立与数据库连接
                                            '打开和建立一个记录集
    Set ADOrs = New ADODB.Recordset         '记录集定义
    strSQL = "Select * From 街道情况 where 行政区='" & Me.cboArea.Text & "'"
    '从数据库中查找数据
    ADOrs.Open strSQL, ADOcon, adOpenKeyset, adLockPessimistic '打开通道
   
    If ADOrs.RecordCount <> 0 Then
        ADOrs.MoveFirst                     '指向首条记录
        strStreetName = "" & ADOrs.Fields("街道名称")
        ADOrs.MoveFirst                     '指向首条记录
        Do While ADOrs.EOF = False
            Me.cboStreet.AddItem ADOrs.Fields("街道名称")
            ADOrs.MoveNext
        Loop
    End If
   
    ADOrs.Close
    Set ADOrs = Nothing
    Call ADOconClose                        '关闭与数据库连接
    Call LEDStreetTestInit

更新

Call ADOconOpen                                 '建立与数据库连接
    '打开和建立一个记录集
    Set ADOrs = New ADODB.Recordset                 '记录集定义
    strSQL = "Select * From Zigbee配置情况 Where MAC地址='" & frmZigbee.txtZigbeeMACAddress.Text & "'"
    '从数据库中查找数据
    ADOrs.Open strSQL, ADOcon, adOpenKeyset, adLockPessimistic '打开通道
   
    If ADOrs.EOF = False Or ADOrs.BOF = False Then
        strSQL = "Update Zigbee配置情况"            '刷新ZigbeeToAccess配置
        strSQL = strSQL & " set 网络ID='" & frmZigbee.txtZigbeePANIDH.Text & " " & frmZigbee.txtZigbeePANIDL.Text & " ', 波特率='"
        strSQL = strSQL & frmZigbee.cboZigbeeBaudRate.Text & "', 网络地址='" & frmZigbee.txtZigbeeShortAddress.Text & "' "
        strSQL = strSQL & " Where  MAC地址= '" & frmZigbee.txtZigbeeMACAddress.Text & " '"
        ADOcon.Execute strSQL                       '执行strSQL
        ADOrs.Close
        Set ADOrs = Nothing
        Call ADOconClose                            '关闭与数据库连接
        Exit Sub
    Else                                            '创建ZigbeeToAccess配置
        If (ADOrs.EOF = True And ADOrs.BOF = True) Then
            strSQL = "Insert Into Zigbee配置情况(网络ID,波特率,网络地址,MAC地址)"
            strSQL = strSQL & " Values('" & frmZigbee.txtZigbeePANIDH.Text & " " & frmZigbee.txtZigbeePANIDL.Text & " ','"
            strSQL = strSQL & frmZigbee.cboZigbeeBaudRate.Text & "','" & frmZigbee.txtZigbeeShortAddress.Text & "','"
            strSQL = strSQL & frmZigbee.txtZigbeeMACAddress.Text & "')"
            ADOcon.Execute strSQL                   '执行strSQL
            ADOrs.Close
            Set ADOrs = Nothing
            Call ADOconClose                        '关闭与数据库连接
            Exit Sub
        End If
    End If