數(shù)據(jù)庫編程答案

1、VBA數(shù)據(jù)庫編程技術(shù)實(shí)驗(yàn)?zāi)康?、 掌握connection對象2、 掌握Recordset對象3、 掌握Command對象實(shí)驗(yàn)題目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數(shù)據(jù)庫編程技術(shù)/學(xué)生成績管理.mdb 設(shè)置連接字符信息conn.open 連接對象打開.conn.Close

2、 關(guān)閉連接對象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數(shù)據(jù)庫編程技術(shù)/學(xué)生成績管理.mdb;UID=;PWD=.conn.CloseSet conn = Nothing方法3Dim conn As ADODB.Connection 定義對象類型Set conn = New ADODB.Connect

3、ion 將對象初始化conn.Open CurrentProject.Connection 打開當(dāng)前正在連接的數(shù)據(jù).conn.CloseSet conn = Nothing9-2 在9-1的基礎(chǔ)上，打開表單學(xué)生信息，利用Recordset對象獲取來自“學(xué)生成績管理.mdb”中“學(xué)生”數(shù)據(jù)表的記錄，并顯示第一條記錄。Private Sub Form_Load()Dim conn As ADODB.Connection 定義連接對象類型Dim rs As ADODB.Recordset 定義記錄集對象類型Set rs = New ADODB.Recordset 將記錄集對象初始化Set conn

4、= New ADODB.Connection 將連接對象初始化conn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/access/vba數(shù)據(jù)庫編程技術(shù)/學(xué)生成績管理.mdb;rs.Open select * from 學(xué)生, conn, adOpenKeyset, adLockReadOnlyrs.MoveFirstText0.Value = rs(姓名)Text2.Value = rs(學(xué)號(hào))Text4.Value = rs(性別)conn.CloseSet conn = NothingEnd Sub9-3 打開表單增加專業(yè)，單擊

5、增加按鈕增加一條關(guān)于專業(yè)的的新記錄：專業(yè)編號(hào)：p07，專業(yè)名稱：電子技術(shù)，專業(yè)負(fù)責(zé)人：劉容強(qiáng)。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:/access/vba數(shù)據(jù)庫編程技術(shù)

6、/學(xué)生成績管理.mdb;rs.Open select * from 專業(yè), conn, adOpenKeyset, adLockOptimisticrs.AddNewrs(專業(yè)編號(hào)) = P07rs(專業(yè)名稱) = 電子技術(shù)rs(專業(yè)負(fù)責(zé)人) = 劉容強(qiáng)rs.UpdateMsgBox 已完成新記錄的添加, 0 + 32, 提示conn.CloseSet conn = NothingEnd Sub9-5新建一個(gè)過程a,將“專業(yè)”數(shù)據(jù)表中專業(yè)名稱為“公共基礎(chǔ)教學(xué)”的專業(yè)負(fù)責(zé)人姓名改為“鄭智強(qiáng)”。Public Sub a()Dim conn As ADODB.Connection 定義對象類型Set

7、 conn = New ADODB.Connection 將對象初始化conn.Open CurrentProject.Connection 打開當(dāng)前正在連接的數(shù)據(jù)Dim comm As ADODB.CommandSet comm = New ADODB.Commandcomm.ActiveConnection = conncomm.CommandText = update 專業(yè) set 專業(yè)負(fù)責(zé)人=鄭智強(qiáng) where 專業(yè)名稱=公共基礎(chǔ)教學(xué)comm.ExecuteMsgBox 已完成修改, 0 + 32, 提示conn.CloseSet conn = NothingSet comm = No

8、thingEnd Sub9-6打開“學(xué)生成績管理.mdb”，設(shè)計(jì)課程成績統(tǒng)計(jì)窗體，運(yùn)行界面如圖9-5所示，要求實(shí)現(xiàn)：1、在組合框combo1中選擇一課程編號(hào)，則對應(yīng)的課程名稱、學(xué)分、任課教師、學(xué)時(shí)分別顯示在對應(yīng)文本框中2、單擊“統(tǒng)計(jì)按鈕”，則在對應(yīng)文本框中顯示指定課程的所有參考學(xué)生人數(shù)、課程平均分、60分以上人數(shù)和不及格人數(shù)。3、若未指定具體課程編號(hào)就單擊“統(tǒng)計(jì)”按鈕，則顯示提示信息。Option Compare DatabasePrivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim

9、 sqlstr As Stringsqlstr = select 課程名稱,學(xué)時(shí),學(xué)分,姓名 from 課程,教師 where 課程.教師編號(hào)=教師.教師編號(hào) and 課程編號(hào)= & Combo0 & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(課程名稱)Text6 = rs(學(xué)分)Text8 = rs(姓名)Text10 = rs(學(xué)時(shí))End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Dim rs

10、As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select * from 成績 where 課程編號(hào)= & 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 x = x + 1Els

11、e y = y + 1End Ifrs.MoveNextLoopaver = sum / nText15.Value = nText17.Value = Int(aver * 100 + 0.5) / 100Text19.Value = xText21.Value = yElse MsgBox 課程編號(hào)為空，請重新選擇, 0 + 16, 提示End Ifrs.CloseSet rs = NothingEnd Sub9-7 打開“學(xué)生成績管理.mdb”，設(shè)計(jì)按課程查閱學(xué)生成績窗體，運(yùn)行界面如圖9-7所示，要求實(shí)現(xiàn)如下：Public rs As ADODB.RecordsetPrivate Sub

