Microsoft Access

Bjørn Kristoffersen Høgskolen i Telemark

bjorn.kristoffersen@hit.no

Sammendrag Microsoft Access (heretter skriver vi kun Access) er et databasehåndteringsverktøy til bruk for personlige databaser og mindre flerbrukerdatabaser. Det har et intuitivt brukergrensesnitt som egner seg for sluttbrukere og til opplæring. Access er også et integrert utviklingsverktøy for programmering av databaseapplikasjoner med menyer, skjermbilder og rapporter, og støtte for databasespråket SQL. Det er mulig å utvikle enkle databaseapplikasjoner uten programmeringserfaring. Grunnleggende funksjonalitet i Access blir forklart, med vekt på definisjon av tabeller, spørringer, skjemaer og rapporter, samt enkel makroprogrammering. Framstillingen er basert på norsk utgave av Access 2003. Mesteparten av stoffet er imidlertid gyldig også for eldre versjoner. Se [Viescas 2003] for en grundig innføring i bruk av Access, og www.microsoft.com for dokumentasjon. Eksempeldatabasen Hobbyhuset.mdb kan lastes ned fra www.universitetsforlaget.no/databasesystemer.

Innhold 1 Grunnleggende bruk................................................................................................................ 2

1.1 Åpne, lage og lukke databaser ............................................................................................................... 2 1.2 Databasevinduet..................................................................................................................................... 2 1.3 Få hjelp .................................................................................................................................................. 3

2 Tabeller.................................................................................................................................... 3 2.1 Ajourhold av data................................................................................................................................... 4 2.2 Lage nye tabeller.................................................................................................................................... 5

3 Spørringer................................................................................................................................ 6 3.1 Lage spørringer i utformingsvisning...................................................................................................... 6 3.2 Lage spørringer med SQL...................................................................................................................... 7 3.3 Spørringer mot flere tabeller .................................................................................................................. 8

4 Skjemaer.................................................................................................................................. 9 4.1 Lage skjemaer med veivisere................................................................................................................. 9 4.2 Modifisere skjemaer .............................................................................................................................. 9 4.3 Håndtere en-til-mange forhold i skjemaer ........................................................................................... 10 4.4 Mengdefunksjoner i delskjemaer ......................................................................................................... 11 4.5 Tilpasse Access til en applikasjon ....................................................................................................... 11

2

2

5 Rapporter............................................................................................................................... 12 5.1 Lage rapporter med veiviser ................................................................................................................ 12 5.2 Delene i en rapport............................................................................................................................... 12

6 Makroer og moduler.............................................................................................................. 13 6.1 Makroer til kommandoknapper............................................................................................................ 13 6.2 Generelt om makroer ........................................................................................................................... 14 6.3 Søkeskjemaer ....................................................................................................................................... 15 6.4 Rapportstyring ..................................................................................................................................... 16 6.5 Veivisere og VisualBasic..................................................................................................................... 17

7 Samordne Access med andre programmer............................................................................ 17 7.1 Import og eksport av data .................................................................................................................... 17 7.2 Lage flettebrev med Word ................................................................................................................... 18 7.3 Automatisere Excel fra Access ............................................................................................................ 18

8 Begreper ................................................................................................................................ 20

9 Oppgaver ............................................................................................................................... 20

Litteratur................................................................................................................................... 21

1 Grunnleggende bruk

1.1 Åpne, lage og lukke databaser Access ligger på Start-menyen. Hvis det er opprettet en snarvei vil det også ligge et ikon formet som en nøkkel på skrivebordet.

Vi bruker menyvalget Fil/Ny for å opprette en ny database, og må oppgi katalog og filnavn. Access-databaser blir lagret i mdb-filer, for eksempel Hobbyhuset.mdb. Ved å dobbeltklikke en mdb-fil i filbehandleren vil Access starte og databasen bli åpnet.

Det er kun mulig å jobbe med én database av gangen. Menyvalget Fil/Avslutt lukker den aktive databasen og avslutter Access. Access vil automatisk sørge for at registrerte data blir lagret underveis i arbeidet.

1.2 Databasevinduet Som andre Office-programmer består brukergrensesnittet til Access av en menylinje, en eller flere verktøylinjer, en statuslinje og det vi kan kalle for arbeidsområdet, se Figur 1. Når vi jobber med en database vil databasevinduet bli vist i arbeidsområdet. Hjelp-systemet er plassert til høyre, men kan flyttes og også løftes ut som et eget vindu.

En Access-database kan inneholde flere typer av databaseobjekter:

• Tabeller blir brukt for å lagre data. Ved å åpne en tabell kan vi legge til nye data, og redigere eller slette eksisterende data.

• Spørringer henter ut data fra en eller flere tabeller. Det er også mulig å lage spørringer som definerer nye tabeller, eller som setter inn, oppdaterer eller sletter data fra en eksisterende tabell.

3

3

• Skjemaer er ”skjermbilder” tilpasset en bestemt arbeidsoperasjon. Vi kan lage skjemaer som forenkler registrering av data i en tabell, skjemaer for å søke etter data, og skjemaer som lar brukeren styre utskrift av rapporter.

• Rapporter presenterer data hentet fra tabeller/spørringer på papir.

• Sider (tenk nettsider) gjør databaser tilgjengelig på internett.

• Makroer og moduler blir brukt av programmerere for å lage spesialtilpassede applikasjoner.

Figur 1 Brukergrensesnittet i Access

1.3 Få hjelp Hjelp-systemet blir aktivert ved å klikke spørsmålstegnet helt til høyre på menylinjen. Velg Innholdsfortegnelse for å lese dokumentasjonen vist i Figur 1. Access kan koble til hjelpesider på internett med utfyllende informasjon.

Det er også mulig å søke på nøkkelord fra tekstboksen helt oppe til høyre i skjermbildet.

Funksjonstasten F1 gir kontekstsensitiv hjelp, det vil si hjelp tilpasset en konkret situasjon. Det kan være lurt å trykke F1 hvis man har påbegynt en operasjon og står fast.

2 Tabeller Data blir lagret i tabeller. En database vil som regel bestå av flere tabeller. Figur 2 viser en tabell som inneholder data om varelageret til en bedrift, slik den blir vist i Access.

4

4

