Transcript Moving from Access Databases to a Visual Studio/SQL Server

Moving from Access
Databases to a Visual
Studio/SQL Server Solutions
Andrew Couch
UK Access User Group
asc associates
www.upsizing.co.uk
Phases

Migrating the database design and data to SQL Server, relinking Access application to SQL Server

Migrating queries to views and stored procedures, also repackaging the SQL behind Forms and Reports to SQL Server
if targeting to Visual Studio. Performance tuning or to make
the next step.

Migrating the application to use web forms and report services
with Visual Studio
Migrating the Database Design
and Data
Preparing the Database Design

Access Design
•
•
Tables & Fields
•
•
•
•
•
Add missing defaults for Boolean fields
Avoid zero-length string anomaly
Add timestamps (recommended)
Add auditing fields (nice opportunity to add at this point)
Decide on specific data type mappings; memos to
VARCHAR(MAX) or TEXT, OLE Objects to
VARBINARY(MAX) or IMAGE and setting decimals precision
Relationships
•
Correct for cycles removing cascade operations from
relationships (www.upsizing.co.uk/Art6_Relationships.aspx)
Preparing the Database Design
(2)
•
•
•
Table validation rules (checks)
•
Translate Access expressions and functions
Field validation rules (checks)
•
Translate Access expressions and functions
Indexes
•
•
•
•
Reject inefficiencies, 20% rule
Check indexing on foreign key fields
Check for primary keys, add if absent
Map ‘unique indexes that ignore nulls’ to unique
index and trigger
Schema’s and Synonyms
Architecture : Schemas/Security


Schemas
•
•
•
•
Group together design objects
Partioning designs
Combining Access databases
Synonyms required for ease of reference
Security
•
•
•
•
Database roles
Grant permission on schemas
Assign users to roles
User  database roles  schemas
Preparing Data

Issues
•
•
•
•
Missing data in a required field, fix data or make field allow
nulls, no workaround beyond this
Violation of other checks (validation rules), avoid by adding
checks after data migrated, or fix the data, adding checks
after migration gets around some of these issues
Date range problem and invalid dates, fix the data, no work
around
Violation of R.I and deep seated corruption, difficult to
generalise, migrate data, remove bad data then add
relationships
Migration Sequence


Initial Migration
•
•
•
•
Migrate optimised and corrected structure
Check and fix data
Migrate data
Add checks, triggers, indexing and relationships
Commissioning
•
•
•
•
•
Check and fix data
Drop checks, triggers, indexing and relationships
Empty data
Migrate data
Add checks, triggers, indexing and relationships
Skills : SQL Server a new world ?


A more important system ?
•
•
•
Dba’s, support, security and reviews of changes to
design
Production and development environments
More developers
Better control
•
•
•
Version Control
Comparing Development and Production
Script files to transfer change, GUI dangers in
production environments!
Skills : SQL Server a new world ?
(2)


