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