MS Access to ASP.Net and MS-SQL, Oracle, DB2

Features

This software combines advanced MS-Access to Oracle, SQL-Server, or DB2 database migration facilities with a robust MS-Access Forms to ASP.Net code generator and MS-Access Reports to Crystal.Net and SQL Server Reporting Services (SSRS) converters.

The database migration facility precisely replicates MS-Access data-model objects (tables, relationships, queries, data, etc.) to the target DBMS.

ASP.Net WebForms are generated that preserve the look-and-feel, and operation of the original MS-Access Forms. All Form Record Source, and Control Row Source SQL statements are converted to stored procedures on the target DBMS.

Crystal.Net Report Designs and Viewer WebForms and/or SSRS Report Designer compatible .rdl files are generated that retain the layout and features of the original MS-Access Reports.

Test drive the Northwind Traders sample application that we converted to ASP.Net.

MS-Access Forms to ASP.Net WebForms Details

Form Layout

For each form , CSS (cascading style sheets) are generated based on the properties of the MS-Access Form's controls that specify font and field characteristics including family, size, back- and fore-ground colors, borders, text alignment, and special effects.

All MS Access field types (Text, Combo Box, Tab Control, Option Group, etc.) are defined using ASP and HTML controls. Absolute field positions and sizes are maintained on the ASP WebForm as well as tab order, enabled/visible, and bold/underline/italic properties.

Form Operation

Single Forms

The conversion of MS Access Single-Forms to ASP replicates the fundamental operation of the original Access forms, including implicit database operations. For example, when a user navigates to another record after modifying one or more fields, or after inserting a new record, the ASP Code-Behind functions determine if any changes were made and perform the appropriate database operations.

An extended Access-like ASP Navigation Bar that includes record traversal (first, previous, next, last)and additional 'editing' buttons for database operations (new, edit, save, undo, delete) is implemented for all Access Forms that specify a Navigation Bar. The state of the editing buttons (enabled/disabled) reflects the associated Form's allowable database maintenance (add, edit, delete) operations.

Like Access, users edit data by tabbing to the desired fields and entering their changes. Pressing the (‘Save Record’) button, or traversing to another record triggers the database update operation.

After pressing the (‘New Record’) button, an empty form is displayed. Default Values from any Access Form controls or their underlying table fields, are automatically supplied. The user can press the ‘save’ button or traverse to another record to update the database.

After a new record is saved, the position of the newly added record is determined so it may be selected and displayed after the PostBack completes.

The value of any automatically generated numbers (Auto-Number – Identity/Sequence) is displayed on any associated form fields. (These insert behaviors apply to all forms).
Deletions are performed by pressing the (‘Delete Record’) button. Users are prompted to confirm or cancel the delete operation. When a record is deleted, the position of the previous record is selected and displayed.

Continuous Forms

Continuous Forms are implemented using ASP DataList controls and are functionally equivalent to their MS-Access counterparts. Javascript is used to set scroll bar positions.

When forms are updatable, but a Navigation Bar is not specified, editing buttons (like those on the extended Navigation Bar) are included in the DataList template so that database maintenance operations can be performed as they are in Access. As with Access, inserting new records is performed by scrolling past the last record to a blank form. Default Values are supplied on inserts and users are prompted before delete operations are performed.

Datasheets

Datasheets are implemented using ASP DataGrid controls and are also functionally equivalent to their MS-Access counterparts. CSS and Javascript are used to implement fixed-headers and to set scroll bar positions. As with Continuous Forms, editing buttons are included when necessary and new records are inserted by scrolling past the last entry to an empty row of fields. Edits are performed either by navigating to, or selecting the desired entry and pressing the ‘edit’ burton on the Navigation Bar or adjacent to the row entry. The ASP Edit Template is then displayed, which allows users to make changes to editable fields. Pressing the ‘save’ button updates the database.

Sub-Forms

Single Forms may include any number of sub-forms, and any form that includes a tab control may contain any number of sub-forms.

All form types (Single, Continuous and Datasheet) are supported as sub-forms.

All Master-Child data relationships are maintained, including sub-forms that are linked to other sub-forms (ex. Customer Orders Form in Northwind).

Before inserting new records on a sub-form, the parent form is checked to ensure that any newly entered record is first saved.

The sub-form area on the main form is bounded by a scrolling area, with scroll bars that are displayed only when the form exceeds the vertical or horizontal scroll bar boundaries.

Sub-form data and scrolling positions are reset when the parent form is traversed.

Tab Controls

