Moving An MS Access Database Application To The Web With Alpha Five V9
Moving an MS Access database Application
to the Web with Alpha Five v9
Firstly, 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 compel ing reasons for doing this. We wil list
the key reasons in bul et 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 instal 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 v9 al ows 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 wil learn how to move a Microsoft Access desktop application to the Web with
Alpha Five. You'l see how easy it can be, and when you're done you'l understand the process wel
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 wil have to give it some thought. While in many cases you should be able to convert
your database schema and migrate your data automatical y, you wil have to manual y 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 smal fraction of the time that it took to implement
the application original y. The best news of al is that your converted application wil shine on the Web.
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 al for you or provide
you any help you may need. To fil out a services request, please fil 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
al of these assets except for charts; several third-party developers, including Imagio and InfoSoft,
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 you are moving an Access desktop database to
the Web. First of al , a Web application can potential y have a very large number of total users, and a
moderately large number of simultaneous users. This means that record locking and overal 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 graceful y, 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é, DB2, Microsoft SQL
Server, MySQL, Oracle, PostgreSQL, Sybase SQL Anywhere, and Sybase SQL Server. Microsoft SQL Server
is the database most frequently offered by hosting providers on Windows-based Web server accounts,
so that’s what we’l use for our example.
Other SQL databases can work just as wel . 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, al of
which are free.
Our general plan is to migrate the schema and data first. Then, we'l 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 original y 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 al ow 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 7 high-level components that you can configure and use on an A5W page along with
HMTL controls and Ajax widgets.
There are seven types of embeddable Web components in Alpha Five. They are:
Dialog - The Dialog component is a form that al ows 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 al ows you to edit the fields of any selected record.
Grid Linker - The GridLinker component contains and displays multiple Grid Components, al owing 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 wil pack and re-index Alpha Five tables and sets. (This is
only useful when you use Alpha Five tables, and is irrelevant if al your tables are in a SQL database.)
Navigation System - The Navigation System component al ows 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 al ows you to switch to any other grid in any level with a click of the
mouse.
You’l find that you use the Grid, GridLinker, and Tabbed GridLinker for the bulk of your database table
and view displays. You’l find these easier to define and lay out than the equivalent Forms and controls
in Access, and you’l 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.
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:
What you’re trying to determine at this point is whether the database schema wil make sense for the
Web application. There may be tables that you don’t want to import at al : it would be helpful to know
that before you go to the effort of importing them. There may be missing relationships, or badly
normalized tables, or missing primary keys. One Access tool you can use at this point is the Analyze
Table wizard on the Database Tools ribbon, which wil 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, make sure that
you 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.
It’s very important for SQL databases to be properly normalized; to have unique primary keys for al
tables; to have foreign key constraints for al relationships; and to have indexes on al fields used as
foreign keys. If none of this makes sense, you may want to refer to the Johansen book listed in the For
more information section of this paper for advice on database design.
If you’re stil unsure about the schema, consider bringing Alpha Consulting 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 typical y the hardest
part of the application development lifecycle, and also 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 wil not apply. Instead, you’l want to design the new schema, and 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 can 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’l start by discussing the generic process, which wil 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 instal ed by default in My Documents\Alpha Five V9\MDBFiles\.
Click the OK button, and then select a location and name for your new Alpha database. These choices
are relatively unimportant; we’l throw away the Alpha database once we’ve exported it to SQL Server.
Select al the tables to import, and none of the other assets:
When the import operation is complete, you’l see a status screen:
This database import had no errors. If you encounter an error, you can go back to the Access database
and fix the problem, delete the partial y imported Alpha database, and try the import again. If you don’t
understand the error message, you might want to ask about it on the Alpha Forums. When you’re done,
you’l 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:
Now, we can export the tables and data to SQL Server:
At this point, we’re defining the SQL database connection and the tables to export. We’l be connecting
to the SQL database via AlphaDAO:
If your SQL database isn’t already running, start it now. You’l have to dril 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:
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 al the tables for export:
When you close the Genie, you’l have the opportunity to save the export operation configuration: we
cal ed it nwtest:
Now we can run the export operation, and see the results:
Using the Alpha Five SQL Explorer, we can now view the tables in the SQL database through the nwtest
connection:
From the context menu that pops up on a right mouse click, we can choose to show the data in the SQL
table:
That data, thankful y, 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:
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 al of these from the diagram view; if
not, it might be time to ask for help 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 wil 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’l 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 wil open: accept the default option, Create new database, and click on
Next.
The next screen is where it may 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 successful y you may get the baffling error
message:
This appears to be a bug in Access 2007, as of 12/2008. It may 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 wel , and you wind up with a database that has al the tables, data, relations, primary keys,
and indexes:
You don’t want to change the Access application:
As you can see in this diagram, the SQL Server database can be ready to go immediately after the
Upsizing Wizard completes:
SQL Server Migration Assistant for Access (SSMA for Access)
The SQL Server team at Microsoft also produces an upsizing wizard of its own, cal ed SQL Server
Migration Assistant for Access (SSMA for Access). There are multiple versions of SSMA listed at
http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx. 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:
You have the option of creating your tables prior to input, or letting SSIS create new tables based on the
existing Access table definitions:
Additional y, there are several transformation and mapping options:
This is just a brief example of how you can migrate your data using this method. SSIS provides wel -
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.
Save the connection string under a name. We’l cal it “nw”:
You should now see the SQL database in the Explorer under its name:
Step 4: Define a grid
Open the Web Projects control panel and create a new Web component:
Make it a Grid:
The Grid Builder wil appear when you press OK. Pick 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 at
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:
Step 5: Define a Web page
In the Web Projects control panel, create a new A5W page. The HTML designer wil open. Switch to the
WYSIWYG tab, and save the current page as page1.a5w.
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 wil look a little strange in the WYSIWYG view:
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:
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.
Think about using the Alpha Five Navigation component where you would have had menus. Think about
using a Grid Linker for parent-child tables, such as customers and orders. Think about using a Tabbed
Grid Linker 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’l see hundreds
of lines of code that have been generated for you based on your graphical selections. Near the top, for
example, you’l 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 al ow you to build code quickly, as you’ve already
seen. You’l eventual y find yourself writing a little Xbasic code, but you won’t need much, and using
Genies and Action Scripting wil get you started gently.
Ajax
Alpha Five V9 has Ajax support in the form of JavaScript libraries and Xbasic functions, but we wil not
cover Ajax in this white paper. It wil be much easier to enable Ajax effects in Alpha Five V10, and doing
so won’t involve much effort at al . For example, a Grid built with Alpha Five V9 wil automatical y
acquire basic Ajax capabilities when upgraded to Alpha Five V10, and you wil 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 tel 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.
Please email marketing@alphasoftware.com for more information on these videos.
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. 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 fil
out this short form http://www.alphasoftware.com/services/proservice.asp
Document Outline
