在VB中連接Access數(shù)據(jù)庫并對(duì)數(shù)據(jù)進(jìn)行操作。本例子使用的數(shù)據(jù)庫是Access2007，實(shí)現(xiàn)了對(duì)Access數(shù)據(jù)庫數(shù)據(jù)的查詢，添加，刪除，修改。

要連接access數(shù)據(jù)庫有兩種方法用ADODC或ADO。

方法一: （ADODC)
此方法首先須引用Projects-Components-Microsoft ADO Data Controls 6.0 (OLEDB).
之后在表單設(shè)置ADODC控件。然后Form_Load()輸入以下代碼：

Private Sub Form_Load()
ADODC1.ConnectionString = "provider=Microsoft.jet.OLEDB.4.0;Persist security info= False; Data source=" & App.Path & "數(shù)據(jù)庫名稱.mdb"

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

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

注：此方法在使用中應(yīng)記得首先須引用工程-引用-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;

實(shí)例：

在Form中設(shè)置3個(gè)Textbox和5個(gè)CommandButton
分別為

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

CommandButton = 新增(cmdadd),儲(chǔ)存(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

以下自作函數(shù)以便文本欄顯示資料表資料
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) （顯示系統(tǒng)今天日期）
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

以下為儲(chǔ)存按鈕代碼
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個(gè)Textbox,1個(gè)commmandbutton和1個(gè)Listbox
Textbox=txtfind
CommandButton=cmdsearch
ListBox=List1

以下為取消查詢按鈕代碼,可查詢?nèi)掌?，文字和?shù)字.
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