A Qu ick Micr o s o ft Acces s 2000/2002 Tu t o r ial
By Char les W. Nev ille, Char les W. Nev ille, Mar ch 2001, w it h m o dificat io n by Leit h Chan , Feb 2003
Verbatimcopyingandredistributionofthistutorialarepermittedinanymediumprovidedthisnoticeandthecopyrightnoticearepreserved.This t u t o r ial is in t en ded fo r co m p u t er s cien ce s t u den t s w ho n eed a q u ick in t r o du ct io n t o Micr o s o ft Acces s , bu t it w ill be u s efu l t o an y o n e n eedin g s u ch an in t r o du ct io n . To get t he fu ll ben efit o f t his t u t o r ial, y o u w ill n eed a co m p u t er r u n n in g o n e o f Micr o s o ft Win do w s 95 o r higher , Micr o s o ft NT 4.0 o r higher , o r Mic ft Win do w s 2000. Yo u w ill als o n eed t o hav e a co p y o f Micr o s o ft Acces s 2000/2002 in s t alled.
I
ntro
du ct io n
AnAccessUroestabasaeDReadyFOpenAnd
ynonymousrosoroso
Tho u gh Micr o s o ft Acces s is NOT s w it h dat abas e s y s t em s , t her e ar e m o r e co p ies o f Mic ft Acces s in u s e t han an y o t her dat abas e s y s t em . It t her efo r e beho o v es co m p u t er s cien ce s t u den t s t o be at leas t s u p er ficially fam iliar w it h MS Acces s . This t u t o r ial w ill gu ide y o u t hr o u gh s o m e o f t he bas ic p o in t -an d-click s t u ff, an d w ill ev en s ho w y o u ho w t o is s u e co m p lex SQL (St r u ct u r ed Qu er y Lan gu age) q u er ies . Yo u w ill o p en t he No r t hw in d Micr o s o ft s am p le dat abas e an d q u er y it in v ar io u s w ay s . This is a HANDS ON t u t o r ial; it giv es y o u s t ep -by -s t ep dir ect io n s fo r car r y in g o u t s im p le t as ks in Acces s . As y o u r ead, y o u s ho u ld hav e a co p y o f t he No r t hw in d dat abas e o p en in Micr o s o ft Acces s 2000/2002, an d y o u s ho u ld car r y o u t t he t as ks y o u r s elf, ex act ly as dir ect ed.
Fir s t , a few w o r ds abo u t w hat Micr o s o ft Acces s 2000/2002 is an d is n 't . Peo p le w ho do n 't r eally u n der s t an d w hat a r elat io n al dat abas e s y s t em is , an d s o m e p eo p le w ho do n 't act u ally u n der s t an d w hat Acces s 2000/2002 is,willtellyouthatMicrosoftAccessisnotafulyleroitalanatadbasesystem.Inhteadatabeswrotno,ld beingfullyrelationalisverybad.Don'tworry,thepeoplewhotellyouthatarelikethepeoplewhotrytotelly o u t hat lin u x is n o t a 32 bit o p er at in g s y s t em . Acces s 2000/2002, an d it s p r edeces s o r s Acces s 95 an d 97, ar e ex cellen t fu lly r elat io n al dat abas e s y s t em s . Bu t Acces s 2000/2002 do es hav e a few s ho r t co m in gs . The p r in cip le s ho r t co m in g is t hat it is alm o s t im p o s s ible t o en fo r ce r eas o n able s ecu r it y r es t r ict io n s w it h Acces s 2000/2002. So if y o u w an t a v ar iet y o f u s er s t o in t er act w it h a dat abas e, y o u s ho u ld m o v e u p t o Micr o s o ft 's SQL Ser v er , an Or acle dat abas e abo v e t he lev el o f Or acle Per s o n al Edit io n , o r t he w o n der fu l, an d FREE, My Sq l. (An d let u s n o t fo r get t o m en t io n t he higher lev els o f FileMaker Pr o as go o d p o s s ibilit ies .) As a cer t ified Micr o s o ft hat er , I w o u ld n at u r ally p r efer t hat y o u m o v e u p t o Or acle, My Sq l, o r FileMaker Pr o , bu t I hav e t o be fair . Micr o s o ft 's dat abas e productsareextremelygood,easytodevelopfor,readilyacceptedbytheoutsideworld,andalwaysgoodcho ices . This t u t o r ial is div ided in t o chap t er s . Yo u p r o bably s ho u ld go t hr o u gh t he chap t er s in s eq u en ce, s t ar t in g w it h chap t er 1. Chap t er s
s
1.Get t in gSt ar t ed. 2.Addin g gin g Chan d an Dat a. 3. le Qu er iesSim p. 4.Cr eat in g er QBE Qu ies. 5. iesMu lt er i-t able QBE Qu. 6. ies erSQL Qu. If y o u w an t t o lear n m o r e abo u t Micr o s o ft Acces s 2000, es p ecially fr o m a dev elo p er 's p o in t o f v iew , t he bes t inglesourceisRogerJennings'excellentbook,UsingAccess2002,publishedbyQuePress.
Chap t er 1. Get t in g St ar t ed
Co p y in g t he No r t hw in d Sam p le Dat abas e
The fir s t t hin g y o u n eed t o do is m ake a co p y o f t he No r t hw in d s am p le dat abas e t o av o id alt er in g an d p er hap s p er m an en t ly dis ablin g it . Yo u s ho u ld u s e t his co p y in s t ead o f t he o r igin al dat abas e w hile y o u go t hr o u gh t his t u t o r ial. Yo u can co p y t he No t hw in d s am p le dat abas e fr o m P:\IMSE1013\Acces s \ No r t hw in d.m db t o y o u r U: dr iv e.
Op en in g t he No r t hw in d Sam p le Dat abas e
St ep 1. Op en y o u r co p y o f No r t hw in d by do u ble clickin g it .
St ep 2. If all go es w ell, Micr o s o ft Acces s w ill s t ar t u p , an d y o u w ill s ee t he o p en in g s cr een o f t he No r t hw in d database.PresstheOKbuttontogettheNorthwindTradersspashscreenoutofthewayandstartworkingit h t he act u al No r t hw in d dat abas e.
w
Mo v in g a
roun
TheopeningscreenoftheNorthwinddatabase
d t he No r t hw in d Dat abas e
um
Oncetheopeningsplashscreenisoutoftheway,youcanseethedatabasewindow.Itconsistsofanberoftabs,eachofwhichdisplaysadifferentaspectofthedatabase.Typically,theNorthwinddatabaseopenswiththeFormstabselected.InMicrosoftapplications,Formsarewindowsusedtointeractwiththeapplication.Lat er , aft er y o u hav e fin is hed t his t u t o r ial, y o u m ight w an t t o s at is fy y o u r cu r io s it y abo u t fo r m s . If s o , t r y selectingtheMainSwitchboardform,andthenpressingtheOpenbutton.AsistypicalinMicrosoftap p licat io n s , y o u can clo s e a fo r m by clickin g t he s m all x at t he t o p r ight o f t he fo r m o n it s t it le bar . (Be
car efu l n o t t o click o n t he x at t he t o p r ight o f t he Micr o s o ft Acces s t it le bar , o r y o u w ill clo s e Acces s co m p let ely .) If y o u w an t t o fin d o u t ho w t o bu ild y o u r o w n fo r m s an d dev elo p Micr o s o ft Acces s ap p licat io n s , readingRogerJenning'sexcellentbook,UsingAccess2002,publishedbyQuePress.
try
TheformstaboftheNorthwinddatabasewindow
WearemainlyinterestedintherelationaldatabaseaspectsofMicrosoftAccess,soletusopentheTablestaban d lo o k at s o m e o f t he t ables in t he No r t hw in d dat abas e. Step1.PressthetabmarkedTablesunderthelistofObjectsattheleftofthedatabasewindow,andthenclickONCEonCustomerstoselecttheCustomersTable.
Step2.PresstheDesignbuttonatthetopofthedatabasewindowtoviewthedesign(tabledefinition)oftheCustomersTable.NoticethesmallkeybyCustomerID.Asyouhaveprobablyalreadyguessed,thismeanshat Cu er ID is a key field.