vba数据库编程答案.doc

1、VBA 数据库编程技术实验目的1、 掌握 connection 对象2、掌握 Recordset 对象3、掌握 Command 对象实验题目9-1 connection 对象的具体连接方法：方法 1Dim conn As ADODB.Connection 定义对象类型Set conn = New ADODB.Connection 将对象初始化conn.ConnectionString= “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/access/vba 数据库编程技术/学生成绩管理.mdb“ 设置连接字符信息conn.open 连接对象打开.

2、conn.Close 关闭连接对象Set conn = Nothing 将连接对象清空方法 2Dim conn As ADODB.Connection 定义对象类型Set conn = New ADODB.Connection 将对象初始化conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/access/vba 数据库编程技术/学生成绩管理.mdb;UID=;PWD=“.conn.CloseSet conn = Nothing方法 3Dim conn As ADODB.Connection 定义对象类型Set conn = Ne

3、w ADODB.Connection 将对象初始化conn.Open CurrentProject.Connection 打开当前正在连接的数据.conn.CloseSet conn = Nothing9-2 在 9-1 的基础上，打开表单学生信息，利用 Recordset 对象获取来自“学生成绩管理.mdb”中“学生”数据表的记录，并显示第一条记录。Private Sub Form_Load()Dim conn As ADODB.Connection 定义连接对象类型Dim rs As ADODB.Recordset 定义记录集对象类型Set rs = New ADODB.Recordset

4、 将记录集对象初始化Set conn = New ADODB.Connection 将连接对象初始化conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/access/vba 数据库编程技术/ 学生成绩管理.mdb;“rs.Open “select * from 学生“, conn, adOpenKeyset, adLockReadOnlyrs.MoveFirstText0.Value = rs(“姓名“)Text2.Value = rs(“学号“)Text4.Value = rs(“性别“)conn.CloseSet conn

5、= NothingEnd Sub9-3 打开表单增加专业，单击增加按钮增加一条关于专业的的新记录：专业编号：p07，专业名称：电子技术，专业负责人：刘容强。Private Sub Command0_Click()Dim conn As ADODB.Connection 定义连接对象类型Dim rs As ADODB.Recordset 定义记录集对象类型Set rs = New ADODB.Recordset 将记录集对象初始化Set conn = New ADODB.Connection 将连接对象初始化conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;

6、Data Source=E:/access/vba 数据库编程技术/ 学生成绩管理.mdb;“rs.Open “select * from 专业“, conn, adOpenKeyset, adLockOptimisticrs.AddNewrs(“专业编号 “) = “P07“rs(“专业名称 “) = “电子技术“rs(“专业负责人“) = “刘容强“rs.UpdateMsgBox “已完成新记录的添加“, 0 + 32, “提示“conn.CloseSet conn = NothingEnd Sub9-5 新建一个过程 a,将“专业”数据表中专业名称为“公共基础教学 ”的专业负责人姓名改为

7、“郑智强” 。Public Sub a()Dim conn As ADODB.Connection 定义对象类型Set conn = New ADODB.Connection 将对象初始化conn.Open CurrentProject.Connection 打开当前正在连接的数据Dim comm As ADODB.CommandSet comm = New ADODB.Commandcomm.ActiveConnection = conncomm.CommandText = “update 专业 set 专业负责人=郑智强 where 专业名称=公共基础教学“comm.ExecuteMsgB

8、ox “已完成修改 “, 0 + 32, “提示“conn.CloseSet conn = NothingSet comm = NothingEnd Sub9-6 打开“学生成绩管理.mdb” ，设计课程成绩统计窗体，运行界面如图 9-5 所示，要求实现：1、在组合框 combo1 中选择一课程编号，则对应的课程名称、学分、任课教师、学时分别显示在对应文本框中2、单击“统计按钮” ，则在对应文本框中显示指定课程的所有参考学生人数、课程平均分、60 分以上人数和不及格人数。3、若未指定具体课程编号就单击“统计”按钮，则显示提示信息。Option Compare DatabasePrivate S

9、ub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select 课程名称,学时,学分,姓名 from 课程,教师 where 课程.教师编号=教师.教师编号 and 课程编号=“ & Combo0 & “rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(“课程名称“)Text6 = rs(“学分“)Text8 = rs(“姓名“)Text1

10、0 = rs(“学时“)End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select * from 成绩 where 课程编号=“ & Combo0 & “rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.BOF() Or Not rs.EOF() Thensum = 0n = 0x

11、= 0y = 0Do While Not rs.EOF()sum = sum + rs(“成绩“)n = n + 1If rs(“成绩“) = 60 Thenx = x + 1Elsey = y + 1End Ifrs.MoveNextLoopaver = sum / nText15.Value = nText17.Value = Int(aver * 100 + 0.5) / 100Text19.Value = xText21.Value = yElseMsgBox “课程编号为空，请重新选择“, 0 + 16, “提示“End Ifrs.CloseSet rs = NothingEnd S

