Transcript Microsoft Access as an ODBC Client Application

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 . . .
PROCESS
ON USER
WORKSTATION
PROCESS
ON USER
WORKSTATION
...
DATA
ON
SERVER
PROCESS
ON USER
WORKSTATION
Client-Server Layout . . .
CREATE REQUEST
SEND / RECEIVE
DATA
UI
Request &
Data
PROCESS
REQEST
IN SERVER DB
DATA
STORED
IN
SERVER DB
Comparison . . .
UI
Access, Jet, ODBC
Server Database with Data
U
U II
Access, Jet
Windows
Data Only
Server
(Not Necessarily Windows)
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
Updating
What’s Different? . . .
Retrieval, extraction, and
manipulation on the Server
 Fewer Problems

– Network failures
– Power failures
– Individual User and Machine failures
Security
What’s Different? . . .
 Access security
Queries
Macros
Forms
Modules
Reports
Local Tables

Server security
– Tables on Server
» Accessing, manipulating, updating data
Data Validation
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 Types
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
Referential Integrity
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
Updateable Queries/Views
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
Performance
Performance Factors

Hardware Environment
Server is more
important than
user’s machine!
» More Memory
» Faster Processors
» Faster Hard Drives

Software Environment
» Not Too Much Else Running

}
Network Environment
»
Faster is Better
Not as
important
as in
Multi-User
Performance
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”
Data Integrity
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
Miscellaneous
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

colid
tabid
tabid
tabname
colname
Server Tables
Access Table
TableName
ColumnName
.
.
.
•continued
Miscellaneous
Cautions . . .

Countermeasure – “intermediary”
colid
tabid
colname
tabid
tabname
Server Tables
Query
tabname
colname
tabname
colname
Access Tables
TableName
ColumnName
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