VB实现Access数据库的数据操作

 在VB中连接Access数据库并对数据进行操作。本例子使用的数据库是Access2007，实现了对Access数据库数据的查询，添加，删除，修改。

要连接access数据库有两种方法用ADODC或ADO。

方法一: （ADODC)
此方法首先须引用Projects-Components-Microsoft ADO Data Controls 6.0 (OLEDB).
之后在表单设置ADODC控件。然后Form_Load()输入以下代码：

Private Sub Form_Load()
ADODC1.ConnectionString = "provider=Microsoft.jet.OLEDB.4.0;Persist security info= False; Data source=" & App.Path & "数据库名称.mdb"

ADODC1.CommandType = adCmdText
ADODC1.RecordSource = "select * from 表格名称"
ADODC1.Refresh
End Sub

下面主要介绍方法二：（ADO）

注：此方法在使用中应记得首先须引用工程-引用-Microsoft ActiveX Data Objects 2.6 Library

连接access2007的字符串：
没有密码：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:myFoldermyAccess2007file.accdb;Persist Security Info=False;  
有密码：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:myFoldermyAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;

 

实例：

在Form中设置3个Textbox和5个CommandButton
分别为

Textbox=Date(txtdate),Items(txtitems),Price(txtprice).

CommandButton = 新增(cmdadd),储存(cmdsave),删除(cmddelete),
取消新增(cmdcancel),编辑(cmdedit).

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mode as string

Private Sub Form_Load()
cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
"Persist security info= False; Data source=" & App.Path & "daily.accdb"
cn.Open
rs.CursorLocation = adUseClient
rs.Open "select * from Daily", cn, adOpenKeyset, adLockPessimistic (Daily为Access 资料表名)

For i = 0 To rs.Fields.Count - 1
List1.AddItem rs.Fields(i).Name
Next i
End Sub

以下自作函数以便文本栏显示资料表资料
Sub LoadControls()
If rs.BOF = False And rs.EOF = False Then
txtdate = rs.Fields("Date")
txtitems = rs.Fields("Items")
txtprice = rs.Fields("Price")
End If
End Sub

以下为新增按钮代码
Private Sub cmdadd_Click()
mode = "add"
txtdate.Text = CStr(Date) （显示系统今天日期）
txtitems.SetFocus
txtitems = ""
txtprice = ""
End Sub

以下为编辑按钮代码
Private Sub cmdedit_Click()
mode = "edit"
Call LoadControls
txtdate.SetFocus
End Sub

以下为删除按钮代码
Private Sub cmddelete_Click()
If rs.EOF = False Then
X = MsgBox("Do you want to delete this record", vbExclamation + vbYesNo)
If X = vbYes Then
rs.Delete
rs.Requery
Call LoadControls
Else
Exit Sub
End If
End If
End Sub

以下为储存按钮代码
Private Sub cmdsave_Click()
If mode = "add" Then
rs.AddNew
rs.Fields("Date") = txtdate
rs.Fields("Items") = txtitems
rs.Fields("Price") = txtprice
rs.Update
MsgBox "Successfully Save Data", vbInformation + vbOKOnly
rs.Requery
txtdate.SetFocus
Call LoadControls
Exit Sub
End If
End Sub

If mode = "edit" Then
rs.Fields("Date") = txtdate
rs.Fields("Items") = txtitems
rs.Fields("Price") = txtprice
rs.Update
MsgBox "Successfully Save Data", vbInformation + vbOKOnly
rs.Requery
txtdate.SetFocus
Call LoadControls
Exit Sub
End If

以下为取消新增按钮代码
Private Sub Cmdcancel_Click()
Call LoadControls
End Sub
-------------------------------
查询：
在form加上1个Textbox,1个commmandbutton和1个Listbox
Textbox=txtfind
CommandButton=cmdsearch
ListBox=List1

以下为取消查询按钮代码,可查询日期，文字和数字.
Private Sub cmdsearch_Click()
Select Case rs.Fields(List1.ListIndex).Name
Case "Items"
SQL = "select * from Table Name where Items=" & "'" & txtfind.Text & "'"
Case "Price"
SQL = "select * from Table Name where Price=" & txtfind.Text
Case "Date"
SQL = "select * from Table Name where Date = #" & txtfind.Text & "#"
End Select

If rs.State = adStateOpen Then rs.Close
rs.Open SQL, cn, adOpenStatic, adLockOptimistic
End Sub