From Access to SQL Server

1
From Access to SQL Server

• ATB 406 Fall 2003

2
Intro to SQL Server

• What is SQL Server?
• Major components of SQL Server
• Access and SQL Server
• Upsizing an Access database to SQL Server
• Working with tables in Enterprise Manager
• SQL Server via ADO
• Learning more about SQL Server

3
Some logistical preliminaries

• Need to set the default printer to anything other
  than Sycamore.
• Need to register the SQL Server were going to
  use today
• Start Enterprise Manager from the SQL Server
  program group on the Start menu
• right click on SQL Server Group and select New
  SQL Server Registration
• Wizard time select ORADEV when prompted for a
  SQL Server to register

4
What is SQL Server?

• Microsofts industrial strength database
  management system
• Intended for client/server architecture
• Includes numerous client side tools for creating,
  modifying and working with SQL Server databases
• It is NOT a front end development tool (i.e. no
  Forms, Reports, VB code modules as in Access)
• Microsoft battling Oracle and IBM (DB2) for
  dominance in the corporate database market
• SQL Server using price, performance, ease of use
  and Microsoft ubiquity as marketing levers

5
Access Two tools in one
(1) Tools for creating Forms, Reports, Macros,
Modules
(2) Database engine (Jet) .mdb file is container
for both data tables as well as front end
elements.
SQL Server
Web applications
SQL Server client side tools such as Enterprise
Manager, Query Analyzer, and DTS
Windows apps created with VB, C, C, Java
Application software such as Excel or Access
Crystal Reports, Business Objects, etc.
SQL Server Databases (1) data file(2)
transaction log file
Transact-SQL or T-SQL is SQL Servers SQL
dialect
6
Why consider upsizing your Access database to SQL
Server?

• of concurrent users
• Access OK for 1-20 (claims of up to 100 but
  depends on details of the database)
• SQL Server can support hundreds or even thousands
  of users
• Database size
• Access has 2Gb mdb size limit (though you can
  split database across multiple mdb files
• Transaction logging and data integrity
• SQL Server has much more sophisticated tools and
  architecture for maintaining data integrity and
  having ability to roll back transactions in
  response to unforeseen system events
• large mission critical databases should not be
  running in MS Access
• Security
• SQL Server has sophisticated security model and
  management tools that can use Windows NT
  Authentication or separate SQL Server logins
• Administrative power
• SQL Server has numerous tools to help administer
  databases

MS Access has Database Upsizing Wizard that makes
it relatively easy to port an Access database to
SQL Server
7
Main Components of SQL Server
(1) On the Server Side

• SQL Server database engine
• runs as a service on computer acting as the
  database server
• server listens for requests from clients
• Analysis Services
• OLAP and data mining

Icon in tray indicating SQL Server is running
(2) Important Client Side Tools

• Enterprise Manager primary tool for managing
  SQL Server databases, security and other objects
• Query Analyzer a tool for building and
  analyzing SQL statements
• Data Transformation Services (DTS) a tool for
  manipulating data coming into or going out of a
  SQL Server database

8
Linking an Access database to a SQL Server
database table

• For an existing SQL Server database, Access can
  link to it via ODBC
• This allows you a simple method for developing a
  front end in Access which uses data stored in a
  SQL Server database.
• The SQL Server Tutorial walks us through doing
  this. Well use the sample Northwind database
  that ships with SQL Server (and Access).
• Lets do this now.

9
Upsizing an Access Database

• Access has built in Database Upsizing Wizard
  which provides simple way to create a SQL Server
  database from an Access database
• Wizard can move tables into SQL Server and create
  the links (like we did in first part of
  Tutorial).
• You continue to use the Forms, Reports, Queries,
  Macros and Modules that you may have already
  developed in your Access database. Theyll work
  in general.
• While simple, and a good way to get started with
  SQL Server, we do sacrifice some of the power of
  SQL Server.
• Next part of the Tutorial walks us through using
  the Upsizing Wizard.
• Note There is some sort of bug that manifests
  itself when we use the Upsizing Wizard in the lab
  with the ORADEV SQL Server
• tables get moved to SQL Server but links dont
  get created
• well create them manually
• when I use the Upsizing Wizard on my machine, it
  works just fine
• Lets continue in the Tutorial and use the
  Upsizing Wizard

10
Enterprise Manager The Big Picture
Databases in this instance of SQL Server
Importing/Exporting data
General tools for administering SQL Server
Managing logins and security
11
Anatomy of a SQL Server Database - Northwind
As in Access, the Northwind database is included
as a sample for you to explore and learn from.
Like Access Relationships
Notice that there are User tables and System
tables. The system tables are like the hidden
system tables in Access. Leave them alone.
Views are stored SELECT queries
Stored Procedures are T-SQL code
Controls who can do what with the database.
12
A Few SQL Server Datatypes
13
Design Table Window

• What happens to Access Autonumber fields when
  they get upsized to SQL Server?
• Whats the difference between the nvarchar and
  varchar data types? What was the data type in
  Access for such fields?
• Whats the difference between the datetime and
  smalldatetime datatypes? Change the data type for
  StatusDate to smalldatetime.

14

• Works just like it does in Access
• Which tables depend on this table and which
  tables does this table depend on?
• This capability runs throughout SQL Server. You
  can script an object which results in SQL
  Server generating the T-SQL code to create and/or
  modify/delete that object. It is extremely
  useful.
• Primary and foriegn key relationships can be set
  here. Diagram objects can also be created which
  look like the Access Relationships window.

15
Quick Review of ADO

• You saw this last year when we used ADO to get
  data from an Access table and stick it into an
  Excel worksheet
• Now well do this again, but from SQL Server
• GetExternalData-SQLServer.xls (Module1 Sub
  GetNodeData_SQLServer)
• ADO ActiveX Data Objects
• Object library for working with databases
• provides objects that let you do things like
  connecting to an external database, get data from
  that source, put data into that source
• Basic steps
• Create Connection to database
• name location of database file
• information on the data provider (data source
  type)
• Open a Recordset object based on table/query
• Navigate Recordset object to get at data you need

16
ADO vs. DAO

• ActiveX Data Objects
• Newer MS technology
• Simple, flexible object library for working with
  all kinds of remote databases
• Internet, Oracle, Access, SQL Server, ...
• MS wants people using ADO

• Data Access Objects
• Older MS technology
• Primarily designed for Jet databases (Access)
• 3.6 library is last one (Access 2000)
• DAO being phased out

17
Make sure Reference is set to ADO Library
Microsoft ActiveX Data Objects 2.1 Library
Theres are other object libraries related to
data access including the DAO library
18
Other Useful Components

• View designer from Enterprise Manager
• QBE like interface
• Query Analyzer
• an environment for building T-SQL
• Data Transformation Services
• very powerful tool for importing and exporting
  data into and out SQL Server from a multitude of
  different data sources
• create and save DTS packages for reuse

19
Resources

• SQL Server Books Online (Help system)
• Chipman, M. and Baron, A. (2001) Microsoft Access
  Developers Guide to SQL Server, SAMS,
  Indianapolis, Indiana.
• Rankins, R., Bertucci, P., and Jensen, P. (2003)
  Microsoft SQL Server 2000 Unleashed, 2nd ed. ,
  SAMS, Indianapolis, Indiana.
• SQL Server for Dummies
• http//www.microsoft.com/sql/ (official MS SQL
  Server site)
• Microsofts SQL Server Development Center
• SQL Server magazine http//www.sqlmag.com/
• SQL Junkies weblog http//www.sqljunkies.com/WebLo
  g/