Moving from Access Databases to a Visual StudioSQL Server Solutions

1
Moving from Access Databases to a Visual
Studio/SQL Server Solutions

• Andrew Couch
• UK Access User Group
• asc associates
• www.upsizing.co.uk

2
Phases

• Migrating the database design and data to SQL
  Server, re-linking Access application to SQL
  Server
• Migrating queries to views and stored procedures,
  also re-packaging 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

3
Migrating the Database Design and Data
4
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_Relati
  onships.aspx)

5
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

6
Schemas and Synonyms
7
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

8
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

9
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

10
Skills SQL Server a new world ?

• A more important system ?
• Dbas, 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!

11
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 SPs, 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 ?

12
Migrating queries and re-packaging the SQL
behind Forms and Reports
13
Screen Control Example
SELECT BigCustomers.Country, BigCustomers.FROM
BigCustomersWHERE (((BigCustomers.Country)Forms
!frmPrompt!txtCountry))
14
Parameter Table View
CREATE TABLE tblParameters(SelectedCountry
VARCHAR(15),SelectedDate DATETIME,SelectedRegion
VARCHAR(15),UserName VARCHAR(128) DEFAULT
suser_name())
CREATE VIEW vw_tblParametersASSELECT FROM
tblParametersWHERE UserName suser_name()
15
Translated View
CREATE VIEW vw_ResultsSimpleASSELECT FROM
BigCustomersCROSS JOIN vw_tblParametersWHERE
Country SelectedCountry
SELECT BigCustomers.Country, BigCustomers.FROM
BigCustomersWHERE (((BigCustomers.Country)Forms
!frmPrompt!txtCountry))
16
Another Example optional start
SELECT IIf(Not IsNull(Forms!frmPrompt!txtTheD
ate), IIf(Forms!frmPrompt!txtTheDateltDate
,1,0),1) AS Expr1, IIf(Not IsNull(Forms!frmPro
mpt!txtCountry), IIf(Forms!frmPrompt!txtCo
untryCountry,1,0),1) AS Expr2,
BigCustomers.FROM BigCustomersWHERE
(((IIf(Not IsNull(Forms!frmPrompt!txtTheDate
), IIf(Forms!frmPrompt!txtTheDateltDate,1,0
),1))1) AND ((IIf(Not IsNull(Forms!frmPrompt
!txtCountry), IIf(Forms!frmPrompt!txtCountr
yCountry,1,0),1))1))
17
Making Parameters Optional
CREATE VIEW vw_ResultsASSELECT FROM
BigCustomersCROSS JOIN vw_tblParametersWHERE
COALESCE (Country, ' ') COALESCE
(SelectedCountry, Country, ' ') AND COALESCE
(Date, ' 1/1/1900') gt COALESCE
(SelectedDate, Date, '1/1/1900')
18
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

19
(No Transcript)
20
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

21
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

22
Migrating the Application
23
Access Application Objects

• Menus, Toolbars and Navigation
• Forms
• Reports
• Macros, Modules and Form/Report Module Code

24
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

25
Example Converted Form
26
Sitemap
27
Master pages and Content Areas
28
CSS to Control Presentation
29
Searching and Filtering
30
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

31
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

32
Report Services

• Easy security with groups
• Forms to prompt
• Launching using ReportViewer controls
• Import Access reports into a Visual Studio Report
  Server project

33
Report Server
34
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

35
MUST

• Download a trial version from www.upsizing.co.uk
• Site contains a number of technical articles,
  expanding on these topics
• andy_at_ascassociates.biz