Migrating Access Applications to .NET and SQL Server - PowerPoint PPT Presentation

1 / 22
About This Presentation
Title:

Migrating Access Applications to .NET and SQL Server

Description:

Focus on features not mundane tasks. Better use of developer effort. Adding new Functionality ... Reduction of engineering staff for mundane tasks ... – PowerPoint PPT presentation

Number of Views:155
Avg rating:3.0/5.0
Slides: 23
Provided by: gregoryjs
Category:

less

Transcript and Presenter's Notes

Title: Migrating Access Applications to .NET and SQL Server


1
  • Migrating Access Applications to .NET and SQL
    Server

Ken Young kyoung_at_nusoftsolutions.com Tim
Landgrave TimL_at_2ndGS.com
2
Agenda
  • Why Migrate from Access?
  • Current Access Migration Methods
  • Why is Migration Tedious, Difficult and
    Expensive?
  • What are the Business Benefits of Migrating?
  • Tool - Application Refactoring and Migration
    Suite (ARMS)
  • How Can ARMS Help and Why?

3
Driving issues for Access Migration
  • Not a Client/Server architecture
  • Value is Performance, flexibility, management
  • Basic database administration
  • Data re-centralization
  • Workgroup applications grow up
  • Number of user increase
  • Data capacity requirements outgrow Access MDB
    size and performance guidelines
  • Functional complexity outgrows MS Access and VBA
    capability
  • Security requirements tighten
  • End user freedom, agility
  • Data integration flexibility
  • Self-service (i.e. reporting, analysis)

4
Benefits to Customers
  • Platform Support
  • Windows Platform and .NET Framework
  • SQL Server 2000
  • RDBMS
  • Reporting Services
  • Visual Studio.NET
  • Enables Office 2003 deployment
  • Customers want to deploy Office 2003 and cant
    due to Access 97 dependency
  • Customer Satisfaction
  • Eliminates Access database corruption issues
  • Increases the availability of the data (slow
    connections)
  • Removes Access MDB file deployment
  • Technical Advantages
  • Scalability
  • Data Security
  • Improved performance
  • Reliability
  • Team Development

5
Challenges Until NOW!
  • Developer quality tools
  • Form Report Migration was a manual process and
    therefore COST PROHIBITIVE
  • Reliable consistent methodology
  • Migrate cost, and analysis thereof

6
Access Application types
Component Re-use (forms, reports, modules)
7
What an Access Application is
Biggest Issue Everyone obtains a copy of the MDB
file in their client computers memory
Network
\\fileserver\sharename
MS Access MDB File
Every computer makes a local working copy of the
MDB file
Form
VBA Code
Report
NOT a Server based architecture
LIMIT 2 gigabytes minus the space needed for
system objects.
  • Key architecture concerns
  • Fat client
  • No Server side processing

8
After Migrating to .NET and SQL Server
  • Key benefits
  • Thin client/Browser Option
  • Rich Client
  • Strong Security (SSL)

Network
Every computer only uses data they need!
Pure Client/Server based architecture
Microsoft SQL Server
  • Key benefits
  • Reliability
  • Performance
  • Scalability
  • Security
  • Flexibility
  • Management Control
  • Reduced cost of ownership

Database
9
Refactoring _at_ Work
Access Application
Automate
SQL Server
10
What this means
  • Deployment
  • No more downloading .mdb to client
  • Better memory utilization on client
  • No longer loading Access engine on workstation
  • Better utilization of SQL Server

11
Migration Process
  • Data
  • QueryDefs
  • Forms
  • Code
  • Reports

12
Processing Data
  • Migrate data using the Upsizing Wizard
  • Does a decent job
  • Reports on the process
  • Can result in .mdb linked to SQL

13
Processing QueryDefs
  • Upsizing wizard does not do much
  • SQL Syntax must match SQL Server
  • Lots of tweaking
  • Testing of new syntax
  • Porting to stored procedure / view is optional

14
Processing Forms
  • Forms require recoding
  • Very tedious process
  • Controls/properties/attributes
  • Rewiring event handlers

15
Processing Code
  • Convert code to VB.NET
  • Language constructs (VBA to VB.NET)
  • Access specific code
  • Items such as message box, forms, etc
  • Data features such as dlookup
  • Data objects
  • DAO / RDO to ADO.NET

16
Processing Reports
  • SQL Reporting Services
  • Import Access reports to SQL Reports
  • Moves reports to web
  • Allows for feature rich easy to use reports
  • IT and user friendly

17
Migration Tools
  • 2nd Genesis Software Application Refactoring and
    Migration Suite (ARMS.NET)
  • Form Converter
  • Object Builder
  • Access2NET Resource Kit
  • QueryDef Converter
  • VBA to .NET
  • Win Forms Databinder
  • SQL Server Reporting Services Import Tool
    (Microsoft)
  • Coming Soon
  • Access to ASP.NET

18
Automated Migration Using 2nd Genesis ARMS.NET
Tools
Tools Save
2nd Genesis ARMS.NET Forms, QueryDefs,
Code Microsoft Access Upsizing Wizard
Mission Critical Apps
Reporting Apps
Reporting Services Import Tool
2nd Genesis ARMS.NET Forms, QueryDefs,
Code Microsoft Access Upsizing Wizard
Departmental Workgroup Applications
Component Re-use (forms, reports, modules)
2nd Genesis ARMS.NET
Non-Mission Critical Apps
19
Migration Process Using ARMS.NET
  • Analyze the initial .mdb using
  • Create plan
  • Convert the Access Application
  • Data
  • Upsize the Access MDB to SQL Server
  • Convert Access QueryDefs to SQL Server Stored
    Procedures
  • UI
  • Convert Access forms to .NET WinForms
  • Business
  • Migrate VBA Code to .NET Classes that manage the
    data
  • Tie Forms to .NET Classes
  • Reports
  • Convert Access Reports to SQL Reporting Services
    reports
  • Modify QueryDefs to use SQL Server data directly
  • Final System Testing

20
Conversion Example
  • National Services company has built its business
    on one Access file
  • Initial .mdb was 64MB (one project)
  • No data, only forms and code
  • Analysis when converted shows

21
Developer Productivity
  • Typical approach to manual forms conversion with
    error rate, analysis, QA, and repair applied to
    example customer application
  • For Example, a single form with 10 controls with
    150 properties will require
  • 2 hours to analyze
  • 16 hours to convert code
  • 6.64 hours to debug and repair
  • At 50/hour (burdened rate), this single form
    costs 1,232 to convert
  • Sample application is 16,000 hours in forms
  • Which at 50/hour is approximately 800,000 JUST
    for Form conversion
  • Conversion without tools is cost prohibitive!

22
Why our approach is better
  • Fewer errors in development
  • Client does not pay to find and fix errors not
    introduced
  • Cost is drastically lower than manual conversion
  • More reliable code
  • Focus on features not mundane tasks
  • Better use of developer effort
  • Adding new Functionality
  • Re-factoring - not rewriting remodel to achieve
    goals above
  • Bottom line
  • Reduction of engineering staff for mundane tasks
  • QA improves dramatically (developer errors go
    down dramatically)
  • Client should not spend money to have bodies
    convert Access attributes to .NET target
    attributes
Write a Comment
User Comments (0)
About PowerShow.com