12、ub9-7 打开“学生成绩管理.mdb ”，设计按课程查阅学生成绩窗体，运行界面如图 9-7 所示，要求实现如下：Public rs As ADODB.RecordsetPrivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select 课程名称,学时,学分,姓名 from 课程,教师 where 课程.教师编号=教师.教师编号 and 课程编号=“ & Combo0 & “rs.Open sqlstr, CurrentProje

13、ct.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(“课程名称“)Text6 = rs(“学分“)Text8 = rs(“姓名“)Text10 = rs(“学时“)End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Set rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select 学生.学号,姓名,专业名称,成绩 from 学生,专业,成绩 where 学生.专业编号=专业.专业编号 “ & _“and

14、成绩.学号= 学生.学号 and 成绩.课程编号=“ & Combo0.Value & “rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.BOF() Or Not rs.EOF() ThenText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Command23.Enabled = TrueCommand24.Enabled = TrueCommand25.Enabled = TrueComman

15、d26.Enabled = TrueElseMsgBox “课程号为空 ,请重新指定课程编号“, 0 + 16, “提示“End IfEnd SubPrivate Sub Command23_Click()rs.MoveFirstText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() ThenText15.Value = rs(“学

16、号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Elsers.MoveNextText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)MsgBox “已经是首记录 “, 0 + 64, “提示“End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() ThenText15.Value =

17、 rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Elsers.MovePreviousText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)MsgBox “已经是未记录 “, 0 + 64, “提示“End IfEnd SubPrivate Sub Command26_Click()rs.MoveLastText15.Value = rs(“学号“)T

18、ext17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)End SubPrivate Sub Form_Activate()Command23.Enabled = FalseCommand24.Enabled = FalseCommand25.Enabled = FalseCommand26.Enabled = FalseEnd Sub9-8 在例 9-7 基础上，增加 “更新” 、 “删除” 、 “新增”和“结束”4 个命令按钮，运行界面如下图所示：Public rs As ADODB.RecordsetPr

19、ivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select 课程名称,学时,学分,姓名 from 课程,教师 where 课程.教师编号=教师.教师编号 and 课程编号=“ & Combo0 & “rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(“课程名称“)Text6 = rs(“学分“)Text8 = rs(“姓名

20、“)Text10 = rs(“学时“)End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Set rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select 学生.学号,姓名,专业名称,成绩 from 学生,专业,成绩 where 学生.专业编号=专业.专业编号 “ & _“and 成绩.学号= 学生.学号 and 成绩.课程编号=“ & Combo0.Value & “rs.Open sqlstr, CurrentProject.Connection,

21、 2, 2If Not rs.BOF() Or Not rs.EOF() ThenText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Command23.Enabled = TrueCommand24.Enabled = TrueCommand25.Enabled = TrueCommand26.Enabled = TrueCommand27.Enabled = TrueCommand28.Enabled = TrueCommand29.Enabled = T

22、rueElseMsgBox “课程号为空 ,请重新指定课程编号“, 0 + 16, “提示“End IfEnd SubPrivate Sub Command23_Click()rs.MoveFirstText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() ThenText15.Value = rs(“学号“)Text17.Value

23、 = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Elsers.MoveNextText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)MsgBox “已经是首记录 “, 0 + 64, “提示“End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() ThenText15.Value = rs(“学号“)Text17

24、.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)Elsers.MovePreviousText15.Value = rs(“学号“)Text17.Value = rs(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)MsgBox “已经是未记录 “, 0 + 64, “提示“End IfEnd SubPrivate Sub Command26_Click()rs.MoveLastText15.Value = rs(“学号“)Text17.Value = r

25、s(“姓名“)Text19.Value = rs(“专业名称“)Text21.Value = rs(“成绩“)End SubEnd SubPrivate Sub Command27_Click()flag = 0yn = MsgBox(“确定更新成绩吗？“, 1 + 32, “提问“)If yn = 1 ThenSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select * from 成绩 “rs.Open sqlstr, CurrentProject.Connection, 2, 2Do While Not rs.EOF(

26、) And flag = 0If rs(“学号“) = Trim(Text15.Value) And rs(“课程编号“) = Trim(Combo0.Value) Thenrs(“成绩“) = Text21.Valuers.UpdateMsgBox “完成成绩更新！“, 0 + 64, “提示“flag = 1Elsers.MoveNextEnd IfLoopIf flag = 0 ThenMsgBox “学号或课程编号有变化，无法进行成绩更新！“, 0 + 16, “提示“End Ifrs.CloseSet rs = NothingEnd IfEnd SubPrivate Sub Comm

27、and28_Click()flag = 0yn = MsgBox(“确定删除本记录吗？“, 1 + 32, “提问“)If yn = 1 ThenSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = “select * from 成绩 “rs.Open sqlstr, CurrentProject.Connection, 2, 2Do While Not rs.EOF() And flag = 0If rs(“学号“) = Trim(Text15.Value) And rs(“课程编号“) = Trim(Combo0.Value) Thenrs.Deleters.UpdateMsgBox “已完成删除！“, 0 + 64, “提示“