ado vba programming in access

《ado vba programming in access》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、ADOVBAProgramminginAccessWhyitsnotsimple1MSAccesshasa'built-in'databaseenginecalledJet–whichyoumightuseButyoumightinsteaduseaseparatedataserverLikeMSSQLServer,OracleorMySQLTheseworkslightlydifferentlyWhyitsnotsimple2Theactualdata(andserver)mightbeonthesamemachineth

2、atAccessisrunningonButitmightnotWhyitsnotsimple3Theactualdata(andserver)mightnotbearelationaldatabaseCouldbeawebpageorspreadsheetSoADO=ActiveDataObjectsisasingleobjectmodeltocoverallcasesthereforeprettyintricate(butcanbesimple)Hereweonlycover–runningfromVBAinAccess

3、usingalocalAccessdatabaseFundamentalobjectsConnectionRecordsetConnectionRepresentsasinglesessionwithadataprovider.Thesequenceis–SetupconnectionOpenconnectionDothingswiththedataClosetheconnectionRecordsetArecordsetisjustasetofrecords(rows)Openarecordset(throughaconn

4、ection)DosomethingwiththerowsClosetherecordsetSimpleexampleAnAccessdatabasehasatablecalledmyTableandakeyfieldcalledIDThefollowingcode(inabuttononaform)goesthroughthetableanddisplaysalltehIDsSimpleexample:part1'declareconntobeaConnection-DimconnAsADODB.Connection'ma

5、keaconnectionobject-Setconn=NewADODB.Connection'specifywhatkindofdataprovideritis-conn.Provider="Microsoft.Jet.OLEDB.4.0"'opentheconnectionononedatabase-conn.Open"c:/walter/ass21.mdb"'declarearecordset-DimmyTableRSAsADODB.Recordset'makeone-SetmyTableRS=NewADODB.Rec

6、ordset'openitusingatableinthedatabase,andtheconnectionmyTableRS.Open"myTable",conn,adOpenDynamic,adLockPessimisticSimpleexample:2'gotostartofrecordset-myTableRS.MoveFirst'untilwereachtheend..DoUntilmyTableRS.EOF'displaytheIDfieldincurrentrowMsgBox(myTableRS.Fields(

7、"ID"))'movenextrowmyTableRS.MoveNextLoop'closetherecordsetmyTableRS.CloseSetmyTableRS.ActiveConnection=Nothing'andtheconnectionconn.CloseSetconn=NothingReadingatableMakeadatabaseandatablewithanumericfieldandatextfield.Putinafewrows.Writearoutineliketheaboveexample,

8、tototalthenumericfieldanddisplayitwithaMsgBoxFindFindMethod(fromMicrosoftHelpfile..)SearchesaRecordsetfortherowthatsatisfiesthespecifiedcriteria.