从DAO转换到ADO

核心提示：SwitchfromDAOtoADOBySamHuggillIntroductionAfewdaysago,IstartedanewPRojectthathandlesalargedatabasecontainingHTMLcodeforacompletewebsite.Theprojecthastoallowthew

SwitchfromDAOtoADO

BySamHuggill

Introduction

Afewdaysago,IstartedanewPRojectthathandlesalargedatabasecontainingHTMLcodeforacompletewebsite.Theprojecthastoallowthewebmastersofthewebsiteviewallupdatesmadetothesite,whentheyweremadeandbywhom.Theycanalsoeditthepagesonthesite,andautomaticallyuploadthem.

ThisprojectrequirestheuSEOfafairlylargedatabasethatneedstobeaccessedbymanypeoplefromdifferentPCs.IdecidedtouseSQLServerasthebackendtotheproject,butthismeantthatIcouldn注释：tuseDAOtoconnecttoit!Whatapain!

So,IdecideditwasabouttimeIstartedtolearnADO.ItookaquickglancearoundonthenetatmyusualVBsites,butfoundlittleornohelpformeonADO.

Well,asweprideourselveshereatVBSquareonaddingoriginalcontent,IdecidedIwouldwriteanarticleonusingADO.

ThisarticleisonlyreallytogetyoustartedonADO,andonlydiscussestheconnectionandrecordsetobjects.TherearemanymorefeaturesofADOthatyouwillneedtolookintobeforeyoutakeonaprojectusingADO.
Connectingtolocalandexternaldatabases

WithADO,youcanbuildallyourcodearoundalocaldatabaseandthen,veryeasilychangeonelineofcodethatwillallowyoutoaccessadatabaseonaSQLServer.

Thethingthattookmeawhiletofigureout,washowtoconnecttoadatabase.WithDAO,youusetheOpenDatabasecommandpassingthepathofthedatabaseasoneofthearguements.ButwithADO,youneedtobuildaconnectionstring.Toconnecttoalocaldatabase,usethefollowingconnectionstring:

ConnectionString="Provider=Microsoft.JET.OLEDB.3.51;DataSource=c:\mydb.mdb"

Thatmayseemabitcumbersome,butthisflexibilityprovidesyouwiththemeanstoconnecttoalmostanydatabaseinanyformatanywhere.ThefollowingconnectionstringisusedtoconnecttoaSQLSeverdatabasenamed注释：people注释：:

ConnectionString="driver=[SQLServer];uid=admin;server=myserver;database=people"
SwitchfromDAOtoADO

BySamHuggill

UsingtheConnectionObject

TheConnectionobjectisthebasefromwhichalmostallADOfunctionsderivefrom.Youcanusethisobjecttocarryoutmostoftheactionsperformedinthesamplecode,usingSQLstatements.E.g.

mCN.Execute"DELETEFROMPeopleWHEREID=1"

Iwon注释：tgointoanydetailaboutusingSQLstatements,buttheMSDNhassomeinfoonthem.

TheconnectionobjectreturnsarecordsetobjectifyouusetheExecutemehtod.YoucanusethistocreateaDLLanduseCOMtogetthecontentsofarecordset.e.g.

PublicSubGetRecordSet()AsADODB.Recordset
GetRecordSet=mCN.Execute("SELECT*FROMPeople")
EndSub

Thismeansthatyoucancentralizeallyoudatabasecodeintoonecomponent,preferablyaDLL.

UsingtheRecordsetObject

InADO,theRecordsetobjectisverysimilartotheDAORecordsetobject.Thismakesthingsaloteasierwhenportingyourcode,althoughyouwillneedtodeviseafewworkaroundstoovercomeafewmissingfeatures.

Forexample,whenyouinsertarecord,butneedtostoreitsID(AutoNumber)valueinthesameaction,youwouldnormallyusethiscodeinDAO:

Withrs
.AddNew
.Fields("Name").value=sNewValue
.Update
.Bookmark=.Lastmodified
m_intRcdID=.Fields("ID").value
.Close
EndWith
TheADORecordsetobjectdoesnotexposeaLastModifiedorLastUpdatedproperty,soweneedtousethefollowingworkaround:

Withrs
.AddNew
.Fields("Name").value=sNewValue
.Update
.Requery
.MoveLast
m_intRcdID=.Fields("ID").value
.Close
EndWith

Afterupdatingtherecordset(whichyoudon注释：tneedtodoifyouaremovingtoanotherrecord,asADOautomaticallyupdateschangesmadewhenyoumoverecords)youneedtorefreshtherecordsetusingtheRequerymethod.Thenyouneedtomovetothelastrecord,whichistheoneyouhavejustadded.Now,justextracttheIDvalueandstoreitinamembervariable.
Sampleapplication

