MS Access vs SQL

Options

Microsoft Access (Jet Engine) Microsoft Access (MSDE) MS SQL MySQL Other SQL applications

Access – File Server Architecture

Server is a storage device only Access database tables stored on server

Queries, forms and reports can be on the client or server Better performance if stored on the client

Client request leads to all related tables to be transmitted to client Performance typically acceptable in LAN Performance suffers on WAN (slow) links

Larger tables can take many minutes (or longer) All processing then done on the client

Client /Workstation Server

SQL – Client/Server Architecture

Server is a processing partner and storage device SQL database tables stored on server Client request leads to processing on server(s) Result is sent to the client for formatting & display

Performance much better particularly on WAN (slow) links Much smaller amount of data transmitted

Both sides can perform relevant data processing

Client /Workstation Server

Client Server Database Systems

5

MS Access vs. SQL Server

MS Access Great for small projects with just a few users Great tool to develop custom business applications, since it's

free to distribute and relatively easy to use Problems arise when you need to scale the application for

More features More data More users

Microsoft's SQL Server 2005 Designed to handle

Data administration Rigorous workload balancing of enterprise applications

Can connect directly to existing MS Access application

MS Access Guidelines

"ok" "Caution" "Stop"

LAN Users <5 5-10 > 10

Internet Users 0 1 > 1

User Permissions Read-only Read / Write Read / Write

Size of DB < 100 MB 100-300MB >300MBs

Data Structures Simple Moderate Complex

DB Schema Design Well Designed Poor Design Ad Hoc

MS Access (Jet) Scalability

Access Jet Engine – 5-10 simultaneous users in the LAN If database is on the Internet requires “real” SQL Server License

Strictly a licensing (revenue) issue Exceptions:

MS Access front end – limits the number of End Users you can support .ASP Active Server Pages – read-only access to your data

Users and Internet Support

Access Jet vs. MSDE

Access Jet Engine database back-end Default Access database engine Simple database schema Don't expect database to grow larger than 1.5 GBs

MSDE Microsoft SQL Server Desktop Engine Works with Access application front-end (Client) Identical to MS SQL Server

MS SQL Server – license is required MSDE – free Certain performance limitations and licensing restrictions

Not be supported on the Vista OS Upgrade to SQL Server 2005 Express

Maximum database size of 4 GB

MSDE Scalability

Extends LAN capabilities Not licensed for the Internet

MS SQL Server Scalability

Mission Critical Enterprise Applications Supporting dynamic Internet applications

Static vs. Dynamic Content (Read vs. Read-Write access) Frequent user access Large number of end users (enterprise-wide deployment) Size of the database greater than 100GB Auditability, Industry Compliance (e.g. SarbOx, HIPPA)

Front-end Comparisons

Split a Microsoft Access Database

Into two files One with the tables One with the queries, forms, reports, macros and modules

Microsoft Jet database engine manages the data Users can access the data with customized forms,

reports, pages, and other objects while maintaining a single source of data on the network Users have only the resources they need

Course Text Microsoft Office Access 2003: Comprehensive Concepts and

Techniques Project 9 – Page AC 569

Split a Access Database (1 of 2)

Use the Tools | Database Utilities | Database Splitter. Follow the instructions in the Database Splitter Wizard

Split a Access Database (2 of 2)

Choose name for backend (tables) file End up with Access DB with only the local tables

Move backend to Server Could have been done within the Wizard

Linked Table Manager (1 of 2)

Use the Tools | Database Utilities | Linked Table Manager

Linked Table Manager (2 of 2)

Use the Select New Location box to find the Server (tables)

Fin…