Hver enkelt rad i tabellen inneholder data om et vareslag. Egenskaper ved varene, som for eksempel betegnelse og pris, er lagret i hver sine kolonner. Enhver kolonne har et navn, vist som overskrift til kolonnen. Alle verdiene i en bestemt kolonne er av samme datatype: Kolonnen VNr inneholder heltall, kolonnen Betegnelse tekst og kolonnen Pris kronebeløp.

Figur 2 Tabellen Vare

2.1 Ajourhold av data For å jobbe med en tabell må man først åpne den. Sørg for at kategorien Tabeller er vist i databasevinduet. Klikk eventuelt i boksene til venstre. Dobbeltklikk tabellen, eller merk tabellen og klikk Åpne øverst i databasevinduet. Access viser tabellen på skjermen. Vi kan forflytte oss i tabellen, endre, slette og legge til data.

• Navigere: Piltaster og rullesjakter (scrollbar) kan brukes for å forflytte seg på vanlig måte. Det er også mulig å gå direkte til et felt ved pek-og-klikk. For tabeller med mange rader kan ikke alle bli vist samtidig. Nederst i tabellvinduet står det hvor mange rader det er i tabellen og hvilken rad som er aktiv. Her er det også knapper for å gå til første, forrige, neste og siste rad.

• Endre: Klikk i verdien og rediger på vanlig måte.

• Legge til en ny rad: Klikk på den lille pilen merket med en stjerne nederst i tabellvinduet, eller gå til siste kolonne i siste rad og trykk Enter-tasten.

• Slette en rad: Merk raden ved å klikke i den grå firkanten til venstre for raden. Trykk Delete-tasten.

• Slette mange rader: Merk radene og trykk Delete-tasten. Sammenhengende rader merkes ved å dra-og-slippe fra den første raden til den siste. Ved å holde nede Ctrl-tasten er det mulig å merke rader som ikke er sammenhengende.

• Sortere: Merk en kolonne ved å klikke i kolonneoverskriften og velg en av knappene Sorter stigende/Sorter synkende på verktøylinjen.

• Søke: Merk en kolonne og velg knappen Søk (kikkerten) på verktøylinjen. Skriv inn søkekriteriet i dialogvinduet som blir vist.

• Filtrere: Ved å merke en tekst og klikke knappen Filtrere etter merking (sjakten med et lyn) vil kun rader som ”matcher” den merkede teksten bli vist. Dette er kun et midlertidig filter; de øvrige radene blir ikke slettet. Prøv for eksempel å filtrere med hensyn på kategori 14 i tabellen over.

5

5

• Endre bredden på kolonner: Dra-og-slipp fra høyre kant i kolonneoverskriftene.

• Skjule og vise kolonner: Velg Format/Vis kolonner og kryss av for aktuelle kolonner. Å skjule en kolonne er ikke det samme som å slette en kolonne. Kun visningen blir endret.

2.2 Lage nye tabeller For å opprette en ny tabell må kategorien Tabeller være aktiv i databasevinduet. Velg deretter Ny øverst i databasevinduet. Det er mulig å lage tabeller på flere måter. Vi forklarer bruk av Utformingsvisning, som er den mest generelle teknikken.

Access viser et ”linjeark” for å definere kolonnene i tabellen, se Figur 3. Hver kolonne blir definert på en egen linje. Skriv inn navnet på kolonnen under Feltnavn1 og velg datatype fra nedtrekkslisten. Til høyre er det plass til å skrive inn en kort forklaring (fritekst) på hva slags data kolonnen skal inneholde.

Figur 3 Tabelldefinisjon

I nedre del av vinduet er det mulig å definere andre egenskaper verdiene i kolonnen skal ha. Nøyaktig hvilke egenskaper som det er mulig å styre avhenger av hvilken datatype som er valgt.

• Feltstørrelse: Maksimalt antall tegn i tekststrenger.

• Standardverdi: Denne verdien blir automatisk satt inn når nye rader blir lagt til, og er nyttig hvis en bestemt verdi forekommer svært ofte.

• Valideringsregel: Her kan man skrive inn en betingelse som alle verdier i kolonnen skal tilfredsstille, for eksempel <10000 for kolonnen Pris. Access vil sjekke betingelsen og gi feilmelding hvis bruker skriver inn en verdi som bryter betingelsen.

1 Access bruker begrepet felt om det vi kaller kolonne.

6

6

• Obligatorisk: Kolonnen må alltid fylles ut.

• Indeksert: Settes for kolonner vi søker ofte i. Indekser gjør at tabellen tar større plass, men søk vil gå hurtigere.

Alle tabeller bør ha en primærnøkkel. En primærnøkkel er en kolonne, eller en kombinasjon av kolonner, som unikt identifiserer en rad i tabellen. For varetabellen vil VNr være velegnet, fordi alle varer har et varenummer og det ikke finnes to varer med samme varenummer.

Primærnøkkel for en tabell blir definert ved å merke kolonnen(e) og klikke knappen merket Angi primærnøkkel på verktøylinjen. Kolonner merkes ved klikk i firkanten til venstre på linjen der kolonnen er definert. Ved å holde nede Ctrl-tasten er det mulig å merke flere linjer (aktuelt for sammensatte primærnøkler). Primærnøkler kan fjernes ved menyvalget Vis/Vis indekser. Merk linjen med indeksnavn PrimaryKey og trykk Delete-tasten.

3 Spørringer Tabeller kan inneholde mange tusen rader. Det oppstår dermed et behov for å kunne søke i, filtrere, ordne og gruppere data. Noen typiske arbeidsoppgaver mot eksempeltabellen:

• Finn ut hvor mye Dvergtuja koster.

• Vis alle varer i kategori 14 som koster under 300 kroner.

• Lag en prisliste for busker sortert på pris.

• Finn ut hvor mange vareslag det er i hver kategori.

Vi bruker spørringer for å løse slike oppgaver. Spørringer er en kategori i databasevinduet og kan bygges opp ”visuelt”, eller med spørrespråket SQL.

En spørring er et ”program” som henter data fra en eller flere tabeller og produserer et spørreresultat. I Access utfører man spørringer ved å dobbeltklikke, eller eventuelt merke og velge Åpne. Et spørreresultat ser ut som en tabell, og noen spørreresultater kan redigeres som tabeller. Det som egentlig skjer er imidlertid at man oppdaterer den underliggende tabellen.

