從DAO轉(zhuǎn)換到ADO

2019-11-18 17:46:30

字體：大中小

供稿：網(wǎng)友

SwitchfromDAOtoADO

BySamHuggill

Introduction

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

ThisprojectrequirestheuSEOfafairlylargedatabasethatneedstobe accessedbymanypeoplefromdifferentPCs.IdecidedtouseSQLServerasthebackendtotheproject,butthismeantthatIcouldn注釋?zhuān)簍useDAOtoconnecttoit!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注釋?zhuān)簆eople注釋?zhuān)?

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

BySamHuggill

UsingtheConnectionObject

TheConnectionobjectisthebasefromwhichalmostallADOfunctionsderivefrom.Youcanusethisobjecttocarryoutmostoftheactionsperformedinthesamplecode,usingSQLstatements.E.g.

mCN.Execute"DELETEFROMPeopleWHEREID=1"

Iwon注釋?zhuān)簍gointoanydetailaboutusingSQLstatements,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注釋?zhuān)簍needtodoifyouaremovingtoanotherrecord,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

注釋?zhuān)篜rivatereferencestotheADO2.1ObjectLibrary
PrivatemCNAsConnection
PrivatemRSAsNewRecordset

注釋?zhuān)篒nternalreferencetothecurrentrecordsIDvalue
PrivatemintRcdIDAsInteger

PrivateSubcmdAbout_Click()
frmAbout.ShowvbModal
EndSub

PrivateSubcmdAdd_Click()
AddRecord
EndSub

PrivateSubcmdClose_Click()
UnloadMe
EndSub

PrivateSubOpenConnection(strPathAsString)

注釋?zhuān)篊loseanopenconnection
IfNot(mCNIsNothing)Then
mCN.Close
SetmCN=Nothing
EndIf

注釋?zhuān)篊reateanewconnection
SetmCN=NewConnection

WithmCN
注釋?zhuān)篢oconnecttoaSQLServer,usethefollowingline:

注釋?zhuān)?ConnectionString="driver=[SQLServer];uid=admin;server=mysrv;database=site"

注釋?zhuān)篎orthisexample,wewillbeconnectingtoalocaldatabase
.ConnectionString="Provider=Microsoft.JET.OLEDB.3.51;DataSource="&strPath

.CursorLocation=adUseClient
.Open

EndWith

EndSub

PrivateSubAddRecord()

注釋?zhuān)篈ddanewrecordusingtherecordsetobject
注釋?zhuān)篊ouldbedoneusingtheconnectionobject
mRS.Open"SELECT*FROMPeople",mCN,adOpenKeyset,adLockOptimistic

WithmRS

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

注釋?zhuān)篈fterupdatingtherecordset,weneedtorefreshit,andthenmovetothe
注釋?zhuān)篹ndtogetthenewestrecord.Wecanthenretrievethenewrecord注釋?zhuān)簊id
.Update
.Requery
.MoveLast

mintRcdID=.Fields("ID").Value

.Close

EndWith

EndSub

PrivateSubDeleteRecord()

注釋?zhuān)篋eletearecordandclearthetextboxes

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

mRS.Delete
mRS.Close

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

EndSub

PrivateSubGetInfo()

注釋?zhuān)篏etthedataforarecordbasedonitsIDvalue
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()

注釋?zhuān)篣pdatearecord注釋?zhuān)簊values
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()

注釋?zhuān)篈sktheuserwhichrecordshouldberetrievedandgetthedata
注釋?zhuān)篺orthatrecord
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->

上一篇：Access97的報(bào)表解決方案

下一篇：VB5.0數(shù)據(jù)庫(kù)編程經(jīng)驗(yàn)小集