首先建立一个ACCESS数据库,名字为TEST.MDB,里面建立一个“telephone”的表,字段有 "姓名"公司""座机"等,并加入几条记录,保存在D盘。
在EXCEL的VB编辑器中,插入一个模块,输入搜索数据库函数:
Public Sub Serchmdb(ByVal so, si As String)
Dim cmd As String
Dim oAss As Object
connstr = "DBQ=D:\test.mdb;DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
Set oAss = CreateObject("ADODB.connection")
oAss.Open connstr
cmd = "SELECT * FROM telephone WHERE " + si + " like '%" + so + "%'"
On Error GoTo 0
Set rs = oAss.Execute(cmd)
btop = 4
bleft = 2
Range("A2:Z1000").ClearContents
Cells(btop, bleft + 1) = "序号"
Cells(btop, bleft + 2) = "姓名"
Cells(btop, bleft + 3) = "公司"
Cells(btop, bleft + 4) = "座机"
Do While Not rs.EOF
btop = btop + 1
Cells(btop, bleft + 1) = rs("id")
Cells(btop, bleft + 2) = rs("姓名")
Cells(btop, bleft + 3) = rs("公司")
Cells(btop, bleft + 4) = rs("座机")
rs.movenext
Loop
rs.Close
End Sub
在EXCEL页面上加一个文本框(TextBox1),一个组合框(ComboBox1),一个按钮(CommandButton1)。
在VB编辑器界面中,点击sheet1,添加以下代码:
Dim so, si As String
Private Sub ComboBox1_Change()
aa = ComboBox1.ListIndex
If aa < 0 Then
si = ""
Else
si = ComboBox1.List(aa)
End If
End Sub
Private Sub CommandButton1_Click()
so = TextBox1.Text
If si = "" Then
si = ComboBox1.List(0)
End If
Serchmdb so, si
End Sub
Public Sub addcom()
With ComboBox1
.Clear
.AddItem "姓名"
.AddItem "公司"
.AddItem "座机"
.Text = .List(0)
si = .List(0)
End With
End Sub
addcom()为往组合框中输入搜索项目名称的函数,需要先运行一下。在文本框中输入搜索内容(或空),点击按钮显示查询结果。
暂时不知道怎样实现分页和往数据库中添加数据或者编辑数据,请高手指点。
评论