3.1 Lage spørringer i utformingsvisning Velg kategori Spørringer og klikk Ny. Som for tabeller er det flere teknikker, og igjen forklarer vi bruk av utformingsvisning. Access viser først et dialogvindu der vi velger hvilke tabeller som spørringen skal hente data fra. Merk aktuelle tabeller og klikk Legg til.

Tabellene som er valgt blir vist øverst i vinduet. Den nedre delen har et antall linjer som blir brukt for å definere hva spørringen skal gjøre:

• Felt: Hvilke kolonner er nødvendige for å definere spørringen? Alle kolonner som skal med i utskriften må i hvert fall være med. Det kan også være bruk for en kolonne selv om den ikke skal vises fram, for eksempel for å definere en betingelse.

• Tabell: Fra hvilke(n) tabell(er) skal data hentes?

• Sorter: I hvilken rekkefølge skal data presenteres? Merk kolonnen(e) og velg mellom stigende eller synkende sortering.

• Vis: Kryss av for de kolonnene som skal med i utskriften.

• Vilkår: Hvilke rader skal være med? Her skriver man betingelser. Alle betingelsene må være oppfylt for at raden skal bli tatt med.

7

7

• eller: For å ta med rader som oppfyller betingelse 1 eller betingelse 2, skriver man førstnevnte under Vilkår og sistnevnte på linjen merket eller.

Figur 4 Spørring i utformingsvisning

Følgende oppskrift viser konstruksjon av en spørring som viser busker (kategori 14) til under 300 kroner stykket. Utskriften skal være sortert på betegnelse.

1. Lag en ny spørring basert på varetabellen som forklart over.

2. Dobbeltklikk kolonnene VNr, Betegnelse, Pris, Antall og KatNr. Legg merke til at kolonnene dukker opp på linjen Felt.

3. Klikk i linjen Sorter under Betegnelse og velg Stigende.

4. Klikk i linjen Vilkår under KatNr og skriv inn betingelsen ”14”.

5. Klikk i linjen Vilkår under Pris og skriv inn betingelsen ”<300”.

6. Fjern avkryssing under KatNr på linjen Vis.

Skjermbildet skal se ut som i Figur 4. Utfør spørringen ved å klikke på Utfør (rødt utropstegn) på verktøylinjen. Ble resultatet som forventet? Lukk spørreresultatet og gi spørringen navnet BilligeBusker. Åpne spørringen i utformingsvisning. Flytt betingelsen ”<300” ned til linjen merket eller. Utfør spørringen på nytt. Tenk over forskjellen på ”og” og ”eller”!

3.2 Lage spørringer med SQL Enhver spørring bygget opp i utformingsvisning kan bli vist som SQL-kode. Omvendt kan man skrive SQL-kode og så se spørringen i utformingsvisning. Når en spørring er åpnet kan man veksle mellom de forskjellige visningene i nedtrekkslisten ved siden av knappen helt til venstre på menylinjen. Eksempelspørringen i Figur 4 svarer til følgende SQL-spørring:

SELECT Vare.VNr, Vare.Betegnelse, Vare.Pris, Vare.Antall

FROM Vare

WHERE Vare.KatNr=14 AND Vare.Pris<300

8

8

ORDER BY Vare.Betegnelse ASC

Etter FROM står navnet på tabellen som spørringen leser fra, etter SELECT står navnet på kolonnene som skal vises, etter WHERE står betingelsen som velger hvilke rader som skal med, og etter ORDER BY står det hvilke kolonner som styrer sorteringen av resultatet.

Ved å kombinere SQL-koding og ”visuell programmering” lærer man begge metoder bedre.

3.3 Spørringer mot flere tabeller Figur 5 viser tabellene i en database for lagerstyring og salg i det såkalte relasjonsvinduet. Tabellen Kunde inneholder en rad for hver av bedriftens kunder. Enhver kunde er tildelt et unikt løpenummer KNr, som er primærnøkkel i tabellen. Tabellen Ordre inneholder en rad for hver ordre bedriften mottar. Hver ordre blir påført et kundenummer. Linjen som er trukket mellom tabellene illustrerer dette forholdet. Symbolet ∞ påført linjen nærmest Ordre og 1-tallet påført linjen nærmest Kunde viser at det er et en-til-mange forhold mellom kunder og ordre: Hver kunde kan ha mange ordre, mens en ordre tilhører nøyaktig én kunde. Vi sier også at kolonnen KNr i Ordre er en fremmednøkkel mot tabellen Kunde.

Figur 5 Relasjonsvinduet

Fremmednøkler blir ofte benyttet for å koble tabeller i spørringer. La oss lage en liste som viser ordrene til hver kunde. Lag en ny spørring og legg til de to tabellene. Vis KNr, Etternavn, OrdreNr og OrdreDato. Sorter på Etternavn, og utfør spørringen. Legg merke til at hver kunde blir koblet med sine ordre.

Gå tilbake til utformingsvisning. Merk linjen mellom tabellene, trykk Delete-tasten og utfør spørringen på nytt. Spørreresultatet inneholder nå langt flere rader: Enhver kunde blir koblet med enhver ordre! Med 50 kunder og 5000 ordrer vil spørreresultatet inneholde 250 000 rader, hvorav de fleste er meningsløse (faktisk 245 000!).

De fleste databaser vil bestå av mange tabeller, og det er ofte et behov for å kombinere data fra flere tabeller. Det er viktig å koble tabellene riktig. Se for eksempel Kapittel 4 i [Kristoffersen] for en grundig forklaring.

9

9

4 Skjemaer

4.1 Lage skjemaer med veivisere Den enkleste måten å lage et skjema på er å bruke en veiviser. Det finnes veivisere for flere typer av skjemaer. Noen av veiviserne krever kun at brukeren oppgir hvilken tabell skjemaet skal jobbe mot og genererer deretter skjemaet automatisk.

Figur 6 Registreringsskjema

Vi beskriver nå en av de enkleste måtene. Merk kategorien Skjemaer i databasevinduet og velg Ny. Bruk veiviseren Autoskjema: Kolonne og velg tabellen Vare fra nedtrekkslisten. Access genererer skjemaet og viser det på skjermen! Lukk vinduet og gi skjemaet navnet Vareregistrering. Resultatet blir omtrent som i Figur 6.

