《vba数据库编程答案(共12页).doc》由会员分享，可在线阅读，更多相关《vba数据库编程答案(共12页).doc（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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 = New
3、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 = NothingEnd Sub9-
5、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;Data Source=E:/acce
6、ss/vba数据库编程技术/学生成绩管理.mdb;rs.Open select * from 专业, conn, adOpenKeyset, adLockOptimisticrs.AddNewrs(专业编号) = P07rs(专业名称) = 电子技术rs(专业负责人) = 刘容强rs.UpdateMsgBox 已完成新记录的添加, 0 + 32, 提示conn.CloseSet conn = NothingEnd Sub9-5新建一个过程a,将“专业”数据表中专业名称为“公共基础教学”的专业负责人姓名改为“郑智强”。Public Sub a()Dim conn As ADODB.Connect
7、ion 定义对象类型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.ExecuteMsgBox 已完成修改, 0 + 32, 提示conn.CloseSet conn = Nothing
8、Set comm = NothingEnd Sub9-6打开“学生成绩管理.mdb”，设计课程成绩统计窗体，运行界面如图9-5所示，要求实现：1、在组合框combo1中选择一课程编号，则对应的课程名称、学分、任课教师、学时分别显示在对应文本框中2、单击“统计按钮”，则在对应文本框中显示指定课程的所有参考学生人数、课程平均分、60分以上人数和不及格人数。3、若未指定具体课程编号就单击“统计”按钮，则显示提示信息。Option Compare DatabasePrivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB
9、.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(姓名)Text10 = rs(学时)End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_C
10、lick()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 = 0y = 0Do While Not rs.EOF()sum = sum + rs(成绩)n = n + 1If rs(成绩) = 60 Then
11、 x = x + 1Else y = y + 1End Ifrs.MoveNextLoopaver = sum / nText15.Value = nText17.Value = Int(aver * 100 + 0.5) / 100Text19.Value = xText21.Value = yElse MsgBox 课程编号为空，请重新选择, 0 + 16, 提示End Ifrs.CloseSet rs = NothingEnd Sub9-7 打开“学生成绩管理.mdb”，设计按课程查阅学生成绩窗体，运行界面如图9-7所示，要求实现如下：Public rs As ADODB.Records
12、etPrivate 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(姓名)Tex
13、t10 = 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, 2, 2If Not rs
14、.BOF() Or Not rs.EOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) Command23.Enabled = True Command24.Enabled = True Command25.Enabled = True Command26.Enabled = TrueElse MsgBox 课程号为空,请重新指定课程编号, 0 + 16, 提示End IfEnd SubPrivate Sub Command23_Click() r
15、s.MoveFirst Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)Else rs.MoveNext Text15.Value = rs(学号)
16、 Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) MsgBox 已经是首记录, 0 + 64, 提示End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)Else rs.MovePrevious Text15.Value = rs(学号)
17、Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) MsgBox 已经是未记录, 0 + 64, 提示End IfEnd SubPrivate Sub Command26_Click() rs.MoveLast Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)End SubPrivate Sub Form_Activate()Command23.Enabled = FalseComm
18、and24.Enabled = FalseCommand25.Enabled = FalseCommand26.Enabled = FalseEnd Sub9-8 在例9-7基础上，增加“更新”、“删除”、“新增”和“结束”4个命令按钮，运行界面如下图所示：Public rs As ADODB.RecordsetPrivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select 课程名称,学时,学分,姓名 from 课程,教师 wh
19、ere 课程.教师编号=教师.教师编号 and 课程编号= & Combo0 & rs.Open sqlstr, CurrentProject.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 学生.
20、学号,姓名,专业名称,成绩 from 学生,专业,成绩 where 学生.专业编号=专业.专业编号 & _and 成绩.学号=学生.学号 and 成绩.课程编号= & Combo0.Value & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.BOF() Or Not rs.EOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) Command23.Enabled = True Co
21、mmand24.Enabled = True Command25.Enabled = True Command26.Enabled = True Command27.Enabled = True Command28.Enabled = True Command29.Enabled = TrueElse MsgBox 课程号为空,请重新指定课程编号, 0 + 16, 提示End IfEnd SubPrivate Sub Command23_Click() rs.MoveFirst Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value =
22、 rs(专业名称) Text21.Value = rs(成绩)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)Else rs.MoveNext Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) M
23、sgBox 已经是首记录, 0 + 64, 提示End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() Then Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)Else rs.MovePrevious Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩) Ms
24、gBox 已经是未记录, 0 + 64, 提示End IfEnd SubPrivate Sub Command26_Click() rs.MoveLast Text15.Value = rs(学号) Text17.Value = rs(姓名) Text19.Value = rs(专业名称) Text21.Value = rs(成绩)End SubEnd SubPrivate Sub Command27_Click()flag = 0yn = MsgBox(确定更新成绩吗？, 1 + 32, 提问)If yn = 1 Then Set rs = New ADODB.Recordset Dim s
25、qlstr As String sqlstr = select * from 成绩 rs.Open sqlstr, CurrentProject.Connection, 2, 2 Do While Not rs.EOF() And flag = 0 If rs(学号) = Trim(Text15.Value) And rs(课程编号) = Trim(Combo0.Value) Then rs(成绩) = Text21.Value rs.Update MsgBox 完成成绩更新！, 0 + 64, 提示 flag = 1 Else rs.MoveNext End If Loop If flag
26、= 0 Then MsgBox 学号或课程编号有变化，无法进行成绩更新！, 0 + 16, 提示 End If rs.Close Set rs = NothingEnd IfEnd SubPrivate Sub Command28_Click()flag = 0yn = MsgBox(确定删除本记录吗？, 1 + 32, 提问)If yn = 1 Then Set rs = New ADODB.Recordset Dim sqlstr As String sqlstr = select * from 成绩 rs.Open sqlstr, CurrentProject.Connection, 2
27、, 2 Do While Not rs.EOF() And flag = 0 If rs(学号) = Trim(Text15.Value) And rs(课程编号) = Trim(Combo0.Value) Then rs.Delete rs.Update MsgBox 已完成删除！, 0 + 64, 提示 Text15.Value = Text17.Value = Text19.Value = Text21.Value = Command23.Enabled = False Command24.Enabled = False Command25.Enabled = False Command
28、26.Enabled = False flag = 1 Else rs.MoveNext End If Loop If flag = 0 Then MsgBox 学号或课程编号有变化，无法进行成绩更新！, 0 + 16, 提示 End If rs.Close Set rs = NothingEnd IfEnd SubPrivate Sub Command29_Click()cmark = 0smark = 0yn = MsgBox(确定新增成绩信息吗？, 1 + 32, 提问)If yn = 1 Then Set rs = New ADODB.Recordset Dim sqlstr As S
29、tring sqlstr = select * from 成绩 where 学号= & Trim(Text15.Value) & and 课程编号= & Combo0.Value & rs.Open sqlstr, CurrentProject.Connection, 2, 2 If Not rs.BOF() And Not rs.EOF() Then MsgBox 成绩表已有记录，无法再新增, 0 + 64, 提示 cmark = 1 End If rs.Close Set rs = Nothing If cmark = 0 Then Set rs = New ADODB.Recordset
30、 sqlstr = select * from 学生 where 学号= & Trim(Text15.Value) & rs.Open sqlstr, CurrentProject.Connection, 2, 2 If Not rs.BOF() And Not rs.EOF() Then smark = 1 Else MsgBox 新增学号不存在，无法新增, 0 + 64, 提示 End If rs.Close Set rs = Nothing If smark = 1 Then Set rs = New ADODB.Recordset sqlstr = select * from 成绩 r
31、s.Open sqlstr, CurrentProject.Connection, 2, 2 rs.AddNew rs(学号) = Trim(Text15.Value) rs(课程编号) = Trim(Combo0.Value) rs(成绩) = Text21.Value rs.Update MsgBox 完成新增操作, 0 + 64, 提示 rs.Close Set rs = Nothing Text15.Value = Text17.Value = Text19.Value = Text21.Value = Command23.Enabled = False Command24.Enabl
32、ed = False Command25.Enabled = False Command26.Enabled = False End If End IfEnd IfEnd SubPrivate Sub Form_Activate()Command23.Enabled = FalseCommand24.Enabled = FalseCommand25.Enabled = FalseCommand26.Enabled = FalseCommand27.Enabled = FalseCommand28.Enabled = FalseCommand29.Enabled = FalseEnd Sub专心-专注-专业