Original PDF Flash format with-Alpha-Five-Version-9  


With Alpha Five Version 9

Moving an MS Access Database Application to the Web
with Alpha Five Version 9
Alpha Software
70 Blanchard Road
Burlington, Mass. 01803-5100
phone: 781-229-4500
fax:
781-272-4846
http://www.alphasoftware.com
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Table of Contents

Introduction .......................................................................................................................................................... 2
Moving an MS Access database application to the Web with Alpha Five Version 9 .......................................... 2
General strategies............................................................................................................................................. 3
What’s different about Alpha?......................................................................................................................... 4
Step 0: Check your database schema in Access .............................................................................................. 6
Step 1: Migrate your Access database to your SQL database ......................................................................... 8
Import and Export using Alpha Five ........................................................................................................... 8
Access-to-SQL Server specific migration .................................................................................................... 22
Step 2: Create an Alpha Five project.............................................................................................................. 28
Step 3: Create a connection to your SQL database ....................................................................................... 28
Step 4: Define a grid....................................................................................................................................... 32
Step 5: Define a Web page ............................................................................................................................ 34
Step 6: Add your grid to your Web page ....................................................................................................... 35
Step 7: Continue defining components and pages ..................................................................................... 37
Step 8: Add code where needed ................................................................................................................... 38
Ajax ................................................................................................................................................................. 38
For more information...................................................................................................................................... 39
PAGE
1
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Introduction
Moving an MS Access database application to the Web
with Alpha Five Version 9
First, let’s briefly cover why you may want to seriously consider moving your MS Application to the Web. It
turns out that the Web provides a number of very compelling reasons for doing this. We will list the key
reasons in bullet form:
•
Once the application has been moved to the Web, any authorized user can log onto the application
from anywhere in the world. Also, different permissions and usage privileges can be assigned based on
the logon.
•
Users just need to use their Web browser (it does not matter if it is IE, Firefox, Google Chrome,
Netscape, or Safari).
•
Users don’t have to install any software on their computer.
•
Users can run the application from any Windows, Linux, Apple Mac computer, or even from an
iPhone or any Smartphone with a browser, such as the new Android class of phones. (The T-Mobile
G1 is an example of this.)
For more on this topic check out http://downloads.techrepublic.com.com/abstract.aspx?docid=353589
In addition, while “first” generation Web applications were often a bit slow, the AJAX technology embedded
into Alpha Five Version 9 allows Web applications built in Alpha Five to be very fast, and offer a smooth
“desktop like” experience to the users.