4.2 Modifisere skjemaer Det er mulig å tilpasse et skjema som er generert av en veiviser. Åpne Vareregistrering i utformingsvisning. Høyreklikk etiketten merket VNr og velg deretter Egenskaper fra hurtigmenyen. Velg kategori Format i egenskapsarket. Access viser en liste med de visuelle egenskapene til etiketten. Prøv å endre noen av egenskapene, for eksempel Bakgrunnsfarge. Lukk egenskapsarket for å se effekten av endringen.

Kontroll er en samlebetegnelse på tekstbokser, etiketter, kommandoknapper, listebokser med flere. Det er mulig å endre egenskapene til flere kontroller av gangen. Trekk opp et rektangel med musen (klikk-hold-og-trekk) som overlapper de aktuelle kontrollene. Alle kontrollene skal da bli merket. Høyreklikk og endre egenskaper på vanlig måte. Med denne teknikken kan man for eksempel endre bakgrunnsfargen til samtlige etiketter, og dermed oppnå et konsistent brukergrensesnitt.

Alle tekstboksene i skjemaet over viser data fra den underliggende tabellen. Dette kalles generelt for bundne kontroller. Det er også mulig å vise fram avledede data. En ubundet kontroll inneholder en formel, bygget opp fra funksjoner og data fra andre kontroller. Vi kan for eksempel legge til en tekstboks som viser pris inklusive merverdiavgift. Sett egenskapen Kontrollkilde til følgende formel:

=[Pris]*1.25

10

10

Det er her antatt at den bundne tekstboksen som inneholder pris uten merverdiavgift har navn Pris. Merk at Access krever at formelen starter med et likhetstegn.

Radene i en tabell er ordnet i den rekkefølgen data blir registrert. Det er imidlertid enkelt å lage et skjema som viser radene sortert med hensyn på en av kolonnene. Anta vi ønsker å modifisere skjemaet for vareregistrering i Figur 6 slik at varene blir sortert med hensyn på Betegnelse:

1. Start med å lage en spørring som sorterer radene i tabellen skjemaet jobber mot.

2. Gå deretter inn i egenskapsarket til skjemaet (boksen øverst i venstre hjørne), og endre egenskapen Postkilde i kategorien Data slik at den inneholder navnet på spørringen.

4.3 Håndtere en-til-mange forhold i skjemaer Det er ofte naturlig å lage skjemaer som viser data fra to tabeller som inngår i et en-til-mange forhold. Hoved/del-skjema er en enkel og nyttig teknikk. Oppskriften under lager et skjema som viser én kunde av gangen påført alle ordrene til denne kunden:

1. Lag et skjema for kunder ved hjelp av veiviser Autoskjema: Kolonne og et skjema for ordre ved hjelp av veiviser Autoskjema: Tabell.

2. Åpne kundeskjema i utformingsvisning og velg Vis/Verktøykasse. Sørg for at tryllestaven (øverst til høyre i verktøykassa) er aktiv og velg Delskjema fra verktøypaletten. Trekk ut et rektangel nederst i skjemaet. Gjør rektangelet stort nok til å vise ordreskjemaet.

3. Velg Bruk et eksisterende skjema og merk ordreskjemaet.

4. Velg Vis Ordrer for hver post i Kunde ved hjelp av KNr. Dette definerer hvordan skjemaene skal ”kobles”.

De to skjemaene er ”synkronisert” i den forstand at navigering til neste kunde medfører at innholdet i delskjemaet blir oppdatert. Delskjemaet viser altså til enhver tid ordre som hører til kunden i hovedskjemaet.

En-til-mange forhold kan alternativt bli håndtert ved å basere skjemaer på spørringer. I stedet for å vise en kunde med tilhørende ordre kan forholdet betraktes fra ”mange-siden”. Det vil vise hver enkelt ordre påført kundeinformasjon. Lag en spørring som kobler kunder og ordre, og lag et skjema mot denne spørringen med en veiviser som forklart tidligere.

Samme type skjema kan også lages ved oppslag. I stedet for å lage en spørring som kobler kunder og ordre kan skjemaet for hver ordre hente inn kundeinformasjon ved hjelp av en funksjon DLookUp:

• Lag et ordreskjema frmOrdre basert på tabellen Ordre. Legg til en ubundet tekstboks Navn for å vise etternavnet til kunden. Sett egenskapen Kontrollkilde for tekstboksen til en formel som gjør bruk av funksjonen DLookUp. Formelen blir som følger:

=DLookUp( "Etternavn"; "Kunde"; "KNr=Forms![frmOrdre]![KNr]" )

Notasjonen Forms![frmOrdre]![KNr] blir brukt for å referere til kontroller. Forms angir samlingen av alle åpne skjemaer i den aktive databasen, frmOrdre er navnet på ett av disse skjemaene, og KNr er en av kontrollene i skjemaet frmOrdre. Vi ”navigerer” altså nedover i et hierarki. Det er for øvrig også mulig å referere til bestemte egenskaper ved en kontroll med denne notasjonen.

11

11

Access har innebygget mekanismer for håndtering av en-til-mange forhold. Særlig nyttig er bruk av kombinasjonsbokser og fremmednøkler. Lovlige varekategorier er lagret i tabellen Kategori. Kolonnen KatNr i varetabellen er en fremmednøkkel mot denne tabellen. Ved registrering av nye varer kan brukeren velge kategori fra tabellen Kategori i stedet for å skrive inn med fritekst. Dette forhindrer skrivefeil, og brukeren slipper å huske på at kategori 14 er busker. Oppskrift: Sørg for at tryllestaven er aktiv og legg til en kombinasjonsboks. Access starter en veiviser. Velg tabell Kategori, ta med både KatNr og Navn, sørg for at nøkkelkolonnen blir skjult og velg at verdien skal lagres i KatNr. Veiviseren ordner resten.

Man kan spesifisere at KatNr alltid skal bli vist som en kombinasjonsboks (i alle skjemaer og også direkte i tabellen). Åpne varetabellen i utformingsvisning og merk kolonnen KatNr. Velg Oppslag og sett Vis kontroll til Kombinasjonsboks. Sett Radkildetype til Tabell/Spørring og velg kategoritabellen i Radkilde. Bundet kolonne skal være et tall som identifiserer KatNr.

4.4 Mengdefunksjoner i delskjemaer Et skjema består av en detaljdel, topptekst/bunntekst på side (for skjemaer med flere sider) og topptekst/bunntekst skjema. Sistnevnte er nyttige når man skal lage delskjemaer.