Other Issues
•
•
•
Backups
Server Tuning
Security
Access Application
• Could stop here and retain the Access front-end
•
•
•
Tune SQL converting as required to SP’s, Views and
Pass Through
Or we can go on and do further bulk conversions
Do all your existing queries work? Do you need all your
existing queries ?
Migrating queries and
re-packaging the SQL behind Forms and
Reports
Screen Control Example
SELECT BigCustomers.Country, BigCustomers.*
FROM BigCustomers
WHERE (((BigCustomers.Country)=[Forms]![frmPrompt]![txtCountry]));
Parameter Table & View
CREATE TABLE tblParameters(
SelectedCountry VARCHAR(15),
SelectedDate DATETIME,
SelectedRegion VARCHAR(15),
UserName VARCHAR(128) DEFAULT suser_name()
)
CREATE VIEW vw_tblParameters
AS
SELECT * FROM tblParameters
WHERE UserName = suser_name()
Translated View
CREATE VIEW vw_ResultsSimple
AS
SELECT *
FROM BigCustomers
CROSS JOIN vw_tblParameters
WHERE [Country] = [SelectedCountry]
SELECT BigCustomers.Country, BigCustomers.*
FROM BigCustomers
WHERE (((BigCustomers.Country)=[Forms]![frmPrompt]![txtCountry]));
Another Example; optional start
SELECT IIf(Not IsNull([Forms]![frmPrompt]![txtTheDate]),
IIf([Forms]![frmPrompt]![txtTheDate]<[Date],1,0),1) AS Expr1,
IIf(Not IsNull([Forms]![frmPrompt]![txtCountry]),
IIf([Forms]![frmPrompt]![txtCountry]=[Country],1,0),1) AS Expr2,
BigCustomers.*
FROM BigCustomers
WHERE (((IIf(Not IsNull([Forms]![frmPrompt]![txtTheDate]),
IIf([Forms]![frmPrompt]![txtTheDate]<[Date],1,0),1))=1)
AND ((IIf(Not IsNull([Forms]![frmPrompt]![txtCountry]),
IIf([Forms]![frmPrompt]![txtCountry]=[Country],1,0),1))=1));
Making Parameters Optional
CREATE VIEW vw_Results
AS
SELECT *
FROM BigCustomers
CROSS JOIN vw_tblParameters
WHERE COALESCE ([Country], ' ') =
COALESCE ([SelectedCountry], [Country], ' ')
AND COALESCE ([Date], ' 1/1/1900') >=
COALESCE ([SelectedDate], [Date], '1/1/1900')
Translating SQL
“We choose here to consider maximising
translation of objects to SQL Server objects,
rather than embedding SQL in code”






Stored Procedures
Views
Access Support Library
Custom UDF Translation
Parameter Translation
Form/Report Control and Record Source re-packaging
SQL
What Queries Translate To

INSERT, UPDATE, DELETE
•
•

Stored Procedure (need to use pass-through queries to call
these by name)
Pass-Through Query; containing explicit SQL
SELECT, CROSSTAB
•
•
•
View (need to indicate unique fields to make these
updateable, where possible, can be linked just like tables)
Stored Procedure (read-only, can not be linked need to use
pass-through queries to call these)
Pass-Through Query (read-only); containing explicit SQL
References

Nested Queries (Calculations)
•
•

Work out how many layers are going to be required, and
then decide whether it would be better to generate a results
table using selection criteria, upon which to base the
calculations or work with the Nested Query.
www.upsizing.co.uk/Art3_NestedQueries.aspx
Parameters Table (Parameters)
•
•
Isolate all the screen references and parameter statements
and use a table containing parameters to filter the SQL,
Conversion of IIF statements to CASE statements and use
of COALESCE functions for optional parameters.
www.upsizing.co.uk/Art7_Controls.aspx
Migrating the Application
Access Application Objects

Menus, Toolbars and Navigation

Forms

Reports

Macros, Modules and Form/Report Module Code
Architecture : Web Forms a
Strategy

Simple bound controls and templates

Web site menu

Master Pages and style sheets

Further points to consider
•
•
Screen layouts and screen areas
Searching and filtering data
Example Converted Form
Sitemap
Master pages and Content Areas
CSS to Control Presentation
Searching and Filtering
Application Issues
“We are only considering the use of bound
controls with custom templates here”


Inconsistent data in fields
•
•
invalid drop-down box selections
invalid radio button selections
Error handling
•
error handling is a necessity
Application Issues (2)

Grid control when inserting data

Searching and filtering for data

Locking problems

•
No support for timestamps on bound controls
Security strategies
•
•
•
Internet
Integrated security
ASP. net security
Report Services

Easy security with groups

Forms to prompt

Launching using ReportViewer controls

Import Access reports into a Visual Studio Report Server
project
Report Server
Skills : Lost Count Now?


New technology areas
•
•
•
•
SQL Server
Visual Studio web forms and .net
Report Server
IIS
Challenges for newcomers
•
•
•
Security
Security
And security
MUST

Download a trial version from www.upsizing.co.uk

Site contains a number of technical articles, expanding
on these topics

[email protected]