Tab Controls are intelligently implemented. For example, when tab pages specify sub-forms, data is only retrieved when (and if) the tab page is clicked.

Form Field Expressions

Form expressions are fully implemented, including sub-form references and aggregate functions.

The following example is from the Northwind Orders Form. The "Total:" value is the Sub-Form reference expression: [=Sum(ExtendedCost)+Freight]). It reflects the sum of the ExtendedCost of all items for the parent form's current OrderID. Expressions are re-calculated when new records are added, modified, or deleted.

Auto-Lookups

MS-Access Auto-Lookup fields that retrieve and display related data after clicking a ComboBox entry are supported. (ex. Northwind Orders Form: examine the operation of the CustomerID Combo Box).

Shortcut / Accelerator Keys

MS-Access shortcut keys are implemented using ASP Access Keys.

Database Operations

MS-Access Forms may specify the Record Source as a QueryDef, Table or using in-line SQL.   All Form Record Sources are converted into target DBMS procedures. With Oracle, a single package is created to contain all procedures.

Procedures are generated for all Form Controls (typically Combo and List Boxes) that specify a Row Source with a Row Source Type of Table / Query. Data to fill ASP DropdownLists is retrieved only once per session, regardless of the number of edit/insert operations that are performed.

Procedures are generated for Sub-Forms that are linked to master/parent Form data. They include parameters that are used to maintain Master-Child data relationships.

Procedures are used to implement Auto-Lookup data extractions and to evaluate Sub-Form expressions.

In addition to the conversion of MS-Access Record and Row Sources, stored procedures that implement table inserts, updates and deletions are also generated for forms that update a single table.   Although the Form’s underlying query can join any number of tables, enabled Form Fields with Control Source properties that refer to the underlying query must also refer to one and only one underlying table.    (ex. Northwind Orders Form: the underlying query joins two tables, but the enabled Form Fields refer only to the Orders table.)

Update procedures are implemented using Optimistic Concurrency to prevent incongruous updates.

Code-Behind

The Code-Behind language is VB.Net. Note that VB to C# conversion utilities are offered by VBConversions and several other third-party vendors. The generated VB.Net code is specially designed to minimize manual post-C#-conversion tasks. VBConversions typically generates 98-100% of our VB.Net code to C# correctly.

The VB.Net code is easy to understand and well structured. VB.Net variable names are based on the original MS-Access names (tables, fields, etc.) used by the application.

There are no cryptic generalized rotuines or DLLs, just plain vanilla ASP.Net controls and VB.Net code are employed.

Special care has been taken to minimize Post-Backs and DataBind operations.

The source for all VB code and Javascripts are contained in the Web Forms' aspx and aspx.vb files and are easily modified.

MS-Access Reports to Cystal.Net Details

Report Layout

For each report , CSS (cascading style sheets) are generated based on the properties of the MS-Access Report's controls that specify font and field characteristics including family, size, back- and fore-ground colors, borders, and text alignment.

The generated Crystal Report Design retains all layout characterics and most functionality of the original MS-Access Reports. Note that Crystal Reports supports the vast majority of MSAccess features. Charts are not currently supported but can be added using the Crystal Report Designer. Separate Parameter Entry WebForms are created for Reports whose underlying query requries parameters.

Report Samples

The following Report Samples are the HTML copies of the actual report. Crystal Navigation has been disabled. Crystal Group Trees are not displayed.

MS-Access Reports to SSRS Details

Report Design

The SSRS converter supports most Access Report features including Sub Reports, Charts, and Multi-Column reports. Access Report Record Sources and Chart Row Sources are converted to SQL Server stored procedures.

Report Samples

The following PDF files were created using SSRS Report Designer 2005 from report design layouts (XML) generated by the converter. No changes needed to be made to the Access or SSRS report designs. 

Alphabetical List of Products
Catalog (Sub-Report, Bound Object Images, Page Breaks)
Customer Labels (Multi-Column)
Employee Sales by Country
Invoice
Products by Category 
Sales by Category (Sub-Report and Chart)
Sales by Year
Sales Totals by Amount
Summary of Sales by Quarter
Summary of Sales by Year

What Is Not Automated.

1. VBA code is not converted to VB.Net.

2. Access ComboBoxes that specify multiple columns are not currently fully implemented; they only display data from the first visible column.

3. Input-Masks are not currently implemented.

4. OLE Objects are not implemented.

5. Form Validation Rules are not implemented, but tables that specify Validation Rules and Validation Text are supported.

6. Form filters are not currently implemented.

  [ Home ][ Solutions ][ Tools ][ Demos ][ Sales/Support ][ Company ]