Veiviseren for hoved/delskjema setter Visningsmåte for delskjemaet til Kontinuerlige skjemaer. Det betyr at brukeren vil se flere rader av gangen. Hvis man ønsker å oppsummere detaljdata i bunnen av delskjemaet er det mulig å legge inn en formel i skjemaets bunntekst.

Anta vi skal lage et ordreskjema med tabellen Ordrelinje som datagrunnlag i et delskjema. PrisPrEnhet er en kolonne i Ordrelinje, og vi ønsker å vise gjennomsnittspris for varene (til en ordre) nederst i delskjemaet. Legg til en ubundet tekstboks i bunnteksten av delskjemaet. Sett Kontrollkilde for tekstboksen til formelen ”=Avg(PrisPrEnhet)”.

4.5 Tilpasse Access til en applikasjon Det kan være hensiktsmessig å skjule noen av de generelle funksjonene i Access, og i stedet tilby brukeren et mer spesialisert grensesnitt. Vi forklarer noen av teknikkene som blir brukt.

Vi kan få Access til automatisk å åpne et bestemt skjema ved oppstart av en gitt database: Velg Verktøy/Oppstart og sett inn aktuelt skjema under Vis skjema/side.

Det er også mulig å tilpasse selve menysystemet. Bruk menyvalg Verktøy/Tilpass:

• Velg Verktøylinjer og klikk Ny. Gi menylinjen navnet MinMenylinje. En ny tom menylinje skal bli vist på skjermen.

• Klikk Egenskaper og sett Type til Menylinje. Legg merke til at det også er mulig å lage egne verktøylinjer og hurtigmenyer.

• Kopier noen av de innebygde menyene ved å holde ned Ctrl-tasten og trekke-og-slippe ned i den nye menylinjen. Prøv for eksempel å kopiere Fil, Rediger og Vis.

• Velg Verktøy/Oppstart og merk den nye menylinjen i nedtrekkslisten Menylinje. Lukk databasen og åpne den igjen for å se effekten.

Menyvalg for å åpne egne skjemaer og rapporter kan legges inn på egendefinerte verktøylinjer og menyer. Se for eksempel Alle skjemaer under kategorien Kommandoer.

12

12

5 Rapporter Rapporter blir brukt for å presentere informasjon. Vanligvis ønsker vi å skrive ut rapporter, men det er også mulig å forhåndsvise rapporter på skjermen.

5.1 Lage rapporter med veiviser Den enkleste måten å lage en rapport på er å bruke en veiviser. Merk kategorien Rapporter i databasevinduet og klikk Ny. Merk en tabell eller spørring og bruk Rapportveiviser. Systemet viser en serie med dialogvinduer:

1. Velg hvilke kolonner som skal være med. Flytt en og en kolonne, eller alle på en gang, med knappene midt i vinduet. Kolonner kan fjernes igjen med knappene som peker mot venstre.

2. Velg gruppenivå. Anta vi har valgt varetabellen og vil beregne gjennomsnittspriser for varene i hver enkelt kategori. Da grupperer vi med hensyn på kolonnen KatNr: Merk kolonnen og flytt den til høyre vindu med pilknappen midt i vinduet.

3. Angi sortering. Velg Betegnelse for å sortere varene på varenavn innen hver kategori. I nedre del av vinduet er det en knapp merket Alternativer for sammendrag. Klikk denne, og velg Gjs (Gjennomsnitt) for kolonne Pris.

4. Velg utforming. Prøv noen forskjellige varianter.

5. Velg stil. Prøv igjen noen forskjellige varianter.

6. Gi rapporten et navn og velg Fullfør. Rapporten vil bli forhåndsvist, og kan deretter bli skrevet ut.

Figur 7 viser et utsnitt av hvordan rapporten kan bli seende ut.

Figur 7 Rapport laget med veiviser

5.2 Delene i en rapport Rapporter laget med en veiviser kan senere tilpasses. Begynn med en enklest mulig rapport som forklart over, men uten gruppering på kategori. Merk den nye rapporten i databasevinduet og klikk Utformingsvisning. Access viser hvordan rapporten er bygget opp:

13

13

• Topptekst i rapport: Overskrift som kun kommer på første side.

• Topptekst på side: Overskrifter som gjentas på hver side. Her er det vanlig å sette inn kolonneoverskrifter.

• Detalj: Beskriver hva som skal stå på hver enkelt linje i rapporten. Dette vil typisk være referanser til kolonner i underliggende tabell/spørring.

• Bunntekst på side: Her er det vanlig å sette inn sidenummer og dato.

• Bunntekst i rapport: Avsluttende tekst på siste side.

For øvelsens skyld: Legg til en etikett i hver av rapportens ulike deler. Dette gjøres ved å velge fra verktøypaletten på samme måte som for skjemaer. Gi etikettene en tekst som gjør at de er lette å kjenne igjen, for eksempel ”Topptekst rapport”. Forhåndsvis rapporten! Legg merke til plassering og antall forekomster av de forskjellige etikettene.

Enkle rapporter er altså delt inn i tre nivåer: Rapport, Side og Detalj. I tillegg kan de ha ett eller flere Gruppenivåer. Rapporten vist i Figur 7 vil ha et ekstra nivå mellom Side og Detalj merket med navnet på grupperingskolonnen, og viser altså områder Topptekst for KatNr og Bunntekst for KatNr.

Det er mulig å legge inn kalkulerte verdier i rapporter på samme måte som for skjemaer. La oss utvide varerapporten med priser inklusive merverdiavgift (mva). Legg inn en tekstboks i detaljdelen, og skriv inn formelen ”=[PrisPrEnhet]*1.25” i egenskap Kontrollkilde.

6 Makroer og moduler En instruksjon er en beskjed til Access om å utføre en bestemt operasjon, for eksempel å åpne et skjema, slette en rad i en tabell, eller skrive ut en rapport. En makro er en sekvens av instruksjoner. Makroer brukes til å automatisere vanlige arbeidsoppgaver.

6.1 Makroer til kommandoknapper For å illustrere bruk av makroer skal vi lage et skjema som kun består av én kommandoknapp. Når brukeren klikker på knappen skal ordreskjemaet laget tidligere bli åpnet. Dette skal vi gjøre ved å knytte en makro til knappen.

Start med å lage makroen: Velg kategorien Makroer i databasevinduet og klikk Ny. Access viser makrovinduet, se Figur 8.

