microsoft access as an odbc client application a presentation for the microsoft access sig of the...
TRANSCRIPT
Microsoft Access as an ODBC Client Application
A Presentation for the
Microsoft Access SIG
of the North Texas PC User Group
and the Metroplex Access Developers
by Larry Linson.
© Copyright 2000 by L.M. Linson, all rights reserved
What is Access?
“A nice little desktop database” First popular Windows database For the novice For the power user For the developer A File Server database with Jet
Access can build
A standalone database A multi-user database A client linked to ODBC databases A direct client to SQL Server A web interface to database
Specifically
Microsoft Access 2, 97, 2000 .MDB or .MDE file format Jet database engine and Data Access
Objects (DAO) Tables LINKed via ODBC Driver to Server database (e.g. SQL Server)
Specifically NOT
Access Data Project (ADP) Data Access Pages (DAP) ActiveX Data Objects (ADO) Other Web Interface (e.g., Front
Page) ODBCDirect
How Many Users?
Multi-User– With everything wrong – 4, 5, or fewer
– With everything right – 90 to 150 ODBC Client
– Easily hundreds, limited only by
– Concurrent connections to server
– Essentially unlimited
Multi-User Layout
Tables on Server– Available To All Users
On Users’ Workstations– Access (or Runtime)
– Application Part
– Fetch .DLLs, Objects Locally
Client-Server Layout
Server Database Engine on Server– Extraction, manipulation done here
On Users’ Workstations– Access (or Runtime) and Jet database engine– Application
» Queries, Forms, Reports, Macros, Modules» Linked TableDefs and Local Tables
– ODBC Drivers, ODBC Data Source Information
Jet is a File-Server
Retrieve, Extract, and Manipulate on User’s Workstation
Every I/O Done Across Network– That would normally be to local hard drive– Not whole database, nor necessarily whole table
– Just what Access finds necessary Index may be enough to find exact records
Server Databases
Retrieval, extraction, and manipulation on the Server
Much less Network I/O– (Just) Requests from user to server
– (Just) Results from server to user
Multi-user Layout . . .
DATAON
SERVER
PROCESSON USER
WORKSTATION
PROCESSON USER
WORKSTATION
. . . PROCESSON USER
WORKSTATION
Client-Server Layout . . .
DATASTORED
INSERVER DB
CREATE REQUESTSEND / RECEIVE
DATA
PROCESSREQEST
IN SERVER DB
Request &Data
U I
Comparison . . .
Server(Not Necessarily Windows)Windows
U I
U IU I
Data Only
Server Database with DataAccess, Jet, ODBC
Access, Jet
What’s Different? . . .
Data in Server DB– Access User Interface to Server Databases– Performance – potentially better; possibly worse– Corruption – not really a factor
Multiple users of same data– Collisions (Add / Update) – Locking handled on server– Seeing Other Users Update – Refreshing– Updating Related Tables – Transactions handled jointly– Who Am I? – Identify Users to Access and to Server
What’s Different? . . .
Retrieval, extraction, and manipulation on the Server
Fewer Problems – Network failures
– Power failures
– Individual User and Machine failures
Updating
What’s Different? . . .
Access security
Server security – Tables on Server
» Accessing, manipulating, updating data
Security
Queries MacrosForms ModulesReports Local Tables
What’s Different? . . .
Rules and triggers at the Server Don’t generate Access error
messages Your application has to have
– pre-validation and
– user notification
Data Validation
What’s Different? . . .
As defined by Server database Server types may
– match directly to Access types
– not have Access counterpart » Text date fields
– not have all Access types» Counter, OLE, Memo
Data Types
What’s Different? . . .
Defined by Server database,– Implemented on Server
Often enforced only by – Triggers
» Written manually
» Generated by Data Modeler
– Some (e.g., Microsoft SQL Server)» Automated, specified similar to Access
Referential Integrity
What’s Different? . . .
Most servers do not permit– updating tables joined in SQL
ODBC workaround (sometimes) – Generate / send multiple updates
Pass-through Queries– Must adhere to server specifications
Updateable Queries/Views
Performance Factors
Hardware Environment» More Memory» Faster Processors» Faster Hard Drives
Software Environment» Not Too Much Else Running
Network Environment» Faster is Better
Performance
Server is moreimportant thanuser’s machine!
Not as important
as in Multi-User
}
More Factors . . .
Reduce Network Traffic – Application Design
Local tables (refresh from server)
Use Queries not Tables– Limit both Tables and Columns
– Database Implementation Queries, not DAO Code (When Feasible) Queries, not RecordsetClone.FindFirst
Performance
More Factors . . .
Built-in performance factors – Re-planning query execution
Also called “compiling” “preparing” State of data is important
Stored procedures Like saved Queries in Access But with logic like code, too
Performance
More Factors . . .
Tuning at the Server – Spread data over different disks– Monitor Performance
» Analyze» Add, remove, modify indexes
– Database Administration» More TLC» That’s why there are DBA’s
Performance
But . . .
Jet can be smart – Return only part of large recordset– Create separate query for FindFirst
It may “save us from ourselves”
Performance
Views Restrict user access
– Only the specified columns– Only the specified rows
Like Access’ “Saved Queries” Appear as Tables to Jet / Access Also assist performance
– Force joins and selection at server
Data Integrity
Record Locking . . . Done “co-operatively” in C/S
– Jet passes the request– Locking actually done by the server– Considerations same as Multi-User
» Except: you may lock out more users» May be a smaller time window for some
So, let’s just “fly through” – the repeated slides
Data Integrity
Record Locking . . . In Bound Forms
– No Locks (aka Optimistic Locking)» Only in the instant the record is saved
– Edited Record (aka Pessimistic Locking) » As soon as user begins to edit
– All Records» All records in the entire recordset» Batch Updates» Administrative Maintenance
Data Integrity
Record Locking . . . Possibilities by Object
– All Three Options» Table datasheets; Select, Crosstab, Union Query, Forms, OpenRecordset
– Lock Edited Record or All Records » Update, Delete, Make-Table, Append
– Lock All Records » Data Definition Queries
– No Locks or Lock All Records» Reports
Data Integrity
Record Locking . . .
Default Record Locking Option– Established by menu Tools | Options
Applies to All, Except– Data Definition Queries – All Records– Open Recordset – Edited Record
Data Integrity
Record Locking . . . OpenRecordset
– dbDenyRead or dbDenyWrite– Overrides .LockEdits property– Native Access Tables Only
.LockEdits Property– True = Edited Record (the default)– False = No Locks
Data Integrity
Record Locking . . . Advantages of Pessimistic Locking
– Simple to Develop, Prevents Overwriting
Disadvantages of Pessimistic– Lock Multiple Records, Less Concurrency
Advantages of Optimistic Locking– Simple to Use, Better Concurrency, Less Lockout
Disadvantages of Optimistic– Can be Confusing to User, Allows Overwriting
Data Integrity
Record Locking
Generally, use Optimistic– Minimize User Lockout
Or, Mixed, by Specific Object– Most Optimistic, but– Critical Information, Pessimistic
» Example: Quantity on Hand in Inventory
Data Integrity
Transactions . . .
Changes in a batch All Succeed or All Fail Same or Related Tables
– Allocate Stock to Order, Deduct Stock on Hand – Credit New Account and Debit Old Account
Data Integrity
Transactions . . .
Server can resolve problems– From failure occurring any time– Full audit trail– Hot backup – second copy of data
No– Orphan locks in .LDB– Data loss, lockout, corruption due to
» Errors that occur while committing
Data Integrity
Transactions
Consider client-server if– Processing funds transactions– Performing critical inventory updates– Etc.– Regardless of other considerations
» Performance
» Number of users
Data Integrity
Identifying Users Typically secure the database
– Everyone Logs In
Viewing and Reporting– MSLDBUSR.DLL by Microsoft– Unsupported
Server Security– Log in to connect and link
Miscellaneous
Some Cautions . . . Jet may lose track of record
– When using server equivalent of AutoNumber– Data entry works fine– When record saved, all show “#Deleted”
Countermeasure– Table of tables and next id number– Stored procedure to return next id– Never use “data entry”, always editing
Miscellaneous
Cautions . . . “Complex” queries
– More than two joins – Jet sometimes decides– Brings back a flood of data
Countermeasure– More than two joins, performance slow– Consider creating a view– View appears as Table to Jet– Forces joins and extraction on server
Miscellaneous
Cautions . . . “Complex” queries (alternative)
– More than two joins – Jet sometimes decides– Brings back a flood of data
Countermeasure– Create a passthrough Query– Tells Jet, “Hands off!”– Forces joins and extraction on server
Miscellaneous
Cautions . . . “Order by” not a key, not indexed
– “Order By field must be in Select Clause”– Message is erroneous
Countermeasure– May have to make it a key or index– May be able to use a passthrough Query
Miscellaneous
Cautions . . . Some joins won’t work in Access
Query
Miscellaneous
tabid
tabname
colid
tabid
colname
TableName
ColumnName
.
.
.
•continuedServer Tables
Access Table
Cautions . . . Countermeasure – “intermediary”
Miscellaneous
tabid
tabname
colid
tabid
colname
tabname
colname
Server Tables Access Tables
tabname
colname
TableName
ColumnName
Query
Resources . . .
Access 97 Developer’s Handbook by Getz, Litwin, Gilbert published by SYBEX ISBN: 0 - 7821 - 1941 - 7 Chap 15 – Developing Client-Server
Applications
Resources . . .
Access 2000 Developer’s Handbook, Volume 2: Enterprise Edition
by Getz, Litwin, Gilbert published by SYBEX ISBN: 0 - 7821 - 2372 - 4 Chap 3 – Developing Client-Server
Applications (also other chapters this volume)
Resources . . .
Building Applications with Microsoft Access 97
Manual, comes with Office Developer or the MSDN Library
publisher: Microsoft Chapter 10 – Creating Multi-User
Databases
Resources
Microsoft Office Visual Basic Programmer’s Guide
Manual, comes with Office 2000 Developer or the MSDN Library
publisher: Microsoft Chapter 16 –Multi-User Database
Solutions