第第9 9章章 VBAVBA数据库编程数据库编程Access数据库程序设计数据库程序设计授课教师：邓广彪授课教师：邓广彪联系电话：联系电话：电子邮箱：电子邮箱：9.1.1 VBA常见操作常见操作 1 1、打开和关闭操作、打开和关闭操作 (1) (1) 打开窗体：打开窗体：DoCmd.OpenForm DoCmd.OpenForm 窗体名窗体名 (2) (2) 打开报表：打开报表：DoCmd.OpenReport DoCmd.OpenReport 报表名报表名 (3) (3) 关闭操作：关闭操作：DoCmd.Close DoCmd.Close 对象类型对象类型, ,对象名对象名 2 2、输入框、输入框 格式：格式：inputbox(inputbox(显示文字显示文字,标题文字标题文字,默认默认文字文字) 说明：显示文字必须有，其他可以省略。说明：显示文字必须有，其他可以省略。全国二级ACCESS教程VBA数据库编程 3 3、消息框、消息框 格式：格式：msgbox(msgbox(提示文字提示文字,按钮类型按钮类型,标题文字标题文字) 说明：提示文字必须有，其他可以省略。说明：提示文字必须有，其他可以省略。 4 4、VBAVBA验证函数验证函数 在控件中的数据被改变之前或记录数据被更新之前在控件中的数据被改变之前或记录数据被更新之前会发生会发生BeforeUpdateBeforeUpdate事件。事件。 要记得表要记得表10-610-6的验证函数名及说明。的验证函数名及说明。全国二级ACCESS教程VBA数据库编程 5 5、计时事件（、计时事件（TimerTimer） Timer Timer事件表示每隔指定的一段时间自动执行的代事件表示每隔指定的一段时间自动执行的代码。码。VBAVBA中没有提供计时控件，一般是中没有提供计时控件，一般是给窗体的给窗体的TimerTimer事事件添加计时的时候执行的代码件添加计时的时候执行的代码，然后通过，然后通过设置设置TimerIntervalTimerInterval属性属性的值确定多久执行一次的值确定多久执行一次( (以毫秒为单以毫秒为单位位)Timer)Timer事件代码并马上开始按间隔执行事件代码并马上开始按间隔执行TimerTimer事件代事件代码，如果设置码，如果设置TimerIntervalTimerInterval的值为的值为0 0则停止执行则停止执行TimerTimer事件。事件。全国二级ACCESS教程VBA数据库编程 例如：制作好下图所示的窗体，要求：按每隔例如：制作好下图所示的窗体，要求：按每隔100100毫秒进行计时，开始时暂停按钮不能使用，正在计时过毫秒进行计时，开始时暂停按钮不能使用，正在计时过程计时按钮不能使用，暂停时暂停按钮不能使用。其中程计时按钮不能使用，暂停时暂停按钮不能使用。其中文本框的名称为文本框的名称为t t，计时按钮的名称为，计时按钮的名称为playplay，暂停按钮，暂停按钮的名称为的名称为pausepause，给窗体、计时按钮、暂停按钮、，给窗体、计时按钮、暂停按钮、TimerTimer事件添加的代码如下页所示。事件添加的代码如下页所示。全国二级ACCESS教程VBA数据库编程Private Sub play_Click()Private Sub play_Click() 计时按钮单击代码计时按钮单击代码Me!pause.Enabled = True Me!pause.Enabled = True 设置暂停按钮可用设置暂停按钮可用pause.SetFocus pause.SetFocus 将光标设置到暂停按钮将光标设置到暂停按钮Me!play.Enabled = False Me!play.Enabled = False 将计时按钮设为不可用将计时按钮设为不可用TimerInterval = 100 TimerInterval = 100 设置每隔设置每隔100100毫秒执行一次毫秒执行一次TimerTimer事件事件End SubEnd SubPrivate Sub Form_Load()Private Sub Form_Load() 窗体窗体loadload事件代码事件代码Me!pause.Enabled = FalseMe!pause.Enabled = FalseEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程Private Sub Form_Timer()Private Sub Form_Timer() 窗体窗体timertimer事件代码事件代码Static count As Double Static count As Double 定义为静态的使其能保留上一次计时的结果定义为静态的使其能保留上一次计时的结果count = count + 0.1count = count + 0.1t = countt = countEnd SubEnd SubPrivate Sub pause_Click()Private Sub pause_Click() 暂停按钮单击事件代码暂停按钮单击事件代码Me!play.Enabled = TrueMe!play.Enabled = Trueplay.SetFocusplay.SetFocusMe!pause.Enabled = FalseMe!pause.Enabled = FalseTimerInterval = 0TimerInterval = 0End SubEnd Sub全国二级ACCESS教程VBA数据库编程9.1.2 VBA的数据库编程的数据库编程 在在VBAVBA中提供了中提供了3 3种数据库访问接口：种数据库访问接口： 1 1、开放式数据库、开放式数据库ODBCODBC 2 2、数据访问对象、数据访问对象DAODAO 3 3、ActiveXActiveX数据对象数据对象ADOADO DAODAO中的对象：中的对象：DBEngineDBEngine、WorkspaceWorkspace、DatabaseDatabase、RecordSetRecordSet、FieldField、QueryDefQueryDef、ErrorError。全国二级ACCESS教程VBA数据库编程 在在VBAVBA中可以通过编程来对表中的数据进行操作。中可以通过编程来对表中的数据进行操作。在操作过程中主要使用的是数据访问对象在操作过程中主要使用的是数据访问对象DAODAO或或ADODBADODB提供的方法来操作，其主要的操作步骤为：提供的方法来操作，其主要的操作步骤为： 1 1、定义对应的变量。、定义对应的变量。 2 2、打开数据库或连接到数据库。、打开数据库或连接到数据库。 3 3、打开表或执行、打开表或执行SQLSQL语句将记录集保存起来。语句将记录集保存起来。 4 4、读取记录集中的字段保存到变量中。、读取记录集中的字段保存到变量中。 5 5、循环读取字段中的每一个值进行汇总、更新、循环读取字段中的每一个值进行汇总、更新、添加、删除的操作。添加、删除的操作。 6 6、关闭记录集及关闭数据库。、关闭记录集及关闭数据库。全国二级ACCESS教程VBA数据库编程 1 1、定义对应的变量：、定义对应的变量：主要代码如下主要代码如下 Dim Dim dbdb As DAO.Database As DAO.Database 定义变量定义变量dbdb用来保存要使用的数据库用来保存要使用的数据库 Dim Dim rsrs As DAO.Recordset As DAO.Recordset 定义变量定义变量rsrs用来保存要使用的记录集用来保存要使用的记录集 Dim Dim mcmc As DAO.Field As DAO.Field 定义字段变量用来保存某个字段的所有值定义字段变量用来保存某个字段的所有值 Dim Dim numnum As DAO.Field As DAO.Field 如果要读取多个字段的值则需要定义多个如果要读取多个字段的值则需要定义多个DAO.FieldDAO.Field类型的字段变量类型的字段变量全国二级ACCESS教程VBA数据库编程 2 2、打开数据库或连接到数据库。、打开数据库或连接到数据库。 Set db = CurrentDb() Set db = CurrentDb() 设置数据库为当前使用的数据库设置数据库为当前使用的数据库 3 3、打开表或执行、打开表或执行SQLSQL语句将记录集保存起来。语句将记录集保存起来。 Set rs = db.OpenRecordset(Set rs = db.OpenRecordset(班级信息表班级信息表) ) 打开表或查询的内容保存到记录集中，双引打开表或查询的内容保存到记录集中，双引号中的为要打开的表，也可以是一条号中的为要打开的表，也可以是一条SQLSQL语句。语句。全国二级ACCESS教程VBA数据库编程 4 4、读取记录集中的字段保存到变量中。、读取记录集中的字段保存到变量中。 Set Set mcmc = rs.Fields( = rs.Fields(班级名称班级名称) 读取字段的所有值保存到字段变量中，并指读取字段的所有值保存到字段变量中，并指向第一个值向第一个值 Set Set numnum = rs.Fields( = rs.Fields(人数人数) 如果要读取多个字段的值，每个字段都需要这如果要读取多个字段的值，每个字段都需要这样读样读全国二级ACCESS教程VBA数据库编程 5 5、循环读取字段中的每一个值进行汇总、更新、循环读取字段中的每一个值进行汇总、更新、添加、删除的操作。添加、删除的操作。 Do While Do While Not rs.EOF Not rs.EOF 还没读到最后一条记录还没读到最后一条记录 对读取得到的记录进行操作对读取得到的记录进行操作 rs.MoveNextrs.MoveNext 读取下一条记录读取下一条记录 Loop Loop 上述红色的两个地方一般都是必须的。上述红色的两个地方一般都是必须的。全国二级ACCESS教程VBA数据库编程 举例：读取举例：读取“班级信息表班级信息表”中的所有班级名称并提中的所有班级名称并提示。具体代码如下：示。具体代码如下：Sub abc()Sub abc() Dim db As DAO.Database Dim db As DAO.Database Dim rs As DAO.Recordset Dim rs As DAO.Recordset Dim mc As DAO.Field Dim mc As DAO.Field Set db = CurrentDb() Set db = CurrentDb() Set rs = db.OpenRecordset( Set rs = db.OpenRecordset(班级信息表班级信息表) Set mc = rs.Fields( Set mc = rs.Fields(班级名称班级名称) Do While Not rs.EOF Do While Not rs.EOF MsgBox mc MsgBox mc rs.MoveNext rs.MoveNext Loop Loop rs.Close rs.Close db.Close db.CloseEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程 如果读取的是数值型字段的值，可以用保存该字如果读取的是数值型字段的值，可以用保存该字段的变量直接与一个数值型变量进行运算。段的变量直接与一个数值型变量进行运算。 举例：循环读取举例：循环读取“班级信息表班级信息表”中中“人数人数”字段字段的值，将所有的人数之和保存到变量的值，将所有的人数之和保存到变量sumsum中。中。 代码见下一张幻灯片。代码见下一张幻灯片。全国二级ACCESS教程VBA数据库编程Sub abc()Sub abc() Dim db As DAO.Database Dim db As DAO.Database Dim rs As DAO.Recordset Dim rs As DAO.Recordset Dim num As DAO.FieldDim num As DAO.Field Dim sum As Integer Dim sum As Integer Set db = CurrentDb() Set db = CurrentDb() Set rs = db.OpenRecordset( Set rs = db.OpenRecordset(班级信息表班级信息表) Set num = rs.Fields(Set num = rs.Fields(人数人数) Do While Not rs.EOF Do While Not rs.EOF sum = sum + numsum = sum + num rs.MoveNext rs.MoveNext Loop Loop MsgBox sum MsgBox sum rs.Clone rs.Clone db.Close db.CloseEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程 如果想对如果想对当前读取到的记录当前读取到的记录进行修改，可以使用进行修改，可以使用“字段变量字段变量 = = 值值”的格式进行修改，但在修改前必须的格式进行修改，但在修改前必须要使用要使用rsrs.Edit.Edit表示允许进行修改，修改后必须使用表示允许进行修改，修改后必须使用rsrs.Update.Update进行更新。进行更新。 举例：对举例：对“班级信息表班级信息表”中中“班级名称班级名称”以以“艺艺术术”开头的班级，将人数增加开头的班级，将人数增加100100。 代码见下一张幻灯片。代码见下一张幻灯片。全国二级ACCESS教程VBA数据库编程Sub abc()Sub abc() Dim db As DAO.Database Dim db As DAO.Database Dim rs As DAO.Recordset Dim rs As DAO.Recordset Dim mc As DAO.Field Dim mc As DAO.Field Dim num As DAO.Field Dim num As DAO.Field Set db = CurrentDb() Set db = CurrentDb() Set rs = db.OpenRecordset( Set rs = db.OpenRecordset(班级信息表班级信息表) Set mc = rs.Fields( Set mc = rs.Fields(班级名称班级名称) Set num = rs.Fields( Set num = rs.Fields(人数人数) Do While Not rs.EOF Do While Not rs.EOF If mc Like If mc Like 艺术艺术* Then* Then rs.Editrs.Edit num = num + 100 num = num + 100 rs.Update rs.Update End If End If rs.MoveNext rs.MoveNext Loop Loop rs.Clone rs.Clone db.Close db.CloseEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程 如果想对如果想对当前读取到的记录当前读取到的记录进行删除，进行删除，rsrs. .DeleteDelete进行删除。进行删除。 举例：删除举例：删除“班级信息表班级信息表”中中“人数人数”大于大于5050的记的记录。录。 代码见下一张幻灯片。代码见下一张幻灯片。全国二级ACCESS教程VBA数据库编程Sub abc()Sub abc()Dim db As DAO.DatabaseDim db As DAO.Database Dim rs As DAO.Recordset Dim rs As DAO.Recordset Dim num As DAO.FieldDim num As DAO.Field Set db = CurrentDb() Set db = CurrentDb() Set rs = db.OpenRecordset( Set rs = db.OpenRecordset(班级信息表班级信息表) Set num = rs.Fields(Set num = rs.Fields(人数人数) Do While Not rs.EOF Do While Not rs.EOF If num 50 ThenIf num 50 Then rs.Deleters.Delete End If End If rs.MoveNext rs.MoveNext Loop Loop rs.Clo rs.Clos se e db.Close db.CloseEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程 对记录集对记录集rsrs还有如下操作：还有如下操作： rs.AddNewrs.AddNew：添加一条新记录：添加一条新记录 rs.MoveFirstrs.MoveFirst：移动到记录集的第一条记录：移动到记录集的第一条记录 rs.MoveLastrs.MoveLast：移动掉记录集的最后一条记录：移动掉记录集的最后一条记录 rs.MovePreviousrs.MovePrevious：往上移动一条记录：往上移动一条记录 在考试过程中，近几年的填空题都是考对在考试过程中，近几年的填空题都是考对rsrs记录记录集的操作，如集的操作，如rs.EOFrs.EOF、rs.updaters.update、rs.MoveNextrs.MoveNext。所。所以在考试时要注意看循环的条件填以在考试时要注意看循环的条件填rs.EOFrs.EOF还是还是Not Not rs.EOFrs.EOF，有更新记录的就填，有更新记录的就填rs.update(rs.update(但之前必须有但之前必须有rs.edit)rs.edit)，循环中没有，循环中没有rs.MoveNextrs.MoveNext就填它。就填它。全国二级ACCESS教程VBA数据库编程 5 5、几个特殊域聚合函数及、几个特殊域聚合函数及RunSQLRunSQL (2) (2) 了解并记住了解并记住DCountDCount、DavgDavg、DSumDSum、DMaxDMax、DMinDMin函数的功能，使用格式均如下：函数的功能，使用格式均如下： 函数名函数名(字段名字段名, , 表或查询的名称表或查询的名称,条件条件) 说明：其中条件可以省略，条件的写法为：字段说明：其中条件可以省略，条件的写法为：字段名名 符号符号 值。值。 功能：从对应的表或查询中，对满足条件的指定功能：从对应的表或查询中，对满足条件的指定字段做对应函数功能的统计。字段做对应函数功能的统计。如：如：Dcount(StuID,StuInfo)Dcount(StuID,StuInfo)DAvg(DAvg(成绩成绩,学生成绩表学生成绩表,课程名课程名= = JavaJava )DMax(DMax(成绩成绩,学生成绩表学生成绩表,StuID=110004),StuID90)90)DLookup(StuName,StuInfo,StuFrom=DLookup(StuName,StuInfo,StuFrom=广西北海广西北海)全国二级ACCESS教程VBA数据库编程 （4 4）DoCmdDoCmd对象的对象的RunSQLRunSQL方法：方法：运行执行参数中运行执行参数中的的SQLSQL语句，这个语句，这个SQLSQL语句可以是语句可以是selectselect、updateupdate等等，等等，使用格式如下：使用格式如下： DoCmd.RunSQL(DoCmd.RunSQL(字符串型的字符串型的SQLSQL语句语句) )全国二级ACCESS教程VBA数据库编程补充知识：双重循环的执行补充知识：双重循环的执行 在循环结构中，可以在一个循环的循环体中嵌套在循环结构中，可以在一个循环的循环体中嵌套另外一个完整的循环，另外一个完整的循环，执行时是如果外的条件成立执行时是如果外的条件成立，则执行循环体，由于外循环的循环体中又包含另外一则执行循环体，由于外循环的循环体中又包含另外一个循环个循环( (称为内循环称为内循环) )，所以需要把内循环完整的执行所以需要把内循环完整的执行一次一次。只有外循环的条件不成立才会结束循环语句的。只有外循环的条件不成立才会结束循环语句的执行。执行。全国二级ACCESS教程VBA数据库编程 分析如下代码，考虑为什么提示的结果是分析如下代码，考虑为什么提示的结果是5555。Sub abc()Sub abc() Dim sum, i, j As Integer Dim sum, i, j As Integer sum = sum = 1 1 For i = 2 To 4For i = 2 To 4 For j = 1 To 3For j = 1 To 3 sum = sum + i * j sum = sum + i * j Next Next Next Next MsgBox sum MsgBox sumEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程 分析如下代码，考虑为什么提示的结果是分析如下代码，考虑为什么提示的结果是2525。Sub abc()Sub abc() Dim sum, i, j As Integer Dim sum, i, j As Integer For i = 2 To 4For i = 2 To 4 sum = 1sum = 1 For j = 1 To 3For j = 1 To 3 sum = sum + i * j sum = sum + i * j Next Next Next Next MsgBox sum MsgBox sumEnd SubEnd Sub全国二级ACCESS教程VBA数据库编程