Velg instruksjonen ÅpneSkjema i nedtrekkslisten Handling, og deretter det ferdiglagede skjemaet frmOrdre under Skjemanavn lenger ned i vinduet. Lukk vinduet og gi makroen navnet ÅpneSkjemaMakro.

Kolonnene Makronavn og Betingelse blir ikke vist som standard. De kan slås av og på fra Vis-menyen, og er ikke nødvendige i dette eksemplet.

14

14

Figur 8 Makro for å åpne et skjema

Lag så skjemaet: Velg kategori Skjema og klikk Ny. Velg Utformingsvisning uten å knytte til noen tabell. Access lager et tomt skjema. Sørg for at tryllestaven i paletten er slått av, og lag en kommandoknapp i skjemaet. Høyreklikk knappen og velg Egenskaper fra hurtigmenyen:

• Endre Tittel under kategori Format til ”Åpne ordreskjema”.

• Sett VedKLikk under kategori Hendelse til makroen ÅpneSkjemaMakro.

Sjekk at kommandoknappen gjør det den skal. Skjemaet er en begynnelse på det som kan bli en hovedmeny, hvor brukeren kan velge å åpne forskjellige skjemaer og rapporter.

6.2 Generelt om makroer Makrospråket er bygget opp fra ca. 50 instruksjoner. Figur 9 viser noen av de mest brukte. En makro er en sekvens av instruksjoner. En makrogruppe er en samling av navngitte makroer.

Instruksjon Beskrivelse

AngiVerdi Sette en verdi inn i en kontroll

GåTilKontroll Flytte fokus til en bestemt kontroll

GåTilPost Gå til første, forrige, neste, siste eller ny rad

KjørBasicKode Utføre en VisualBasic funksjon

KjørSQL Utføre en SQL-setning

Lukk Lukke det aktive vinduet

MeldingsBoks Vise en meldingsboks på skjermen

SkrivUt Skrive ut det aktive vinduet

15

15

SpørPåNytt Oppdatere datagrunnlaget for et skjema

SøkEtterPost Gå til første rad som oppfyller en betingelse

Timeglass Vise et timeglass på skjermen når bruker må vente

ÅpneRapport Åpne en rapport for forhåndsvisning eller utskrift

ÅpneSkjema Åpne et skjema

ÅpneSpørring Utføre en spørring og vise spørreresultatet

ÅpneTabell Åpne en tabell for redigering

Figur 9 Noen instruksjoner i makrospråket

Makroer kan for eksempel brukes til å erstatte de innebygde navigasjonsknappene med egne kommandoknapper:

1. Lag en makro som utfører instruksjonen GåTilPost med Post satt til Neste.

2. Legg til en kommandoknapp uten veiviser.

3. Sett knappens VedKlikk-egenskap til makroen laget i steg 1.

4. Merk knappens Bilde-egenskap og klikk knappen merket med tre prikker. Velg bilde som heter GåTilNeste1 fra listen.

For å beholde oversikten er det nyttig å samle makroer i makrogrupper. Første steg er å tildele makroer navn. Velg først Vis/Makronavn, se Figur 8. Navnet på en makro skal skrives inn i kolonnen Makronavn på første linje i makroen (ÅpneOrdreSkjema i figuren). Vi kan nå legge inn flere makroer, for eksempel en makro for å åpne hvert enkelt skjema i databasen. For å referere til en blant flere makroer i en makrogruppe setter vi sammen navn på makrogruppen med navn på makroen. Eksempel: ÅpneSkjemaMakro.ÅpneOrdreSkjema.

6.3 Søkeskjemaer Det kan være ønskelig å utvide et registreringsskjermbilde med søkemuligheter. La oss si vi har et skjema AnsattSkjema bundet til en tabell Ansatt med en kolonne Etternavn, og vil bygge ut med søkemuligheter på etternavn.

1. Legg til en ubunden tekstboks EtternavnSøk i skjemaet.

2. Lag en spørring som henter ut alle kolonner fra Ansatt og som inneholder følgende betingelse:

WHERE Etternavn = Forms![AnsattSkjema]![EtternavnSøk]

Spørringen henter altså det brukeren har skrevet inn i tekstboksen og bruker dette for å filtrere data.

3. Utfør makroen SpørPåNytt etter oppdatering av tekstboksen.

4. Sett skjemaets Postkilde til spørringen laget i steg 2.

Et problem nå er at WHERE-betingelsen ikke slår til når tekstboksen er tom, noe som er tilfellet i det skjemaet blir åpnet. Forsøk å endre betingelsen til:

WHERE Etternavn LIKE Forms![AnsattSkjema]![EtternavnSøk] & '*'

16

16

Det vi har gjort er altså å bruke en spørring som datagrunnlag for et skjema, la spørringen ta en parameter hentet fra skjemaet (EtternavnSøk), og beregne datagrunnlaget på nytt hver gang brukeren skriver inn en ny søkeverdi. Spørringer danner et filter på den underliggende tabellen.

Vi kan også bruke filtrering i det vi åpner et skjema. Anta vi har laget et registreringsskjema for kunder og vil ha en kommandoknapp som åpner et eget skjema med ordrene som hører til denne kunden:

• Lag kommandoknappen og knytt makroen ÅpneSkjema til VedKlikk-egenskapen. Sett parameteren WHERE-betingelse til:

KNr = Forms![Kundeskjema]![KNr]

Her er det antatt at Kundeskjema er det aktive registreringsskjemaet, og at det inneholder en kontroll med navn KNr.

6.4 Rapportstyring Det er mulig både å forhåndsvise og skrive ut rapporter fra et skjema med instruksjonen ÅpneRapport. I en rapportmeny kan man lage to knapper for hver rapport: ”Forhåndsvis rapport” og ”Skriv ut rapport”. Det er likevel bedre å la brukeren velge rapport fra en liste, og deretter velge forhåndsvisning eller utskrift. Denne formen for rapportstyring lar seg programmere med makroer.

Anta vi har laget tre rapporter, la oss si Rapport1, Rapport2 og Rapport3. Lag så et tomt skjema, aktiver tryllestaven og lag en alternativgruppe med navn AltGruppe. Skriv inn Rapport 1, Rapport 2 og Rapport 3 i listen for etikettnavn. Velg standardverdi hvis en av rapportene vil bli skrevet ut oftere enn de andre. Gi selve alternativgruppen navnet AltGruppe. Lagre skjemaet med navn UtskriftsDialog.

