VBA连接中连接sql-access等数据的方法8页

1、VBA连接中连接sql，access等数据的方法收藏Dim CNN As New ADODB.Connection定义一个新的ADO对象连接Dim RST As New ADODB.Recordset定义一个ADO对象数据集Dim Stpath, strSQL As String定义路径、查询变量Stpath = ThisWorkbook.Path & Application.PathSeparator & 学生档案.mdb定义路径及文件名CNN.Open provider=Microsoft.jet.OLEDB.4.0;data source= & Stpath & ;Jet OLEDB:D

2、atabase Password= & 123打开链接：provider=Microsoft.jet.OLEDB.4.0是软件提供者为Microsoft.jet.OLEDB.4.0data source= & Stpath 是链接数据源为StpathJet OLEDB:Database Password= & 123是如果ACCESS数据库设置有保护密码，此句必不可少If ComboBox3.Value = Then如果性别的框中为空则strSQL = Select * from 档案 WHERE 籍贯 LIKE & ComboBox2.Value & 从档案表中查找籍贯为ComboBox2的

3、记录Select*是查找所有符合条件的字段,如果想查找符合条件并显示出具体字段,可以用SELECT 字段名1,字段名2.from 档案是从档案表中查找符合 籍贯 LIKE & ComboBox2.Value & 的记录WHERE后为查找的条件ElseIf ComboBox2.Value = ThenstrSQL = Select * from 档案 WHERE 性别 LIKE & ComboBox3.Value & ElsestrSQL = Select * from 档案 WHERE 性别 LIKE & ComboBox3.Value & & AND 籍贯 LIKE & ComboBox2.

4、Value & End If以上几句为当选取项目不同时设置不同的查找语句RST.Open strSQL, CNN打开记录集recordset.OpenSource(来记录来源),ActiveConnection(打开的链接)Sheet1.Range(A2:G100).ClearContents Sheet1.Cells(2, 1).CopyFromRecordset RSTCopyFromRecordset 方法将一个 ADO 或 DAORecordset对象的内容复制到工作表中，复制的起始位置在指定区域的左上角,Sheet1.Cells(2, 1).CopyFromRecordset RST

5、为把查找到的记录得制到以Sheet1.Cells(2, 1)为顶点的单元格区域中 RST.Close关闭记录集Set RST = Nothing释放对象变量Set CNN = Nothing2：实现查询功能（ADO+SQL）On Error GoTo 100If TextBox1.Text = ThenMsgBox 请输入姓名, 1 + 16, 系统提示 TextBox1.SetFocusElseDim CNN As New ADODB.ConnectionDim RST As New ADODB.RecordsetDim Stpath, strSQL As StringStpath = Th

6、isWorkbook.Path & Application.PathSeparator & 学生档案.mdbCNN.Open provider=Microsoft.jet.OLEDB.4.0;data source= & Stpath & ;Jet OLEDB:Database Password= & 123strSQL = Select * from 档案 WHERE 姓名 LIKE & TextBox1.Value & RST.Open strSQL, CNNTextBox2.Value = RST.Fields(年龄).ValueTextBox4.Value = RST.Fields(性

7、别).ValueTextBox5.Value = RST.Fields(籍贯).ValueRST.CloseSet RST = NothingSet CNN = NothingEnd IfExit Sub100: MsgBox 找不到符合条件的记录, 1 + 16, 系统提示实现查询功能（DAO）On Error GoTo 100If TextBox1.Text = ThenMsgBox 请输入姓名, 1 + 16, 系统提示TextBox1.SetFocusElseDim RS1 As RecordsetDim DB1 As DatabaseSet DB1 = OpenDatabase(Th

8、isWorkbook.Path & & 学生档案.MDB)Set RS1 = DB1.OpenRecordset(Name:=档案, Type:=dbOpenDynaset)RS1.FindFirst姓名= & TextBox1.Value & If RS1.NoMatch = True ThenMsgBox 对不起，没有该记录RS1.CloseExit SubElseTextBox2.Value = RS1.Fields(年龄).ValueTextBox4.Value = RS1.Fields(性别).ValueTextBox5.Value = RS1.Fields(籍贯).ValueTex

9、tBox6.Value = RS1.Fields(联系电话).ValueEnd IfRS1.CloseSet RS1 = NothingSet DB1 = NothingEnd IfExit Sub100: MsgBox 找不到符合条件的记录, 1 + 16, 系统提示3：实现数据输入功能（利用DAO）代码：Dim RS1 As RecordsetDim DB1 As DatabaseOn Error GoTo 1000Set DB1 = OpenDatabase(ThisWorkbook.Path & & 学生档案.MDB)RS1 = DB1.OpenRecordset(Name:=档案,

10、Type:=dbOpenDynaset)With RS1.AddNew.Fields(姓名).Value = Me.TextBox1.Value.Fields(年龄).Value = Me.TextBox2.Value.Fields(性别).Value = Me.TextBox4.Value.Fields(籍贯).Value = Me.TextBox5.Value.Fields(联系电话).Value = Me.TextBox6.Value.UpdateMsgBox 档案表中增加了一条记录!End WithDB1.CloseExit Sub4：实现修改指定记录功能On Error GoTo 1

11、00If TextBox1.Text = ThenMsgBox 请输入姓名, 1 + 16, 系统提示TextBox1.SetFocusElseDim RS1 As RecordsetDim DB1 As DatabaseSet DB1 = OpenDatabase(ThisWorkbook.Path & & 学生档案.MDB)Set RS1 = DB1.OpenRecordset(Name:=档案, Type:=dbOpenDynaset)RS1.FindFirst 姓名= & TextBox1.Value & RS1.EditRS1.Fields(年龄).Value = TextBox2.

12、ValueRS1.Fields(性别).Value = TextBox4.ValueRS1.Fields(籍贯).Value = TextBox5.ValueRS1.Fields(联系电话).Value = TextBox6.ValueRS1.UpdateRS1.CloseSet RS1 = NothingSet DB1 = NothingEnd IfExit Sub100: MsgBox 找不到符合条件的记录, 1 + 64, 系统提示5：实现删除指定记录功能On Error GoTo 100If TextBox1.Text = ThenMsgBox 请输入姓名, 1 + 16, 系统提示T

13、extBox1.SetFocusElseDim RS1 As RecordsetDim DB1 As DatabaseSet DB1 = OpenDatabase(ThisWorkbook.Path & & 学生档案.MDB)Set RS1 = DB1.OpenRecordset(Name:=档案, Type:=dbOpenDynaset)RS1.FindFirst 姓名= & TextBox1.Value & RS1.DeleteRS1.Update RS1.CloseSet RS1 = NothingSet DB1 = NothingEnd IfExit Sub100: MsgBox 找不

14、到符合条件的记录, 1 + 64, 系统提示6：实现排序功能例：查询结果按年龄字段排序strSQL = Select * from 档案 WHERE 性别 LIKE & ComboBox3.Value & 改为升序排列（默认）strSQL = Select * from 档案 WHERE 籍贯 LIKE & ComboBox3.Value & ORDER BY 年龄降序排列strSQL = Select * from 档案 WHERE 籍贯 LIKE & ComboBox2.Value & ORDER BY 年龄DESC7：实现分类汇总功能Dim CNN As New ADODB.ConnectionDim RST As New ADODB.RecordsetDim Stpath, strSQL As StringStpath = ThisWorkbook.Path & Application.PathSeparator & 学生档案.mdbCNN.Open provider=Microsoft.jet.OLEDB.4.0;data source= & S