Access 2002 Gevorderden (SQL server) Roger Frans met cd-rom
campinia
media
Frans, Roger – Access 2002 – gevorderden (SQL) / Roger Frans; Geel: Campinia Media vzw, 2003; 212 p; index; 29 cm; gelijmd. ISBN: 90.356.1152.7; NUGI 854; UDC 681.3.06; Wettelijk depot België: D/2003/3941/11 Campinia Media vzw Kleinhoefstraat 4 B – 2440 – GEEL (Belgium) Tel.: (+32) 014/59 09 59 Fax: (+32) 014/59 03 44 e-Mail:
URL: <www.campiniamedia.be> Copyright 2003:
No part of this book may be reproduced in any form, by print, photoprint, microfilm or any other means without written permission of the publisher. Niets uit deze uitgave mag worden verveelvoudigd en/of openbaar gemaakt door middel van druk, fotokopie, print, microfilm of op welke andere wijze ook, zonder voorafgaande schriftelijke toestemming van de uitgever.
Inleiding In de cursus Access 2002 – gevorderden (Jet) hebt u leren programmeren in Visual Basic for Applications in Access. U hebt er ook kennis gemaakt met het ADO-objectmodel dat u toelaat om gegevens in een database via programmacode te benaderen. In deze cursus, Access 2002 – gevorderden (SQL Server) leren we u werken met Access-projecten, Access Data Projects of ADP-projecten. In een Access-project gebruikt u Access als front end en SQL Server als back end in een client/server-omgeving. U gebruikt een ADP-project meestal indien u grotere databases wenst te beheren en/of meerdere gebruikers toegang tot deze gegevens wenst te geven. Dit vergt een andere aanpak dan het werken met gegevens die op één enkele pc worden beheerd. U moet bv. trachten zo weinig mogelijk gegevens over het netwerk te sturen. Ook moet u er rekening mee houden dat andere gebruikers de gegevens ook bewerken. De tools die Access levert om de gegevens uit de SQL Server database te beheren, zijn beperkt. SQL Server levert u betere hulpmiddelen. We installeren deze al in het eerste hoofdstuk. We leren u deze tools gebruiken in het zevende hoofdstuk. U mag het hoofdstuk echter al vroeger bestuderen. We leren u systematisch werken met ADP-projecten. Hiervoor is het ook noodzakelijk dat u de nodige kennis opdoet van de taal T-SQL. Dit is de SQL-versie die SQL Server gebruikt. We geven een overzicht van de taal in het achtste hoofdstuk. Ook dat hoofdstuk mag u dus tussendoor al aansnijden. Access 2002 – gevorderden (SQL Server) is een doe-cursus. Dit betekent dat u de cursus doorneemt terwijl u voor de computer zit. U neemt per hoofdstuk de cursustekst door. Daarna maakt u de oefeningen. Bij de cursus hoort een cd-rom. Op deze cd-rom staan voorbeelden en oefeningen die u uitwerkt in deze cursus. U vindt er ook een bestand Lees mij.doc. Hierin staat last minute informatie. U kan het bestand lezen door te dubbelklikken op het bestand of door het bestand te openen in een tekstverwerkingspakket. Tot slot van deze inleiding dank ik éénieder die geholpen heeft bij het tot stand komen van deze cursus. Opmerkingen die een latere uitgave kunnen verbeteren, zijn welkom. Roger Frans april 2003
Inleiding - 1
2 – Access 2002 – gevorderden (SQL Server)
Inhoudsopgave
INLEIDING ..................................................................................................................1 INHOUDSOPGAVE ....................................................................................................3 1 1.1
MICROSOFT ACCESS PROJECTEN..................................................................9 Inleiding .................................................................................................................................................... 9
1.2 Client/server tegenover file/server .......................................................................................................... 9 1.2.1 Bestandsserver (file/server).................................................................................................................... 9 1.2.2 Client/server......................................................................................................................................... 10 1.2.3 Waarom een client/server-applicatie? .................................................................................................. 10 1.3
OLE DB en ADO .................................................................................................................................... 11
1.4 Microsoft Access Project........................................................................................................................ 11 1.4.1 Microsoft Access Project ..................................................................................................................... 11 1.4.2 SQL Server .......................................................................................................................................... 12 1.4.3 MSDE ten opzichte van SQL Server ................................................................................................... 13 1.4.4 Documentatie ....................................................................................................................................... 13 1.5 SQL Server Desktop Edition installeren .............................................................................................. 13 1.5.1 Vooraf .................................................................................................................................................. 13 1.5.2 Installatie.............................................................................................................................................. 13 1.5.3 Opstarten van de database.................................................................................................................... 14 1.5.4 Installatie van de tools ......................................................................................................................... 15 1.5.5 Books Online ....................................................................................................................................... 20 1.6 Microsoft Access Projecten.................................................................................................................... 20 1.6.1 Access Data Project ............................................................................................................................. 20 1.6.2 Noordenwind openen ........................................................................................................................... 20 1.6.3 Tabellen ............................................................................................................................................... 22 1.6.4 Weergaven, functies en opgeslagen procedures................................................................................... 23 1.6.5 Databasediagrammen ........................................................................................................................... 24 1.6.6 Andere objecten ................................................................................................................................... 25 1.7 Een nieuwe toepassing............................................................................................................................ 26 1.7.1 Omschrijving ....................................................................................................................................... 26 1.7.2 Een data-model .................................................................................................................................... 26 1.8
Een nieuw project aanmaken ................................................................................................................ 27
1.9 Tabel aanmaken ..................................................................................................................................... 28 1.9.1 Ontwerpvenster van een tabel .............................................................................................................. 28 1.9.2 De tabel tblContacten........................................................................................................................... 29 1.9.3 De tabel tblContactpersonen ................................................................................................................ 31 1.9.4 De tabel tblHuurcontracten .................................................................................................................. 32 1.9.5 De tabel tblHuurcontractDetaillijnen ................................................................................................... 33 1.9.6 De tabel tblLokalen.............................................................................................................................. 34 1.10
Check Constraints (controlebeperkingen) ........................................................................................... 34
1.11 Relaties .................................................................................................................................................... 35 1.11.1 Aanbrengen relaties ......................................................................................................................... 35 1.11.2 Eigenschappen................................................................................................................................. 37 1.12 Gegevenstypen ........................................................................................................................................ 39 1.12.1 Binaire gegevens ............................................................................................................................. 39 1.12.2 Tekst ................................................................................................................................................ 39 1.12.3 Datum en tijd ................................................................................................................................... 39 1.12.4 Numerieke gegevens........................................................................................................................ 40 1.12.5 Valutagegevens................................................................................................................................ 40
Inhoudsopgave - 3
1.12.6
Speciale typen ..................................................................................................................................41
1.13 Databasehulpprogramma's....................................................................................................................41 1.13.1 Wizard Upsize .................................................................................................................................41 1.13.2 Reservekopie van SQL-database .....................................................................................................44 1.13.3 SQL-database herstellen ..................................................................................................................45 1.13.4 Databaseoverdracht..........................................................................................................................45 1.13.5 Databasebestand kopiëren................................................................................................................45 1.13.6 Een SQL Server database verwijderen.............................................................................................46 1.14
Een project aanmaken en bestaande gegevens gebruiken ..................................................................46
1.15
Oefeningen...............................................................................................................................................47
2 2.1
VIEWS, STORED PROCEDURES, FUNCTIONS.............................................. 51 Inleiding...................................................................................................................................................51
2.2 Importeren van gegevens .......................................................................................................................51 2.2.1 Importeren van contacten .....................................................................................................................51 2.2.2 Toevoegen van de contactpersonen......................................................................................................52 2.2.3 Importeren van de gegevens m.b.t. de lokalen .....................................................................................52 2.3 Views........................................................................................................................................................54 2.3.1 Wat is een view? ..................................................................................................................................54 2.3.2 Creëren van een view ...........................................................................................................................54 2.3.3 Ordenen van gegevens .........................................................................................................................57 2.3.4 Berekende kolom en aliassen ...............................................................................................................58 2.3.5 Criteria .................................................................................................................................................59 2.3.6 Joins .....................................................................................................................................................60 2.3.7 Groeperen van gegevens ......................................................................................................................62 2.4 Stored procedures...................................................................................................................................63 2.4.1 Aanmaken van een stored procedure....................................................................................................63 2.4.2 Een INSERT-query ..............................................................................................................................64 2.4.3 Een UPDATE-query ............................................................................................................................65 2.4.4 Een DELETE-query .............................................................................................................................66 2.4.5 Ontwerpmodus of SQL-code ...............................................................................................................66 2.5 Gebruikersgedefinieerde functies..........................................................................................................67 2.5.1 Scalaire functies ...................................................................................................................................67 2.5.2 In line functie .......................................................................................................................................69 2.5.3 Tabelwaardefunctie ..............................................................................................................................70 2.6
Triggers ...................................................................................................................................................71
2.7
Oefeningen...............................................................................................................................................71
3 3.1
FORMULIEREN IN EEN ACCESS-PROJECT .................................................. 73 Inleiding...................................................................................................................................................73
3.2 Een eenvoudig formulier........................................................................................................................73 3.2.1 Het formulier aanpassen.......................................................................................................................74 3.2.2 Recordbron (RecordSource).................................................................................................................75 3.2.3 Recordbronkwalificatie (RecordSourceQualifier)................................................................................75 3.2.4 Type recordset (RecordsetType) ..........................................................................................................75 3.2.5 Maximum aantal records......................................................................................................................75 3.2.6 Unieke tabel .........................................................................................................................................75 3.3
Een hoofd- en subformulier ...................................................................................................................76
3.4
Een view als recordbron van een keuzelijst..........................................................................................78
3.5 Het hoofd- en subformulier voor huurcontracten ...............................................................................80 3.5.1 Aanmaken van het formulier................................................................................................................80 3.5.2 Synchroniseren van twee keuzelijsten..................................................................................................81 3.5.3 Beperken van de inhoud van een keuzelijst .........................................................................................81 3.5.4 Een oplossing met EXEC.....................................................................................................................83 3.6
Oproepen van een ander formulier.......................................................................................................84
4 – Access 2002 – gevorderden (SQL Server)
3.6.1 3.6.2 3.6.3
Aanmaken van het op te roepen formulier ........................................................................................... 84 De recordbron is een tabel ................................................................................................................... 84 De recordbron is een stored procedure................................................................................................. 84
3.7
Opties....................................................................................................................................................... 85
3.8
Oefeningen .............................................................................................................................................. 86
4
RAPPORTEN .....................................................................................................89
4.1
Inleiding .................................................................................................................................................. 89
4.2
Een eenvoudig hoofd- en subrapport.................................................................................................... 89
4.3
De recordbron van het subrapport is een stored procedure............................................................... 93
4.4
Oefeningen .............................................................................................................................................. 94
5
ACTIVEX DATA OBJECTS ...............................................................................97
5.1
Inleiding .................................................................................................................................................. 97
5.2
Objectenbibliotheken van ADO ............................................................................................................ 97
5.3
Het objectmodel ADODB ...................................................................................................................... 98
5.4 Onafhankelijk formulier........................................................................................................................ 99 5.4.1 Het formulier frmLokaalO ................................................................................................................... 99 5.4.2 Keuzelijst cboLokaal ......................................................................................................................... 100 5.4.3 Bij het openen van het formulier........................................................................................................ 100 5.5 Een record selecteren ........................................................................................................................... 101 5.5.1 Selecteren van een record .................................................................................................................. 101 5.5.2 Aanmaken van de stored procedure ................................................................................................... 102 5.6 Een record toevoegen ........................................................................................................................... 103 5.6.1 De programmacode achter de knop Toevoegen ................................................................................. 103 5.6.2 Bewaren van de gegevens .................................................................................................................. 104 5.6.3 Declaratie in een algemene module ................................................................................................... 106 5.6.4 De stored procedure spoLokalenInsert............................................................................................... 107 5.7 Bewerken van een record..................................................................................................................... 107 5.7.1 De programmacode achter de knop Bewerken................................................................................... 107 5.7.2 De stored procedure spoLokalenUpdate ............................................................................................ 108 5.8 Een record verwijderen ....................................................................................................................... 108 5.8.1 Een record verwijderen ...................................................................................................................... 108 5.8.2 De stored procedure spoLokalenDelete ............................................................................................. 109 5.9
De knop Annuleren .............................................................................................................................. 109
5.10
De knop Sluiten..................................................................................................................................... 110
5.11 Het Connection-object ......................................................................................................................... 110 5.11.1 Basisprincipes van het Connection-object..................................................................................... 110 5.11.2 Een (andere) connectie openen...................................................................................................... 111 5.11.3 Eigenschappen van een Connection-object ................................................................................... 111 5.11.4 Providers........................................................................................................................................ 112 5.12 Command-object .................................................................................................................................. 112 5.12.1 Algemeen....................................................................................................................................... 112 5.12.2 Parameters ..................................................................................................................................... 114 5.12.3 Returnwaarden............................................................................................................................... 116 5.13
Recordset............................................................................................................................................... 116
5.14 Bijkomende voorbeelden ..................................................................................................................... 116 5.14.1 Uitvoerparameter en @@IDENTITY ........................................................................................... 116 5.14.2 Een parameter rechtstreeks meegeven met een Command-object................................................. 118 5.14.3 Parameters meegeven via een array............................................................................................... 119 5.15
6
Oefeningen ............................................................................................................................................ 120
RECORDSET-OBJECTEN...............................................................................123 Inhoudsopgave - 5
6.1
Inleiding.................................................................................................................................................123
6.2 Openen van een recordset ....................................................................................................................123 6.2.1 De eigenschap CursorType ................................................................................................................123 x Forward-Only cursor ..............................................................................................................................123 x Static cursor ............................................................................................................................................123 x Keyset cursor ..........................................................................................................................................124 x Dynamic cursor ......................................................................................................................................124 6.2.2 De eigenschap CursorLocation ..........................................................................................................124 6.2.3 De eigenschap LockType ...................................................................................................................124 6.2.4 Options ...............................................................................................................................................125 6.2.5 Combineren van de eigenschappen ....................................................................................................125 6.3 Enkele voorbeelden...............................................................................................................................125 6.3.1 Pessimistic locking.............................................................................................................................125 6.3.2 Optimistic locking..............................................................................................................................126 6.4
Bewerken, toevoegen en verwijderen van een record........................................................................128
6.5
De methode Find...................................................................................................................................129
6.6
De eigenschap Filter .............................................................................................................................129
6.7
De methode GetRows ...........................................................................................................................129
6.8
De methode GetString ..........................................................................................................................130
6.9
Bookmarks ............................................................................................................................................131
6.10
Batchverwerking...................................................................................................................................131
6.11
Oefeningen.............................................................................................................................................133
7 7.1
BEHEER VAN SQL SERVER.......................................................................... 137 Inleiding.................................................................................................................................................137
7.2 Enterprise Manager .............................................................................................................................137 7.2.1 Starten van de Enterprise Manager ....................................................................................................137 7.2.2 Registreren van een server .................................................................................................................139 7.2.3 Het Taskpad .......................................................................................................................................142 7.2.4 De server stoppen en starten...............................................................................................................143 7.3 Databases...............................................................................................................................................143 7.3.1 Bestaande databases ...........................................................................................................................143 7.3.2 Aanmaken van een database...............................................................................................................144 7.3.3 Eigenschappen van een database........................................................................................................148 7.3.4 Verwijderen van een database............................................................................................................148 7.4 Back-up maken van een database .......................................................................................................148 7.4.1 De Backup Wizard .............................................................................................................................148 7.4.2 Het dialoogvenster SQL Server Backup.............................................................................................152 7.5
Een database herstellen........................................................................................................................153
7.6
Eigenschappen van SQL Server ..........................................................................................................154
7.7 SQL Query Analyzer............................................................................................................................155 7.7.1 Opstarten van SQL Query Analyzer ..................................................................................................155 7.7.2 Een SQL query ingeven .....................................................................................................................156 7.7.3 Een SQL query bewaren en opnieuw openen.....................................................................................157 7.8
8 8.1
Oefeningen.............................................................................................................................................157
TRANSACT-SQL ............................................................................................. 159 Inleiding.................................................................................................................................................159
8.2 T-SQL-operatoren ................................................................................................................................159 8.2.1 Rekenkundige operatoren...................................................................................................................159 8.2.2 Vergelijkingsoperatoren .....................................................................................................................159 8.2.3 Logische operatoren ...........................................................................................................................160 8.2.4 Concatenatie.......................................................................................................................................160
6 – Access 2002 – gevorderden (SQL Server)
8.3 T-SQL-functies ..................................................................................................................................... 160 8.3.1 Datum- en tijdfuncties........................................................................................................................ 160 8.3.2 Wiskundige functies........................................................................................................................... 161 8.3.3 Aggregaatsfuncties............................................................................................................................. 163 8.3.4 Stringfuncties ..................................................................................................................................... 163 8.3.5 Systeemfuncties ................................................................................................................................. 165 8.3.6 @@-functies ...................................................................................................................................... 166 8.4
Variabelen............................................................................................................................................. 166
8.5 Controlestructuren............................................................................................................................... 167 8.5.1 Selectie (IF ELSE) ............................................................................................................................. 167 8.5.2 Meervoudige selectie (CASE) ........................................................................................................... 167 8.5.3 Iteratie (WHILE)................................................................................................................................ 168 8.5.4 GOTO ................................................................................................................................................ 169 8.6
SET NOCOUNT................................................................................................................................... 170
8.7 Transacties ............................................................................................................................................ 170 8.7.1 Wanneer gebruikt u transacties? ........................................................................................................ 170 8.7.2 Syntaxis.............................................................................................................................................. 170 8.8
9 9.1
Oefeningen ............................................................................................................................................ 171
TOEPASSINGEN .............................................................................................173 Inleiding ................................................................................................................................................ 173
9.2 Een zoekfunctie i.p.v. een keuzelijst.................................................................................................... 173 9.2.1 Probleemstelling ................................................................................................................................ 173 9.2.2 De programmacode achter cmdZoek ................................................................................................. 174 9.2.3 Het formulier met de gevonden items ................................................................................................ 176 9.2.4 Tonen van de gegevens ...................................................................................................................... 177 9.3 Een toepassing voor meerdere gebruikers ......................................................................................... 178 9.3.1 Een formulier voor meerdere gebruikers - werkwijze........................................................................ 178 9.3.2 Gegevens opvragen............................................................................................................................ 179 9.3.3 Gegevens toevoegen .......................................................................................................................... 180 9.3.4 Gegevens bewerken ........................................................................................................................... 180 9.3.5 Een record verwijderen ...................................................................................................................... 181 9.3.6 Bewaren van de gegevens .................................................................................................................. 182 9.3.7 De overige programmacode ............................................................................................................... 186 9.4
Fouten.................................................................................................................................................... 188
9.5
Oefeningen ............................................................................................................................................ 189
10
BEVEILIGING ...............................................................................................191
10.1
Inleiding ................................................................................................................................................ 191
10.2
ADE-bestand......................................................................................................................................... 191
10.3
Enkel VBA-modules beveiligen ........................................................................................................... 192
10.4
Beveiliging in SQL Server ................................................................................................................... 192
10.5
Toegang tot SQL Server ...................................................................................................................... 193
10.6 Logins .................................................................................................................................................... 194 10.6.1 De login sa..................................................................................................................................... 194 10.6.2 Een nieuwe login maken voor SQL Server ................................................................................... 194 10.6.3 Een nieuwe login maken voor een Windows-gebruiker ................................................................ 198 10.6.4 Logins beheren .............................................................................................................................. 198 10.6.5 Serverrollen ................................................................................................................................... 200 10.7 Beveiliging op een database ................................................................................................................. 201 10.7.1 Gebruiker toevoegen...................................................................................................................... 201 10.7.2 Databaserollen ............................................................................................................................... 203 10.7.3 Ownership...................................................................................................................................... 206 10.8 Rechten op objecten ............................................................................................................................. 206 10.8.1 Rechten per object instellen........................................................................................................... 207
Inhoudsopgave - 7
10.8.2 10.9
Rechten per gebruiker of databaserol.............................................................................................207
Application role ....................................................................................................................................208
TREFWOORDENREGISTER ................................................................................. 209
8 – Access 2002 – gevorderden (SQL Server)
