From Access to SQL Server

342 Pages·2000·14.557 MB·English

by  Russell Sinclair (auth.)

From Access to SO-L Server
RUSSELL SINCLAIR
APress Media, LLC
From Access to SQL Server
Copyright by Russell Sinclair
~2000
Originally published by Apress in 2000
Ali rights reserved. No part ofthis work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information stor
age or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-893115-24-8 ISBN 978-1-4302-1131-0 (eBook)
DOI 10.1007/978-1-4302-1131-0
'fiademarked names may appear in this boole. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the ben
efit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewer: Peter Vogel
Editor: Anne Marie Walker
Production Editor: Janet Vaii
Page Compositior and Cat Herder: Susan Glinert
Indexer: Valerie Perry
Cover and Interior Design: Derek Yee Design
In the United States, phone 1-800-SPRINGER; orders@springer-ny. corn;
www.springer-ny.com
Outside the United States, contact [email protected]; www.springer.de; fax
[phone]
The information in this book is distributed onan "as is" hasis, without warranty. Although every
precaution bas been taken in the preparation of this work, neither the author nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
Contents at a Glance
Introduction ........ ........ ....... ........ ....... ..... ... ........ ... .. ...... .... .... ....... ........ .... xi
Chapter 1 What Every Access Programmer
Needs to Know about SQL Server ................................................................. 1
Chapter 2 Why Migrate? ................................................................................. 25
Chapter 3 Planning the Upgrade ................................................................ 37
Chapter 4 The Microsoft Wizard ................................................................ 59
Chapter 5 Data Transformation Services .............................................. 99
Chapter 6 Using SQL Server 6.5 .............................................................. 141
Chapter 7 Using SQL Server 7 .................................................................. 175
Chapter 8 Enabling Data Access .............................................................. 207
Chapter 9 Stored Procedures .................................................................... 221
Chapter 10 Getting Connected .................................................................... 249
Chapter 11 Coding with SQL Server Data .............................................. 281
Chapter 12 Optimizing Access for SQL Server ................................... 307
Appendix A Selected Tables ........................................................................ 321
Index ....................................................................................................................... 325
iii
Contents
Introduction ....................................................................................................... xi
Chapter 1 What Every Access Programmer
Needs to Know about SQL Server ............................. 1
SQL Server Architecture .................................................................................... 1
Transact-SQL ........................................................................................................ 3
Main Database Components .............................................................................. 4
Database Files ....................................................................................................... 5
Security Modes ..................................................................................................... 5
System Databases ................................................................................................ 6
Database Objects .................................................................................................. 8
Using Enterprise Manager (SQL 6.5) .......................................................... 13
Viewing Data. ...................................................................................................... 16
Using Enterprise Manager (SQL 7.0) .......................................................... 18
Viewing Data. ...................................................................................................... 21
Finding Information ........................................................................................... 22
Chapter 2 Why Migrate? ......................................................................... 25
What Is Migrating? ............................................................................................. 25
Access versus SQL Server ................................................................................ 27
Size Constraints .................................................................................................. 27
Transferring Data ............................................................................................... 27
Multi-User Constraints ...................................................................................... 29
Maintenance and Ad.Ininistration .................................................................... 30
Replication .......................................................................................................... 30
Cost ...................................................................................................................... 31
Reasons to Migrate ............................................................................................. 32
Size ...................................................................................................................... 32
Number of Users ................................................................................................ 32
Network Traffic ................................................................................................... 33
Response Issues .................................................................................................. 33
Maintenance and Ad.Ininistration .................................................................... 33
Replication .......................................................................................................... 34
v
Reasons Not to Migrate ..................................................................................... 34
Size ....................................................................................................................... 34
Organization of Data .......................................................................................... 34
User Connectivity ............................................................................................... 35
Cost ...................................................................................................................... 35
Migration Considerations ................................................................................ 35
Chapter 3 Planning the Upgrade .....................................................
37
Planning How Data Will Be Accessed ........................................................... 37
Data Access Methods ......................................................................................... 37
Analyzing Objects ................................................................................................ 40
Tables .................................................................................................................. 41
Queries ................................................................................................................ 47
Fonns ................................................................................................................... 49
Reports ................................................................................................................ 52
Macros ................................................................................................................. 52
Modules ............................................................................................................... 53
General Considerations ..................................................................................... 55
Documentation ........................................................................................................ 55
Chapter 4 The Microsoft Wizard .....................................................
59
Introducing the Upsizing Wizard ................................................................. 59
Obtaining the WIZard ......................................................................................... 60
Preparing to Use the WIZard .............................................................................. 60
Running the WIZard ........................................................................................... 67
Evaluating the Wizard ....................................................................................... 92
What the WIZard Does and Doesn't Do Well .................................................... 92
Recommendations ............................................................................................. 94
Chapter 5 Data Transformation Services ................................
99
What Is Data Transformation Services? .................................................... 99
DTS Architecture ................................................................................................ 99
Relevance to an Access Migration ................................................................... 101
Accessing DTS ................................................................................................... 103
Scripting ............................................................................................................ 132
Using DTS with the Upsizing Wizard ......................................................... 138
A Final Word about DTS ................................................................................... 139
vi
Contents
Chapter 6 Using SQL Server 6.5 ................................................... 141
Using SQL Server 6.5 ....................................................................................... 141
Managing Devices............................................................................................ 142
Managing Databases........................................................................................ 148
Managing Logins .............................................................................................. 154
Managing Database Objects ........................................................................... 156
Other Tools ....................................................................................................... 170
A Special Note on Security .............................................................................. 171
Moving On ............................................................................................................... 172
Chapter 7 Using SQL Server ] ........................................................ 175
Using SQL Server 7 ........................................................................................... 175
Managing Databases ........................................................................................ 177
Managing Logins .............................................................................................. 183
Managing Database Objects ........................................................................... 186
Other Tools ....................................................................................................... 204
A Special Note on Security .............................................................................. 205
Moving On ............................................................................................................... 206
Chapter 8 Enabling Data Access ................................................... 207
Tables ..................................................................................................................... 207
Choosing the Appropriate Data Type ............................................................. 207
Indexing ............................................................................................................ 211
Timestamps ...................................................................................................... 214
Views ........................................................................................................................ 215
CreatingViews .................................................................................................. 216
WhyUseViews ................................................................................................. 218
An Ounce of Prevention .................................................................................. 219
Chapter 9 Stored Procedures .......................................................... 221
Using Stored Procedures ................................................................................ 221
What Stored Procedures Can Do .................................................................... 221
Stored Procedure Types ................................................................................... 222
Components ..................................................................................................... 222
Stored Procedure Syntax ................................................................................. 223
Coding Stored Procedures ............................................................................... 226
vii
Migrating to Stored Procedures ................................................................. 240
Migrating Queries ............................................................................................ 240
Migrating Macros ............................................................................................. 242
Migrating Code ................................................................................................. 243
Examples ................................................................................................................. 244
Testing ................................................................................................................... 247
SQL Power Tools .................................................................................................. 248
Chapter 1.0 Getting Connected .......................................................... 249
Choosing a Connection Method ..................................................................... 249
ODBC ................................................................................................................ 249
Access Database Projects ................................................................................. 271
Chapter 1.1. Coding with SQL Server Data ................................. 2B1
Data Coding Choices ......................................................................................... 281
Data Access Objects (DAO) ............................................................................. 281
ODBCDirect ...................................................................................................... 282
ActiveX Data Objects (ADO) ............................................................................ 282
Choosing a Data Access Technology .............................................................. 282
Using DAO to Manage Local Objects ............................................................. 284
Working with Data ........................................................................................... 295
The Choice Is Yours ......................................................................................... 305
Chapter 1.2 Optimizing Access for SQL Server .................... 307
Reducing Data Transfers ................................................................................ 307
Filtering Data on the Server ............................................................................ 308
Bound Controls ................................................................................................ 308
Access Settings ................................................................................................. 309
Oient-Side Processing ..................................................................................... 310
Increasing Throughput ..................................................................................... 311
SQLDatabaseDesign ....................................................................................... 311
Access Database Design .................................................................................. 312
A Plan for the Future ..................................................................................... 313
Tip #1: Use Queries to Enhance Security ....................................................... 314
Tip #2: Perform Bulk Updates Using Stored Queries .................................... 314
Tip #3: Use Parameters Now ........................................................................... 315
Tip #4: Increase Maintainability with Stored Queries ................................... 317
Tip #5: Use ADO for Coding against Data ...................................................... 318
A Final Word ........................................................................................................ 319
viii
Contents
Appendix A Selected Tables ................................................................ 321
SQL Server Data Types ......................................................................................3 21
Microsoft Access ODBC Registry Settings .............................................. 322
Index ....................................................................................................................... 325
ix
Introduction
There is considerably more TO MIGRATING AN ACCESS DATABASE TO SQL SERVER THAN
Microsoft would have you believe. The fact that you are reading this introduction
leads me to believe that you already know this, and that you are looking for more
answers. This book provides those answers.
You may be wondering right now if this book is for you. Let me help you answer
that question. This book is written primarily for Microsoft Access developers
those of you who create applications using Microsoft Access both as the database
that stores the data, and as the development environment in which the user inter
face is created and maintained. The main purpose of this book is to aid Access
developers in understanding how to modify their database and application, so
that the data side of the application can be moved to SQL Server. All of the tech
niques described to access data and the information on how to plan the move
assume you are migrating the back-end database only. Although this book does
concentrate a great deal on Access as an interface development environment,
Visual Basic developers and others may find this information helpful because
many of the concepts can easily be applied to pure Visual Basic applications in
which the database itself is being moved from Access to SQL Server.
In writing this book, I made a few assumptions about you, the reader, and your
experience developing applications with Microsoft Access. I made the assumption
that you have experience creating databases and user interfaces in Microsoft
Access and know how to make the two cooperate. This means that you know how
to design tables, queries, forms, and reports in the IDE. I assumed that you have at
least tried to create an Access application that has separate database files for the
back-end data and the front-end interface. It is not required but I would recom
mend that you understand at least how to do this. Finally, I made the assumption
that you have done at least a little Visual Basic for Applications (VBA) program
ming. This is because the vast majority of Access development requires at least
some rudimentary VBA programming. Although you can get by without needing
any VBA code, Access really shines when you start to program it using VBA.
Where 's the CD?
Okay, you caught me. This is probably one of the few application-specific books you
have picked up in recent years that doesn't come with a CD (or a least a diskette).
You may be wondering why.
When I originally started to write this book, I had every intention of creating a
CD that you could use to follow the techniques described in each chapter.
xi