Migrate Access To SQL Server WebApp

Microsoft Access is a highly popular and powerful database management system (DBMS), but there is no web-accessible version of Microsoft Access. Using dbFront, you can unlock the data in your Microsoft Access application and make it available as a Web Application

The first step would be to migrate the data to SQL Server (or database of your choice). Once your data is in SQL Server, then:

You can install dbFront, which will immediately give you a powerful web interface for your migrated data. dbFront runs in parallel with your Microsoft Access application, so you can easily compare the functionality and migrate on your schedule.
Additionally, your Access application should see a significant increase in reliability and performance. This allows you to continue to use your Access application where it works best, and use the dbFront-provided web application where it is strongest.

Small and simple Microsoft Access applications can only take hours to migrate, but complex applications may require significantly more work. Some considerations are:

The ultimate goal. (Augment or Replace)
Table, Form, & Report counts and complexity.
Dataset size.
Is the data well structured (3NF)?
The amount and purpose of any VBA or Macros.

You can contact us at [email protected] for a quote.

If you prefer, you can work through the steps below and even use the FREE version of dbFront to complete the Web Application.

1. SQL Server Migration Assistant (SSMA) for Access

The first step is to install and use the SQL Server Migration Assistant to migrate your Access data to SQL Server.

SQL Server Migration Assistant: Documentation and Download

The next step is to create a full backup of your Microsoft Access Application databases. The Migration Assistant does have its own backup operation, but I prefer to retain a FULL backup in a location of my choice before the irreversible happens. The Migration Assistant will update your local Access database as it migrates the tables, including adding/changing keys, renaming tables, creating links, and such...

One of the biggest gotchas is that Microsoft Access is far more lenient than SQL Server when it comes to indexes, joins and relationships. This can result in failed conversions or extra work to piece things together. In many cases, relationships will migrate "WITH NOCHECK" meaning that they require data cleanup and validation. For Microsoft's list of potential issues, see: Incompatible Access Features

Ensure that you select to have SSMA replace the local migrated tables with links to the server tables. This causes the original tables in Access to be renamed and table links (a Globe symbol) to be added to replace them.

2. Post Migration Checks

Testing

Post v8.19, the Migration Assistant includes the ability to test that the converted database objects and data arrived in a usable state.

Assessment Report

It is important to review the SSMA Assessment Report and deal with the noted issues.

Review the converted Datatypes

Additionally, it is a good idea to review any data type differences to ensure that there is no unexpected data loss or conversion issues.

Validate Migrated Relationship and Data

Finally, it is important to review all relationships to verify that they are enabled and valid.

You can use the following query to find Invalid or Untrusted relationships.

SELECT * FROM sys.foreign_keys where is_disabled = 1 or is_not_trusted = 1;

You will need to start by enabling any disabled constraints so they can be used and so that the data can be properly fixed. To enable a relationship while temporarily allowing invalid data, use the following ALTER statement.

ALTER TABLE [Family] WITH NOCHECK CHECK CONSTRAINT FK_Family_Individuals;

Notice the WITH NOCHECK CHECK, this specifies that the constraint should check all future changes but NOT check existing data unless it is changed.

WARNING: NOCHECK constraints may affect performance because the query optimizer can't trust the data.

3. Install dbFront to enable the Web Application

Once the data migration is complete and the original Access Application is functional, then it is time to configure the dbFront-provided Web Application.

dbFront is highly configurable and provides several layout options, but a basic requirement is that the database is properly structured on SQL Server. This is essential and allows dbFront to bring all of the correct pieces together.

To see what is possible, please look at our video tutorials or examine the product details.

Adventure Works - Product Table

The following interface was created from the product table in Microsoft's sample database

Expense Reporter - Home

A free and open source dbFront application that allows your employees to submit expense reports which would pass through management and finance for approval and processing. Implements: Workflow, Auditing and a TodoList

Depreciation Calculator - Home

Depreciation Calculator is a simple open source dbFront application that allows you to maintain a simple list of Assets and calculate the yearly depreciation for each item.

Responsive Design

Support for everything from a small smartphone to a 4K monitor and beyond

Simple or Distributed Setup (DMZ)

A simple setup allows for everything to be installed on a single server. The most secure setup is to spread the Database, Application Server and Web Application over three servers. dbFront allows for any combination.

Child Tables (1-M)

Regular (1-M) child tables appear in separate tabs, each with a grid and form

Child Tables (1-1)

A child (1-1) relationship will appear without a grid

Single-Select Foreign Keys (1-M)

Simple Lookup to Store Table

Multi-Select Foreign Key (M-M)

Multiselect Lookup to Credit Card Table (intermediate associate table)

Configure Relationship Details

Arrange, Rename or Hide related tables

Reporting - Quick Reports

Template based reporting allows regular users to simply, quickly and safely create their own reports. Administrators can save these reports to enable single click reports with optional prompts for search fields or output choices.

Reporting - Invoice Template

Invoice Template setup on the Adventure Works Sales Table

Reporting - Sample Item Listing

This is a simple listing of selected records. Users can easily create these listings and either print or export the results.

Reporting - Crystal Reports

dbFront has the ability to directly process and print SAP Crystal Reports. Users can optionally choose to print to a server-attached printer, view it as a PDF or download the output as either CSV, DOC, PDF, RTF, TSV or XLS.

Advanced Search

Below every table is a configurable and collapsible panel with a list of fields that you can search on individually. This allows advanced access to range and other custom searchs.

Row Level Security

Row Level Security is applied using SQL expressions that evaluate to true or false. The expression syntax is similar to a regular where clause. Select, Update, Delete and Insert permissions are supported.

Stored Procedures

Stored Procedures can be triggered by an Action Button and can be used perform any process and then dynamically return results that can be used to drive a report or export. Multiple recordsets are supported.

Table Views

Database views can be added to tables and their fields can be used everywhere that a regular table field can be used. This includes Reports, Exports, Row Security Expressions, Dropdown Columns, everywhere...

Workflow in Database

Workflow is implemented within the Database using a status and workflow table with dbFront providing the UI. A fully functional example is provided in the Expense Reporter Application.

Layered Security for Defence in Depth

Security is a thorny issue when building database-driven web applications. With dbFront, we have and will take this problem seriously which is why we designed dbFront with the view of defence in depth.

Database Compatibility

Oracle 10+, Microsoft SQL Server 2000+ and MySQL 4+

•••••••••••••••••••••

Try dbFront for Free

dbFront offers a 30-day free trial so that you can download, install and test it in your environment. After the trial you can continue to use the FREE version of dbFront for up to 3 users. Please review the video tutorials and plan to start a free trial today. We believe you will be impressed.

Start Free Trial View Live Demo