MS Access vs SQL

1
MS Access vs SQL
2
Options

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

3
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

4
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

5
Client Server Database Systems
6
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

7
MS Access Guidelines
8
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

9
Users and Internet Support
10
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

11
MSDE Scalability

• Extends LAN capabilities
• Not licensed for the Internet

12
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)

13
Front-end Comparisons
14
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

15
Split a Access Database (1 of 2)

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

16
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

17
Linked Table Manager (1 of 2)

• Use the Tools Database Utilities Linked Table
  Manager

18
Linked Table Manager (2 of 2)

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

19
Fin