Lag deretter en makro med tre linjer: Hver linje skal inneholde instruksjonen ÅpneRapport (for hver av de tre rapportene). Fyll ut Rapportnavn og sett Visningsmåte til Forhåndsvisning. Sett Betingelse til [AltGruppe]=1 for rapport 1, og tilsvarende for de to andre. Dette gir altså tre instruksjoner med hver sin betingelse. Lagre makroen med navn VisRapporter.

Gå tilbake til skjemaet UtskriftsDialog og legg til en knapp merket ”Forhåndsvisning”. Knytt makroen du har laget til knappens egenskap VedKlikk.

Utvid til slutt med en knapp for å produsere utskrift til papir. Framgangsmåten blir lik, men Visningsmåte settes til Utskrift i stedet for Forhåndsvisning.

I et skjema for rapportstyring er det ofte behov for å lese inn parametere fra brukeren, for eksempel for å bestemme fradato og tildato for tidsrommet rapporten skal hente data fra.

1. Lag en parameterspørring som produserer grunnlaget for rapporten. Fradato og tildato blir parametere.

2. Legg til tekstbokser for fradato og tildato i dialogvinduet for rapportstyring.

3. Modifiser spørringen ved å erstatte parameterene med referanser til tekstboksene i dialogvinduet. WHERE-betingelsen blir typisk som følger, der txtFradato og txtTildato er navnet på tekstboksene og Dato er navnet på en kolonne i tabellen spørringen henter data fra:

WHERE Dato >= Forms![UtskriftsDialog]![txtFradato]

AND Dato <= Forms![UtskriftsDialog]![txtTildato]

17

17

6.5 Veivisere og VisualBasic Alle typer av kontroller kan lages med veivisere. Åpne vareskjemaet i utformingsvisning, sørg for at tryllestaven er aktiv i verktøypaletten og lag en kommandoknapp. Access starter en veiviser i tre steg:

1. Velg kategori Postnavigering og operasjon Gå til neste post.

2. Bestem utseende på knappen. Standardforslaget fra Access er et bilde av en høyrevendt pil.

3. Gi knappen et navn. Dette er kun et internt navn, som blant annet blir brukt for å referere til knappen fra en makro. Gi knappen et meningsfullt navn, for eksempel GåTilNeste. Fullfør og lukk skjemaet.

Veiviseren genererer et lite ”program” som utfører navigasjon til neste post, og setter egenskapen VedKlikk under kategori Hendelse til å referere dette programmet. Det er mulig å få se den genererte programkoden. Merk linjen VedKlikk og klikk i den lille knappen merket med tre prikker til høyre for Hendelsesprosedyre. Access starter et delsystem som heter Visual Basic for Applications (VBA). Dette systemet inneholder blant annet en editor for å skrive programmer i språket Visual Basic. Eksempelkoden vil se omtrent slik ut:

Private Sub GåTilNeste_Click()

On Error GoTo Err_GåTilNeste_Click

DoCmd.GoToRecord , , acNext

Exit_GåTilNeste_Click:

Exit Sub

Err_GåTilNeste_Click:

MsgBox Err.Description

Resume Exit_GåTilNeste_Click

End Sub

Bruk menyvalget File/Close and return to Microsoft Access for å lukke VBA.

7 Samordne Access med andre programmer I en organisasjon er data som regel produsert med en rekke forskjellige verktøy og lagret i flere formater. Ordresystemet med data om varer, kunder og bestillinger blir kanskje ajourholdt i et databasesystem. Regnskapsdata kan være lagt inn i et regneark. Brev blir laget med en tekstbehandler. Det oppstår dermed et behov for å samordne data laget med forskjellige verktøy.

7.1 Import og eksport av data Vi beskriver først hvordan Excel regneark kan importeres til Access. Anta regnearket har samme struktur som databasetabellen vi skal importere til, og at den første raden i regnearket inneholder kolonnenavn.

Velg Fil/Hent eksterne data/Importer. Sett Filtype til Excel og velg regnearkfilen. En veiviser blir startet. Pass på å krysse av for at første rad inneholder overskrifter (kolonnenavn), og velg riktig tabell for lagring. Hvis alt gikk bra gir Access beskjed om hvor mange rader som ble importert.

18

18

I og med at regneark allerede er strukturert i rader og kolonner er import av slike datasett som regel enkel. Tekstfiler er generelt mindre strukturert og kan kreve noe manuelt arbeid i forkant. Følgende er et eksempel på en tekstfil som lar seg importere automatisk til Access:

"VNr";"Betegnelse";"Pris";"KatNr";"Antall";"Hylle"

"10820";"Dukkehår, hvitt";kr 46,50;13;106;"E12"

"10822";"Dukkehår, lys brunt";kr 46,50;13;0;"E12"

"10830";"Nisseskjegg, 30 cm";kr 57,50;13;42;

Hver rad er lagret på en linje i tekstfilen og semikolon er brukt som skilletegn mellom verdier. Tekstverdier er omsluttet av doble apostrofer. Den første linjen inneholder kolonnenavn. Import gjøres også her fra menyvalg Fil/Hent eksterne data/Importer, men Filtype settes til Tekstfiler. I veiviseren må man blant annet krysse av for at første rad inneholder overskrifter og definere semikolon som skilletegn.

Eksport av data gjøres ved å merke en tabell eller spørring i databasevinduet og velge Fil/Eksporter. Velg filnavn og filtype, for eksempel Excel regneark eller tekstfiler.

7.2 Lage flettebrev med Word Tenk at en bedrift skal sende ut tilbudsbrev til sine kunder. Kundedata er lagret i en database. Alle brevene skal være like bortsett fra kundens navn og adresse i brevhodet. Flettebrev er en teknikk for å automatisere denne prosessen. Man tar utgangspunkt i et Word-dokument (brevteksten) og setter inn såkalte flettefelt. Et flettefelt er en referanse til kolonnenavn i databasetabellen. En egen Word-kommando vil til slutt masseprodusere dokumentet og automatisk sette inn data om hver kunde.