TohelpyoumovefromDAOtoADO,IhavemadeasimilarsampleapplicationasIdidfortheBeginningDatabasesarticle.Thesampleoffersthesefeatures:

Addingnewrecords
Deletingrecords
Updatingrecords
Gettingrecorddata
Itisaverysimpledemo,butshouldhelpyoutounderstandthebasics.ItusethelatestversionofADO,version2.1.SeethesectionatthebottomfordownloadingtheADOLibrariesandthesampleapplcation.

Togetthesampleapplicationtowork,startanewStandardEXEProjectandaddareferencetotheMicrosoftActiveXDataObjects2.1Library(Project,References).Addfourcommandbuttons(cmdAdd,cmdDelete,cmdGet,cmdSave)andthreetextboxes(txtNotes,txtURL,txtName).Copy/pastethefollowingcodeintotheform:

OptionExplicit

注释：PrivatereferencestotheADO2.1ObjectLibrary
PrivatemCNAsConnection
PrivatemRSAsNewRecordset

注释：InternalreferencetothecurrentrecordsIDvalue
PrivatemintRcdIDAsInteger

PrivateSubcmdAbout_Click()
frmAbout.ShowvbModal
EndSub

PrivateSubcmdAdd_Click()
AddRecord
EndSub

PrivateSubcmdClose_Click()
UnloadMe
EndSub

PrivateSubOpenConnection(strPathAsString)

注释：Closeanopenconnection
IfNot(mCNIsNothing)Then
mCN.Close
SetmCN=Nothing
EndIf


注释：Createanewconnection
SetmCN=NewConnection

WithmCN
注释：ToconnecttoaSQLServer,usethefollowingline:

注释：.ConnectionString="driver=[SQLServer];uid=admin;server=mysrv;database=site"

注释：Forthisexample,wewillbeconnectingtoalocaldatabase
.ConnectionString="Provider=Microsoft.JET.OLEDB.3.51;DataSource="&strPath

.CursorLocation=adUseClient
.Open

EndWith

EndSub

PrivateSubAddRecord()


注释：Addanewrecordusingtherecordsetobject
注释：Couldbedoneusingtheconnectionobject
mRS.Open"SELECT*FROMPeople",mCN,adOpenKeyset,adLockOptimistic

WithmRS

.AddNew
.Fields("Name").Value=txtName.Text
.Fields("URL").Value=txtURL.Text
.Fields("Notes").Value=txtNotes.Text

注释：Afterupdatingtherecordset,weneedtorefreshit,andthenmovetothe
注释：endtogetthenewestrecord.Wecanthenretrievethenewrecord注释：sid
.Update
.Requery
.MoveLast

mintRcdID=.Fields("ID").Value

.Close

EndWith

EndSub

PrivateSubDeleteRecord()

注释：Deletearecordandclearthetextboxes

mRS.Open"SELECT*FROMPeopleWHEREID="&mintRcdID,mCN,adOpenKeyset,adLockOptimistic

mRS.Delete
mRS.Close

txtName.Text=""
txtURL.Text=""
txtNotes.Text=""

EndSub

PrivateSubGetInfo()

注释：GetthedataforarecordbasedonitsIDvalue
mRS.Open"SELECT*FROMPeopleWHEREID="&
mintRcdID,mCN,adOpenKeyset,adLockOptimistic

WithmRS

txtName.Text=.Fields("Name").Value
txtURL.Text=.Fields("URL").Value
txtNotes.Text=.Fields("Notes").Value
.Close

EndWith

EndSub

PrivateSubUpdateRecord()

注释：Updatearecord注释：svalues
mRS.Open"SELECT*FROMPeopleWHEREID="&mintRcdID,mCN,adOpenKeyset,adLockOptimistic

WithmRS

.Fields("Name").Value=txtName.Text
.Fields("URL").Value=txtURL.Text
.Fields("Notes").Value=txtNotes.Text

.Update
.Close

EndWith

EndSub

PrivateSubcmdDelete_Click()
DeleteRecord
EndSub

PrivateSubcmdGet_Click()

注释：Asktheuserwhichrecordshouldberetrievedandgetthedata
注释：forthatrecord
mintRcdID=Val(InputBox$("EnterIDofrecord:",App.Title,"1"))

GetInfo

EndSub

PrivateSubcmdSave_Click()
UpdateRecord
EndSub

PrivateSubForm_Load()

OpenConnectionApp.Path&"\people.mdb"

EndSub

PrivateSubForm_Unload(CancelAsInteger)

IfNot(mRSIsNothing)Then
SetmRS=Nothing
EndIf

IfNot(mCNIsNothing)Then
mCN.Close
SetmCN=Nothing
EndIf

EndSub->