12、 Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select 課程名稱,學(xué)時(shí),學(xué)分,姓名 from 課程,教師 where 課程.教師編號(hào)=教師.教師編號(hào) and 課程編號(hào)= & Combo0 & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(課程名稱)Text6 = rs(學(xué)分)Text8 = rs(姓名)Text10 = rs(學(xué)時(shí))E

13、nd Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Set rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select 學(xué)生.學(xué)號(hào),姓名,專業(yè)名稱,成績 from 學(xué)生,專業(yè),成績 where 學(xué)生.專業(yè)編號(hào)=專業(yè).專業(yè)編號(hào) & _and 成績.學(xué)號(hào)=學(xué)生.學(xué)號(hào) and 成績.課程編號(hào)= & Combo0.Value & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.BOF() Or Not

14、 rs.EOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) Command23.Enabled = True Command24.Enabled = True Command25.Enabled = True Command26.Enabled = TrueElse MsgBox 課程號(hào)為空,請重新指定課程編號(hào), 0 + 16, 提示End IfEnd SubPrivate Sub Command23_Click() rs.MoveFirst T

15、ext15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)Else rs.MoveNext Text15.Value = rs(學(xué)號(hào)) Text17.Value

16、 = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) MsgBox 已經(jīng)是首記錄, 0 + 64, 提示End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)Else rs.MovePrevious Text15.Value = rs(學(xué)號(hào)) Text17.Value

17、= rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) MsgBox 已經(jīng)是未記錄, 0 + 64, 提示End IfEnd SubPrivate Sub Command26_Click() rs.MoveLast Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)End SubPrivate Sub Form_Activate()Command23.Enabled = FalseCommand24.Enabled

18、 = FalseCommand25.Enabled = FalseCommand26.Enabled = FalseEnd Sub9-8 在例9-7基礎(chǔ)上，增加“更新”、“刪除”、“新增”和“結(jié)束”4個(gè)命令按鈕，運(yùn)行界面如下圖所示：Public rs As ADODB.RecordsetPrivate Sub Combo0_Change()Dim rs As ADODB.RecordsetSet rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select 課程名稱,學(xué)時(shí),學(xué)分,姓名 from 課程,教師 where 課程.教師編號(hào)=教

19、師.教師編號(hào) and 課程編號(hào)= & Combo0 & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.EOF() ThenText4 = rs(課程名稱)Text6 = rs(學(xué)分)Text8 = rs(姓名)Text10 = rs(學(xué)時(shí))End Ifrs.CloseSet rs = NothingEnd SubPrivate Sub Command12_Click()Set rs = New ADODB.RecordsetDim sqlstr As Stringsqlstr = select 學(xué)生.學(xué)號(hào),姓名,專業(yè)名稱,成績

20、 from 學(xué)生,專業(yè),成績 where 學(xué)生.專業(yè)編號(hào)=專業(yè).專業(yè)編號(hào) & _and 成績.學(xué)號(hào)=學(xué)生.學(xué)號(hào) and 成績.課程編號(hào)= & Combo0.Value & rs.Open sqlstr, CurrentProject.Connection, 2, 2If Not rs.BOF() Or Not rs.EOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) Command23.Enabled = True Command24.Enabl

21、ed = True Command25.Enabled = True Command26.Enabled = True Command27.Enabled = True Command28.Enabled = True Command29.Enabled = TrueElse MsgBox 課程號(hào)為空,請重新指定課程編號(hào), 0 + 16, 提示End IfEnd SubPrivate Sub Command23_Click() rs.MoveFirst Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Tex

22、t21.Value = rs(成績)End SubPrivate Sub Command24_Click()rs.MovePreviousIf Not rs.BOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)Else rs.MoveNext Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) MsgBox 已經(jīng)是首記錄,

23、 0 + 64, 提示End IfEnd SubPrivate Sub Command25_Click()rs.MoveNextIf Not rs.EOF() Then Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)Else rs.MovePrevious Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績) MsgBox 已經(jīng)是未記錄,

24、0 + 64, 提示End IfEnd SubPrivate Sub Command26_Click() rs.MoveLast Text15.Value = rs(學(xué)號(hào)) Text17.Value = rs(姓名) Text19.Value = rs(專業(yè)名稱) Text21.Value = rs(成績)End SubEnd SubPrivate Sub Command27_Click()flag = 0yn = MsgBox(確定更新成績嗎？, 1 + 32, 提問)If yn = 1 Then Set rs = New ADODB.Recordset Dim sqlstr As Stri

25、ng sqlstr = select * from 成績 rs.Open sqlstr, CurrentProject.Connection, 2, 2 Do While Not rs.EOF() And flag = 0 If rs(學(xué)號(hào)) = Trim(Text15.Value) And rs(課程編號(hào)) = Trim(Combo0.Value) Then rs(成績) = Text21.Value rs.Update MsgBox 完成成績更新！, 0 + 64, 提示 flag = 1 Else rs.MoveNext End If Loop If flag = 0 Then MsgB

26、ox 學(xué)號(hào)或課程編號(hào)有變化，無法進(jìn)行成績更新！, 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, 2 Do While

27、Not rs.EOF() And flag = 0 If rs(學(xué)號(hào)) = Trim(Text15.Value) And rs(課程編號(hào)) = 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 Command26.Enabled =

28、False flag = 1 Else rs.MoveNext End If Loop If flag = 0 Then MsgBox 學(xué)號(hào)或課程編號(hào)有變化，無法進(jìn)行成績更新！, 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 String sqlstr = select * from 成績 where 學(xué)號(hào)= & Trim(Text15.Value) & and 課程編號(hào)= & 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