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->