In this white paper, you will learn how to move a Microsoft Access (http://office.microsoft.com/en-
us/access/default.aspx) desktop application to the Web with Alpha Five (http://www.alphasoftware.com/).
You'll see how easy it can be, and when you're done, you'll understand the process well enough to undertake it
yourself.
There’s good news and bad news about this. First, the bad news: this is not a completely automatic process,
and you will have to give it some thought. While in many cases you should be able to convert your database
schema and migrate your data automatically, you will have to manually re-implement your forms, reports, and
business logic.
The good news is that it isn’t very hard to do any of this in Alpha Five. And the better news is that it should
only take a fraction of the time that it took to implement the application originally. The best news of all is
that your converted application will shine on the Web.
PAGE
2
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



This white paper helps you understand how to go through the steps. If you would prefer us to do it for you,
our Alpha Services (https://www.alphasoftware.com/services2/ ) can do it all for you or provide you any help
you may need. To fill out a services request, please fill out this short form
(http://www.alphasoftware.com/services/proservice.asp).

Think about the assets in an Access application. Besides the database schema and the data, you have forms,
charts, queries, reports, macros, and VBA programs. Alpha Five can provide rough equivalents to all of these
assets, except for charts. Several third-party developers, such as Imagio (http://www.simplechart.net/) and
InfoSoft (http://www.fusioncharts.com/), provide chart controls that are compatible with Alpha Five.
Let’s start with the big picture.
General strategies
There are several considerations to bear in mind when moving an Access desktop database to the Web. First of
all, a Web application can potentially have a very large number of total users, and a moderately large number
of simultaneous users. This means that record locking and overall scalability are very important for Web
applications.
Second, a Web application incurs a delay every time there is a round trip between the browser and the Web
server. This means that performance can be improved by minimizing the amount of page refresh using Ajax
techniques, and that user frustration can be mitigated by displaying progress indicators for operations that can
take more than a second.
Third, a Web application incurs a delay when executing database queries and downloading the result sets.
This means that techniques such as lazy loading, data pre-fetch, data paging, and data caching can make a big
difference in the performance of the application. This also reinforces the need to display progress indicators.
You may have heard of people using Access databases directly from Web applications. We don’t recommend
this, because Access databases don’t support high numbers of simultaneous users or tables with large numbers
of rows very gracefully. But it can be done from Alpha Five Web applications. We don’t recommend using
Alpha’s native DBF format database for Web applications either, primarily for scalability reasons. Instead, we
recommend using a SQL database.
Alpha Five supports most SQL databases through ODBC drivers, including Caché
(http://www.intersystems.com/cache/), DB2 (http://www-01.ibm.com/software/data/db2/), Microsoft SQL
Server (http://www.microsoft.com/sqlserver/2008/en/us/default.aspx), MySQL (http://www.mysql.com/),
Oracle (http://www.oracle.com/index.html), PostgreSQL (http://www.postgresql.org/), Sybase SQL
Anywhere (http://www.sybase.com/products/databasemanagement/sqlanywhere), and Sybase SQL Server
(http://www.sybase.com/products/databasemanagement/). Microsoft SQL Server is the database most
frequently offered by hosting providers on Windows-based Web server accounts, so that’s what we’ll use for
our example.
Other SQL databases can work just as well. If you are on a budget, hosting your own database, and don't need
database support, consider Microsoft SQL Server Express Edition, MySQL, and PostgreSQL, all of which are
free.
PAGE
3
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Our general plan is to migrate the schema and data first. Then, we'll build new Web forms with Alpha, using
the seven Alpha Web components and HTML, based loosely on your existing Access forms.
Since Alpha has Builders to help you to configure components quickly, it won’t take nearly as long to recreate
your forms in Alpha as it did to create them originally in Access. You can use Alpha Five’s own HTML
Designer to add saved components to a Web page easily.
What’s different about Alpha?
There are many technologies used to program Web server pages. You may be familiar with PHP, ASP,
ASP.NET, or JSP. Alpha Five A5W pages are similar to these: they allow you to intermix HTML and
Xbasic code on the server to process queries and forms, operate on a database, and display formatted data.
Alpha Five provides seven high-level components that you can configure and use on an Alpha Five Web page,
along with HMTL controls and Ajax widgets.
PAGE
4
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



The seven types of embeddable Web components are:
Dialog - The Dialog component is a form that allows you to read and write variable data and execute Xbasic
scripts. It is not intended for supporting read and write transactions with tables.
Grid - The Grid component displays multiple fields from multiple records in a table format. The data can come
from one or multiple tables (as long as the tables are linked one-to-one). The Grid component can be either
read only or editable. When the Grid component is read only, there is an option to have an additional Detail
View form that allows you to edit the fields of any selected record.
GridLinker - The GridLinker component contains and displays multiple Grid Components, allowing you to
display data from tables linked one-to-many (or parent-child) relationships.
Login - The login component provides user ID and password security for Web applications.
Maintenance - The Maintenance component will pack and re-index Alpha Five tables and sets. (This is only
useful when you use Alpha Five tables, and is irrelevant if all your tables are in a SQL database.)
Navigation System - The Navigation System component allows you to create horizontal toolbars with drop-
down menus, or vertical menus with fly-out menus. The menus can control navigation to the various pages of
your Web site.
Tabbed GridLinker - The Tabbed GridLinker component organizes its grids in a tabbed structure that displays
only one grid per level, but allows you to switch to any other grid in any level with a click of the mouse.
You’ll find that you use the Grid, GridLinker, and Tabbed GridLinker for the bulk of your database table and
view displays. You’ll find these easier to define and lay out than the equivalent Forms and controls in Access,
and you’ll find that you can accomplish almost everything you need to with them without having to write any
code. The Grid Builder generates the code from your configuration options.
PAGE
5
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Step 0: Check your database schema in Access
Before you go to the work of moving the database from Access, you should review the schema. In Access
2007, you can see a database diagram by selecting Relationships from the Database Tools ribbon:

You can view the details for any table on the diagram by right-clicking on the table and selecting Table
Design.
PAGE
6
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




What you’re trying to determine at this point is whether the database schema will make sense for the Web
application. There may be tables that you don’t want to import at all: it would be helpful to know that before
you go to the effort of importing them. There might be missing relationships, badly normalized tables,
missing primary keys. One Access tool you can use at this point is the Analyze Table wizard on the Database
Tools ribbon, which will detect some common problems.
If there are problems, you have two basic choices about fixing the schema: get it right in Access now, or get it
right in the SQL database later, with the help of Alpha Five and any other tools you have for the SQL
database. You can also combine the two. If you attempt to fix the schema in Access, preserve a copy of your
original Access database somewhere safe. We don’t recommend fixing everything in Access, but you should
probably fix any serious normalization problems if you can, and make sure that the data you migrate has
relational integrity.
PAGE
7
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



It’s very important for SQL databases to be properly normalized, to have unique primary keys for all tables,
foreign key constraints for all relationships, and indexes on all fields used as foreign keys. If none of this makes
sense, refer to the Johansen book listed in the For more information section of this paper for advice on
database design.
If you’re still unsure about the schema, consider bringing Alpha Consulting Services
(http://www.alphasoftware.com/services/) in for a brief database design review. You can produce a great
database application in Alpha Five even if you’re not a great database designer, as long as you get help on the
design. Database design is typically the hardest part of the application development lifecycle, and the most
critical to a successful application. Don’t feel that you have to do that part yourself.
If you determine in Step 0 that you need to start over with a better database design, then Step 1 outlined here
will not apply. Instead, you’ll want to design the new schema, then import data selectively from the Access
database tables to your new SQL database design. Once this is complete, you can proceed with Step 2.
Step 1: Migrate your Access database to your SQL database
Before you start working with Alpha to build a Web application, you need to import your database schema
and your data to the database engine you’re planning to use from the Web. We’re targeting Microsoft SQL
Server 2008 for this example. We’ll start by discussing the generic process, which will also work for other
SQL databases.
Import and Export using Alpha Five
For purposes of this example, we’re using the venerable Access 2003 version of the Northwind sample
database, which ships with Alpha Five. Start Alpha Five, and from the Select Database dialog’s Database &
Tasks tab pick Create a database from imported data. In the drop-down box for Access, navigate to the
northwind.mdb file installed by default in My Documents\Alpha Five V9\MDBFiles\.
PAGE
8
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Click the OK button, and then select a location and name for your new Alpha database. These choices are
relatively unimportant. We’ll throw away the Alpha database once we’ve exported it to SQL Server.
PAGE
9
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Select all the tables to import, and none of the other assets.
PAGE
10
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




When the import operation is complete, you’ll see a status screen:

PAGE
11
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



This database import had no errors. If you encounter an error, go back to the Access database and fix the
problem, delete the partially imported Alpha database, and try the import again. If you don’t understand the
error message, ask about it on the Alpha Forums (http://msgboard.alphasoftware.com/alphaforum/). When
you’re done, you’ll see your imported tables on the Tables/Sets tab of the Alpha Five control panel.

You can view the imported data in the tables using Alpha Five’s default browse.
PAGE
12
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Now, we can export the tables and data to SQL Server.
PAGE
13
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




At this point, we’re defining the SQL database connection and the tables to export. We’ll be connecting to the
SQL database via AlphaDAO.
PAGE
14
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




If your SQL database isn’t already running, start it now. You’ll have to drill down a few levels to get to the
Create SQL Connection String dialog, where you can select the correct type of database, select the actual
server, select or create the database, and test the connection.
PAGE
15
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




It’s useful at this point to save the connection string from the Connection String dialog. We used the name
nwtest.
With that done, you can open the Alpha database and the target database simultaneously in the AlphaDAO
Export Genie, and select all the tables for export.
PAGE
16
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




When you close the Genie, you’ll have the opportunity to save the export operation configuration: we called it
nwtest.
PAGE
17
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Now we can run the export operation, and see the results.
PAGE
18
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Using the Alpha Five SQL Explorer, we can now view the tables in the SQL database through the nwtest
connection.
PAGE
19
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




From the context menu that pops up on a right mouse click, we can choose to show the data in the SQL table.
PAGE
20
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




That data, thankfully, looks just like it did in the original Access database and the intermediate Alpha
database.

This is a success as far as it has gone.
PAGE
21
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




What’s wrong with this SQL Server Management Studio database diagram? The tables are present in SQL
Server and the rows, but there are no primary keys, no foreign keys, no relations, and no indexes. If you know
what you’re doing, it’s a matter of a few minutes to add all of these from the diagram view. If not, it might be
time to ask for help (http://www.alphafivedatabase.com/access/contact.asp) from someone with SQL Server
experience.
Access-to-SQL Server specific migration
The main advantage of the data migration method that we just demonstrated is that it will work with any SQL
database supported by Alpha Five, not just Microsoft SQL Server. However, there are two methods available
from Microsoft that may capture more of the database assets for the specific case of an Access database moving
to SQL Server: the Access SQL Server upsizing wizard and SSMA for Access.
The Access SQL Server upsizing wizard
We’ll walk through the Access SQL Server upsizing wizard very briefly. In Access 2007, open the Northwind
2007 sample, and go to the Database Tools ribbon. In the Move Data group, click on SQL Server. The
upsizing wizard will open. Accept the default option, Create new database, and click on Next.
PAGE
22
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



The next screen is where it might start to get hairy. The SQL Server instance you use must have SQL Server
authentication mode enabled, and you must have a login that has CREATE DATABASE privilege. If you try
to use an instance of SQL Server 2008, once you log in successfully you may get the baffling error message:

This appears to be a bug in Access 2007, as of Dec. 2008. It might be fixed in some later version.
If you use an instance of SQL Server numbered between 6.5 and 2005 and have the correct privileges, it works
fairly well, and you wind up with a database that has all the tables, data, relations, primary keys, and indexes.


You don’t want to change the Access application.
PAGE
23
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




As you can see in this diagram, the SQL Server database can be ready to go immediately after the Upsizing
Wizard completes.
PAGE
24
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




SQL Server Migration Assistant for Access (SSMA for Access)
The SQL Server team at Microsoft also produces an upsizing wizard of its own, called SQL Server Migration
Assistant for Access (SSMA for Access) (http://www.microsoft.com/sqlserver/2005/en/us/migration-
access.aspx). There are multiple versions of SSMA. Since this page includes copious instructions, readmes,
FAQs, white papers, and case studies, we won’t belabor the process here. It’s certainly worth trying,
assuming that your Access database is in good shape.
SSIS
If you find that you need to make significant changes to your database schema, you might want to consider
using SQL Server Integration Services (SSIS). This powerful ETL tool replaced Microsoft Data
Transformation Services (DTS) starting with SQL Server 2005. This tool is launched when you select
import/export data from the database menu in SQL Server Enterprise Manager.
You can connect to a variety of data sources, including Access.
PAGE
25
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9





You have the option of creating your tables prior to input, or letting SSIS create new tables based on the
existing Access table definitions.
PAGE
26
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Additionally, there are several transformation and mapping options.
PAGE
27
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9





This is just a brief example of how you can migrate your data using this method. SSIS provides well-written
wizards to walk you through the process, and there are numerous sources for help online.
Step 2: Create an Alpha Five project
Once you have your database set, you’re ready to create your Alpha Five Web application. Open Alpha Five,
and create a new, empty database. We created “Web demo.adb” in “My Documents\Alpha Five V9\Web
demo,” but the location is not important.
Step 3: Create a connection to your SQL database
Select the Tools|External Databases|Database explorer menu item, and click on the “+” at the top of the
window. Specify a connection string for your SQL database, using the Build button.
PAGE
28
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




PAGE
29
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Save the connection string under a name. We’ll call it “nw.”

You should now see the SQL database in the Explorer under its name.
PAGE
30
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




PAGE
31
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Step 4: Define a grid
Open the Web Projects control panel and create a new Web component.

Make it a Grid.
PAGE
32
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




The Grid Builder will appear when you press OK. Choose AlphaDAO and the named connection nw. You
can do a lot by configuring the Grid with the Grid Builder. Play with it yourself and try different options. If
you get confused, consult the Alpha Five documentation, either inside Alpha Five or online
(http://support.alphasoftware.com/alphafivehelpv9/index.htm).
We came up with this Grid based on the Northwind Customers table in the SQL Server database, and saved it
as custgrid1.
PAGE
33
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Step 5: Define a Web page
In the Web Projects control panel, create a new Alpha Five Web page. The HTML designer will open. Switch
to the WYSIWYG tab, and save the current page as page1.a5w.
PAGE
34
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Step 6: Add your grid to your Web page
Select Insert Component from the top tool bar. Pick the custgrid1 Grid component we just saved.

The page will look a little strange in the WYSIWYG view.
PAGE
35
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




Add a title to the page by typing above the Grid Component, style it as you wish, save the page, and preview
the page in your browser by clicking on the lightning bolt icon on the top tool bar.
PAGE
36
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9




This page is completely live. Go ahead and sort rows, search for items (try Bl*, for example), and jump to
different pages.
Step 7: Continue defining components and pages
In our example, we haven’t made any of the fields on the Grid editable, we haven’t turned on the detail view,
and we haven’t set any validation properties. You can go back to the Grid Builder at any time and change
your settings using the Edit button at the top of the Web Projects control panel. You can publish your site at
any time using the Publish button at the top of the Web Projects control panel. You can add defined
components to multiple pages.
You can use your existing Access forms as guides to what you might want to do in your Alpha Five Web
application. You may also want to redesign the application to work better on the Web, or to take better
advantage of the capabilities of Alpha Five. As you’ve seen, Alpha Five development goes quickly, so feel free
to experiment with the application until it does what you want.
PAGE
37
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



Think about using the Alpha Five Navigation component where you would have had menus. Think about
using a GridLinker for parent-child tables, such as customers and orders. Think about using a Tabbed
GridLinker for more deeply nested relationships. Think about using the Alpha Login Component to provide
role-based security for your application.

Step 8: Add code where needed
As an Access developer, you’re probably looking for the code. It’s there, but in most cases you don’t need to
write it yourself. If you edit your grid component and look at the Xbasic tab, you’ll see hundreds of lines of
code that have been generated for you based on your graphical selections. Near the top, for example, you’ll see
your SQL query:
tmpl.CS.SQL = "SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address],
[City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Customers]"
Alpha Xbasic is similar to but not identical to Access Visual Basic for Applications (VBA). Alpha Action
Scripting is similar to Access Macros. Alpha Genies allow you to build code quickly, as you’ve already seen.
You’ll eventually find yourself writing a little Xbasic code, but you won’t need much, and using Genies and
Action Scripting will get you started gently.
Ajax
Alpha Five V9 has Ajax support in the form of JavaScript libraries and Xbasic functions, but we will not cover
Ajax in this white paper. It will be much easier to enable Ajax effects in Alpha Five Version 10, and doing so
won’t involve much effort at all. For example, a Grid built with Alpha Five Version 9 will automatically
acquire basic Ajax capabilities when upgraded to Alpha Five Version 10, and you will be able to enable
advanced Ajax capabilities by setting a few options in the Grid Builder.
We hope that you’ve enjoyed this white paper. Please tell us what you think of it by emailing
marketing@alphasoftware.com.
In the next section, we list a few useful sources of additional information about Alpha Five.
Extensive, step by step self-paced training videos are available to help you master building Web applications
(and desktop applications if you desire) with Alpha Five.
(http://www.imakenews.com/alphasoftware/index000327891.cfm)
Please email marketing@alphasoftware.com for more information on these videos.
Bio:
Martin Heller, a Web and Windows programming consultant, is a Contributing Editor, reviewer, and blogger.
He develops databases, software, and sites; and writes, edits and consults from his office in Andover,
Massachusetts, as he has for over 20 years.
PAGE
38
of
39
Moving an MS Access Database Application to the Web with Alpha Five Version 9



For more information
Alpha Software Web site, http://www.alphasoftware.com
Alpha and Access Web site, http://www.alphafivedatabase.com/access/
Alpha blog, http://blog.alphasoftware.com/
Alpha forums, http://msgboard.alphasoftware.com/alphaforum/
Alpha migration help, http://www.alphafivedatabase.com/access/contact.asp
Alpha online documentation, http://support.alphasoftware.com/alphafivehelpv9/index.htm
Johansen, Mark, A Sane Approach to Database Design (Lulu, 2008, 187 pp., $30, ISBN 978-1-4357-3338-1);
this is also available as a PDF from Alpha for $14.
Extensive training videos are available. Please contact marketing@alphasoftware.com
Books and Alpha Five Products, https://www.alphasoftware.com/shop/index.asp
If you would like Alpha Services (https://www.alphasoftware.com/services2/ ) to take your MS Access (or
any other desktop app) and convert it to a modern, fast AJAX powered Web application, please fill out this
short form http://www.alphasoftware.com/services/proservice.asp
PAGE
39
of
39