Start Word og lag et tilbudsbrev med plass til kundens navn og adresse øverst på arket. Lag også en overskrift: ”Kjære ”. Her skal kundens fornavn inn. Start fletting fra Verktøy-menyen. Word åpner en veiviser der man blant annet kan koble til en Access-database og velge tabell. Vi velger kundetabellen. Etter tilkobling vil kolonnenavnene fra tabellen bli vist på en egen verktøylinje. Plasser markøren øverst i brevet og sett inn flettefeltene Fornavn og Etternavn adskilt av et blankt tegn. Sett inn flettefeltet Adresse på linjen under. Flytt markøren til etter overskriften ”Kjære” og sett inn Fornavn. Når vi starter selve flettingen vil Word kombinere brevmalen med data fra Access-tabellen, slik at vi får ett brev til hver kunde. Resultatet kan lagres i et nytt dokument, eller skrives ut.

Veiviseren for å lage flettebrev kan også startes fra Access, menyvalg Verktøy/Office-koblinger. Fra denne menyen kan man også enkelt overføre tabelldata til et Excel regneark.

7.3 Automatisere Excel fra Access Anta vi har en spørring som heter SalgPrKategori og et skjema med en kommandoknapp med navn TilExcel. Spørreresultatet inneholder to kolonner Navn og Salg som inneholder henholdsvis navnet på en kategori og samlet salg for denne kategorien.

Hvis vi knytter prosedyren under til hendelsen VedKlikk vil Access starte Excel, opprette en ny arbeidsbok og sette inn spørreresultatet i kolonne A og B i Ark1. Deretter blir det generert et kakediagram som viser salg pr. kategori. Arbeidsboken med diagrammet vil vises på skjermen, men lagres ikke automatisk.

19

19

Private Sub TilExcel_Click()

Dim ExcelObj As New Excel.Application

Dim bok As Excel.Workbook

Dim ark As Excel.Worksheet

Dim diagram As ChartObject

Dim rs As Recordset

Dim i As Integer

' Start Excel med en ny arbeidsbok og velg Ark1

ExcelObj.Visible = True

Set bok = ExcelObj.Workbooks.Add

Set ark = bok.Worksheets("Ark1")

' Åpne spørring SalgPrKategori i aktiv database

Set rs = CurrentDb.OpenRecordset("SalgPrKategori")

' Sett bredde på kolonne A og B

ark.Columns("A:B").ColumnWidth = 14

i = 1 ' Initialiserer en tellevariabel

' Behandle hver rad i spørreresultatet

Do

ark.Cells(i, 1) = rs!Navn ' Kategori i kolonne 1

ark.Cells(i, 2) = rs!Salg ' Salg i kolonne 2

i = i + 1

rs.MoveNext ' Hent neste rad

Loop Until rs.EOF ' Gjenta til slutten (EOF=End Of File)

' Nå er regnearket fylt med datagrunnlaget

' Lag et nytt diagram og gjør det aktivt

Set diagram = ark.ChartObjects.Add(156, 0, 500, 300)

diagram.Activate

' Diagrammet skal være et kakediagram

diagram.Chart.ChartType = xl3DPieExploded

diagram.Chart.ChartArea.Border.LineStyle = xlNone

20

20

' Diagrammet skal hente data fra kolonne A og B

diagram.Chart.SeriesCollection.Add _

Source:=bok.Sheets("Ark1").Range("A1:B" & i - 1)

' Gir diagrammet en overskrift

diagram.Chart.HasTitle = True

diagram.Chart.ChartTitle.Text = "Salg pr kategori"

' Gir diagrammet en forklaring (fargene til kategoriene)

diagram.Chart.Legend.Position = xlLegendPositionBottom

' Går til celle A1

ark.Cells(1, 1).Activate

End Sub

8 Begreper • Database, databasehåndteringssystem, MDB-fil

• Menylinje, verktøylinje, statuslinje, arbeidsområde, databasevindu

• Databaseobjekt: tabell, spørring, skjema, rapport, side, makro, modul

• Tabell, feltstørrelse, valideringsregel, standardverdi, obligatorisk, indeksert, primærnøkkel, fremmednøkkel

• Spørring, spørreresultat, betingelse, sortering

• Relasjonsvindu, forhold

• Veiviser

• Skjema, kontroll, egenskap

• Rapport, sidenivå, detaljnivå, gruppenivå

• Makro, instruksjon, makrogruppe, betingelse

• VisualBasic, funksjon

• Import, eksport

• Flettebrev, flettefelt

9 Oppgaver 1. Ta utgangspunkt i databasen til Hobbyhuset.

a. Lag en spørring som viser alle kunder sortert på etternavn.

b. Lag en spørring som viser alle varer i kategori 3 som koster mer enn 100 kr.

c. Lag en spørring som viser alle ordre påført navn på kunden. Tips: Bruk to tabeller.

21

21

d. Lag en varerapport. Samtlige varer skal skrives ut, ordnet etter kategori og sortert på varenavn innen hver kategori. Antall varer i hver kategori skal også skrives ut. Tips: Gruppering på kategori.

e. Lag et registreringsskjema for ajourhold av kunder. Utvid med søkemuligheter med hensyn på kundenavn.

f. Lag et hoved/del-skjema for ordreregistrering. Tips: Bruk tabellene Ordre og Ordrelinje.

g. Eksporter salgsdata til et regneark og lag et diagram som viser utviklingen av salg fordelt på varekategorier siste 6 måneder. Merk: Teknikker for presentasjon i regneark er ikke beskrevet her.

2. Lag en ny database.

a. Opprett en ny tabell i utformingsvisning. Tabellen skal ha følgende kolonner:

• AnsattID av datatype Autonummer.

• Etternavn av datatype Tekst med lengde 15.

• Fornavn av datatype Tekst med lengde 30.

• Stilling av datatype Tekst med lengde 20.

• Lønn av datatype Valuta.

• Adresse av datatype Tekst med lengde 40.

• AnsattDato av datatype Dato med format Middels dato.

La AnsattID være primærnøkkel. Lagre tabellen og gi den navn Ansatt. Legg inn noen rader med data.

b. Bruk en veiviser for å lage et skjema for ajourhold av tabellen. Legg til egne kommandoknapper for å navigere i tabellen. Bruk skjemaet for å registrere noen flere ansatte.

c. Lag et flettebrev til alle ansatte med invitasjon til julebord.

Litteratur

Kristoffersen B. (2007) Databasesystemer, Universitetsforlaget.

Viescas J. L. (2003) Microsoft Office Access 2003 Inside Out. Microsoft Press.