Gevorderden (SQL server)

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: <[email protected]>
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)