Microsoft Access Developer Transition to Internet/Web Applications

Version 3 – May 30, 2005

 

 

Executive Summary

 

Don’t consider using Access Data Access Pages for a true internet application if you are planning to use the built in Access/Jet database.  Not feasible due to a security problem with RDS which is required for connection to the Access/Jet database.  Works OK in an intranet environment and I am told it works just fine with an SQL database..

 

Take a hard look at Visual Web Developer Express 2005 – Beta 2 available as of this writing and free – this web development environment lets you create forms and grids for view/add/change/delete without any coding!!

 

Also take a look at ATAF and Raptier for generating web sites from an Access database.  ATAF can generate forms that show data one record at a time.  Raptier only does grids.

 

A third product worth looking at is Microtools Access Wiz - MS Access to .NET Converter.  It actually converts some Access forms.  I got it to work once but the second time, I could not get it to work.

 

Finally, be sure you understand the final server environment in which your application will operate.  Below I show how to find out the URL name of the path the database will be stored in.  You can then setup a similar path on your development PC.  That way you don’t have to learn about relative addressing and such.

 

You can contact the author at       bobalston9    AT    yahool    DOT     com

 

 

 

Preface

 

I am writing this to help some other poor soul who attempts to make the transition from being a successful developer of applications using Microsoft Access to building Access database based applications on the web.  If you are comfortable with VBA and with form events in Access that you are probably ready for the transition.  If not, I don’t suggest trying. 

 

Even if you are comfortable with VBA and Access form events, you may still find it a difficult transition.  Hopefully some of this will help make the road less turbulent.

 

Access Built in Web Capabilities

 

If you are looked into Access’ built in web capabilities, Data Access Pages, you may have already noticed that they are not as capable nor as full functioned as Access Forms.  For example, you can’t have a sub-page in a data access page.  And only a very few web page events exist as compared to the large number of events for Access Forms and controls.

 

If you first try Data Access Pages, as I did, by trying to convert all the forms in one of your Access apps you may be disappointed in how convertible they are.  What – worked well for you?  Wonderful.  Lucky you!

 

Access to Web Converters

 

So I started off being disappointed with Data Access Pages.  I started looking for something better.  I researched Access conversion software.  Found some out there.  Several are quite expensive.  Many work not only for Access databases but any ODBC compliant database; they work because they create “grids” like the table data view in access or a spreadsheet view.  No ability to convert the Access forms.  Well, that was the initial limitation in Access’ Data Access Pages.

 

 

Web Development Environments

 

OK, if not an access conversion, how about something in a web development environment, an “Access for the web”.  Executive summary:  such does not exist.  Hopefully Microsoft will continue to develop Access’ own web capabilities.

 

But there certainly are capable web development environments available. 

 

Web Matrix

First, listed first because it is free, is Microsoft’s own Web Matrix.   This is a FREE tool for developing ASP.NET web applications.  And, it supports Microsoft Access.

http://asp.net/webmatrix/default.aspx?tabIndex=4&tabId=46

 

Note there is a nice online tutorial – see the left sidebar.  Also you can download Web Matrix from this page.

 

IT is a nice development environment.  It has some automated capabilities for building simple grids with Access.  It has some wizards that assist in developing applications.  However, I quickly found I had to get into code to get anything beyond trivial to work.  And that is why I love Access – because in Access, you don’t have to.

 

FrontPage

Second is Microsoft’s FrontPage.  I happened to have a copy of MS Office that included FrontPage.  It also has some development wizards and capabilities to work with Access databases.  And it can generate complete add/change/delete applications.  But they involve multiple pages – cumbersome as compared with native Access forms.  But they do work.  And if you can use a site that allows you to use Microsoft Front Page extensions to publish your web, that makes it quite easy.

 

Visual Studio.NET

Third is the Cadillac – Microsoft’s Visual Studio. NET.  I used version 2003 – I think a later version may be available.  It looks very similar to Web Matrix (but since it was developed first, the relation is actually the reverse.)  This is a very powerful development environment.  And takes a bit of work to learn.  But once again, I found myself into the code business.

 

Summary:  So I ended back at Access and Data Access Pages.  I decided to work around the limitations.  If it won’t handle subforms, just find another method. 

*******************************************************************************

***** IMPORTANT UPDATE ***** 

UPDATE:  When implementing my first app using Data Access Pages, I had an epiphany and finally realized that my DAPs were still actually using my local PC database. 

 

I learned more about UseRemoteProvider and the need to specify a UNC reference to the database

 

\\myPCName\DBdirectory\DBName.mdb

 

Note that the DBDirectory must be a directory name available for Web sharing (right click on the directory in Windows Explorer).

 

Also I just figured out that since the pages are all HTML, there is no server side processing.  All DB processing is client-side via Active X/ADO.  To access the database (Access/Jet database) on a server, in addition to setting the proper DB location reference and setting useremoteprovider in the Access page definition, the server must also be setup properly to support RDS or Remote Data Services.  This is the link between the ActiveX in the page displayed to the jet database back on the server.

 

In my case, I could not get this to work using my own PC as a IIS server.  My PC works just fine for ASP and ASPX pages but not RDS.  I did some reading and found you must also configure a control file called mdfsmap.ini  - but read “Setting up the configuration file (mdfsmap.ini) for the remote service  provider is a bit tricky and needs a bit of trial and error.”  I tried and still can’t get it to work.

 

Then when doing research on the RDS problems, I find out that Microsoft has “deprecated” RDS – made it obsolete.  Also, it is not widely used if at all by ISPs due to security concerns.  So, even if I could get it to work, I could not use any public server, again for use to access a jet database.

 

So if you are considering Access data access pages and a jet database for Internet – forget it.

 

I have gone back to using ASP pages.

 

*******************************************************************************

***** IMPORTANT UPDATE ***** 

 

Visual Web Developer Express 2005 Beta 2

 

Microsoft released Beta 2 of another new product for web development.  This one you can download for free

http://lab.msdn.microsoft.com/express/vwd/

 

This product is like Visual Studio and Web Matrix.  The key here is that you can create several kinds of forms, that show data in a grid or a form with one record at a time, with retrieval as well as add/delete/change – ALL without any coding.  Now this is getting interesting!

 

This seems to be brand new – as of May 2005.  The books aren’t even out yet – target for them is September/October 2005.

 

There is some documentation available.  Check out this thread for more info:

http://forums.asp.net/940634/ShowPost.aspx

 

 

 

Note that in the Appendices I have more information on the web development environments I tried as well as the conversion software.

 

 

Final Web Host for Your Application

 

I strongly recommend you early on identify the final web host that will host your application in production.  Doing so will ensure that will actually host your app and you can setup your development environment to make the transition much less painful.

 

Access’ data access pages do not require use of ASP – active server pages nor the newer .NET versions ASPX.  That means that more web sites are probably able to host the web site.  However, it does require a web site that will allow you to embed an Access database into your web site and update the database.

 

With this restriction, you can probably eliminate web hosting from the vendor you get your high speed internet connection.  I use Cox.  They do not allow such.

 

 

Try out your web host

 

Once you find a web host, I strongly recommend you create something straightforward to find out how to upload files to the host, how their directories work.  Also to verify that you CAN update a database in a web site.

 

But there is another reason to try them out.  You need to understand their file structure and how web sites relate to the underlying operating system file system.  That is because you are using Access database and it is accessed through the O/S as a file.

 

You can ask your vendor and hopefully they will tell you.  Or you can write a simple web page to tell you.  To do this you need to use a command called “mappath”.  This will map the actual path on the underlying file system of a file name you specify.  And you can specify the file name of the web page that is running the command.

 

Here is the page I use, an ASP web page called   aspmappath.asp

 

<%@ Language=VBScript %>

<html>

 

<body>

 

<%

 

response.write server.mappath("aspmappath.asp")

%>

 

</body>

</html>

 

You also need to know if the web host has any conventions for where you can put your Access database.  For my web host, it must be in a directory named “database”.  This is because the host needs to establish special file access permissions to that directory on the host’s file system – so web users can update the database.

 

 

Now I know the Web Host File Structure – So What

 

Now you can establish an identically named file structure on your own PC.  This will make it HUGELY easier to deploy the web application.  Without this, you will need to change the path to the Access database ON EVERY WEB PAGE THAT ACCESSES THE DATABASE.  Ugh.

 

A web site I am using gave me this mappath result

 

G:\e\bobalston\mappath.htm

 

Since I put my test page in the root of my web site, it told me the directory structure.

I am going to follow the host web site convention of putting my Access database in a directory named “database”.

So the real path to this database will be:

 

G:\e\bobalston\database\mydb.mdb

 

I can now proceed to setup a duplicate file structure on my test/development PC

 

Create Duplicate File Structure on Test/Development PC

 

So I created a “g” drive on my own PC for development.  You can use Partition Magic or any of a number of partition making software to create multiple logical drives on your hard disk.  And you can do this without disturbing your existing files – as long as you have the empty space.

 

So if your hard drive is “C” and your CD is “D”, you need to establish a “Virtualx” directory on your existing hard disk.  For example, if you need a drive “G”, create directory  c:\virtualg.

 

Then you can use the DOS command  subst g: c:\virtualg

This sets up a drive mapping of G: to your directory  c:\virtualg.  Now you can use it as if it were really a physical or logical G: drive.

 

Suggest you put the command in a small BAT file, virtualg.bat and set it up to run at windows startup.

 

Now it is easy to create my other directories and put my database in

 

G:\e\bobalston\database\

 

Access will also need to know where to put its generated web pages.  I suggest “web”

 

G:\e\bobalston\database\web

 

You can set this as a default in MS Access so each web page you create will be put in the correct folder.  And it will point to the location of the database.

 

When you carefully upload all directories and files in “G:\e\bobalston” to the web host, they will all be in the same locations.

 

Configure a Virtual Web on your Development/Test PC

 

Finally you need to configure the IIS server on your PC to create a virtual web site.  I suggest you use the last directory in your mapped path.  This is probably part of the host site web site name.  For example, my web site is

 

http:111.222.333.444/bobalston

 

So I created a virtual web named “bobalston” that points to this directory:  G:\e\bobalston\

Then on my own PC, I can get to my web pages via:

 

G:\e\bobalston\web\anyname.htm

 

Set the Database Directory to allow Sharing and Updating

 

Finally you must go to Internet Explorer and right click on   G:\e\bobalston\database\

Select “Sharing and Security”.  Click “Share this folder on the network” and also click “Allow network users to change my files”.  This allows the database to be updated from the web application.  If you don’t do this you will get a message something like this when you try to add/delete or change data: 

                       

                        Operation must use an updateable query.

 

 

Create Your First Test Web App

 

 

OK.  Now let’s do something constructive.  Copy an Access database to your database directory.  Open it.  Click on the wizard to create a new web page.  Use a simple table.

 

Follow the prompts and watch carefully when saving the htm page to save it in the proper location.  Also select the option to make this the default.

 

Once saved, you should be able to bring up the web page using your browser.  Make sure your IIS server is active.

Type in the proper address:

 

http:localhost/web/<whatever>.htm

 

You can use your PC name if the “localhost” does not work.  For me it is:

 

http://bobsDell4400/web/<whatever>.htm

 

If it comes up slick.  If not, you have some detective work to do.

 

Error Message about needing an Updatable Query

 

If you get an error message when attempting to do updates via the web pages, the problem may be that you have

Not established permissions so that network users can update your files.  Go to the directory holding your access database.  Right click on it and select Sharing.  Click on share to network users and allow network users to update the directory.  This is needed because the “user” associated with your web interaction is not your PC logon ID but rather a default internet user.  And that user is accessing the file as if they were somewhere else on the network.

 

 

Publish the First Test Web App to the Host Server

 

Now publish the test web app to the host server.  Use FTP or your host’s upload procedure.  Be sure to get the files in the proper directories.

 

Now try to access it via the host:

 

http:<host name or address>/<web name>/web/<whatever>/htm

 

Now make sure that you are really accessing the database on the host server.  Rename or remove the development database on your PC to ensure the access is really to the host server, via useremoteprovider and RDS.

 

 

Security for the Application

 

Another challenge for a new Access Database using Data Access Pages is security.  If you use Microsoft Access’s user specific security, it appears that you must enter the ID and password for each and every form.  At least I could not find out how to get around this.

 

I also thought I might use the kind of access security created automatically by FrontPage when building a web app using access.  But couldn’t figure out how to convert it over for use in Data Access Pages.

 

Finally I found NetLogin.  There is a free version and a Lite version which costs $5.99

The free version only allows 3 users and no groups.

 

http://www.aspworld.com/solutions.asp 

This is an add-on security capability that is quite easy to install and customize.  Your Data Access pages, which are HTM pages, must be renamed to ASP and a piece of code copied into each page.  This insert is customized to indicate which user(s) can access the page.  If you buy the Lite version, you can use group names and authorize all users in a group to access a page.

 

 

 

Questions or Comments

 

If you have questions or comments on this document, you can contact the author at

 

Bobalston9     AT    aol      DOT    com

 

 

Appendix I – Web Development Environments

 

Internet Web Sites & Publishing

Including Access Form and Database Conversion Software

 

May 30, 2005

 

 

Web Site & Web Page Development Environments

 

 

1) Microsoft FrontPage 2003

 

Used for developing and publishing the TulsaAlstons web site.

 

Advantages:

1)      Nice interface to web sites with Microsoft Front Page Extensions (which Cox has) for publishing web site and updates.

 

2)      Very easy to use for static content.

 

3)      Excellent automatic linking of pages in a hierarchy and automatic templates for site navigation.  Only very basic navigation options. 

 

4)      Has a Database wizard that will create view/edit/add/delete screens for any table.  Works slick for Access database in the web.  When you add the Access database into the web, it automatically creates the connection information required.

 

Disadvantages:

 

1)      Navigation options are a bit limited.  For example, no way to expand an option to see the next level down while retaining the main hierarchy, as in an expandable tree structure.

 

 

 

 

Online Tutorials:

 

Database Power with Microsoft FrontPage version 2002.  Excellent introduction!

http://www.microsoftfrontpage.com/content/ARTICLES/dbpower.html#_Toc517024652

 

 

Books:

“Special Edition Using Microsoft Front Page 2000” – Que – by Randall & Jones

 

 

 

 

 

 

 

 

 

2) Microsoft Web Matrix

 

http://www.asp.net/webmatrix/default.aspx?tabIndex=4&tabId=46

 

FREE web site environment and generator.  Like a “mini Visual Studio” but without all the Visual Studio power.

 

Advantages:

 

1)      FREE

 

2)      Has automated templates for accessing and updating database table data.  Limited capability – limited to use of grids, like a spreadsheet view of the table. 

 

3)      Some capabilities require manual intervention to tie database access objects to grids or other page controls.  But still it gives a lot of help in generating database connections.  Properties of objects are easily accessed.

 

Disadvantages:

 

1)      No built in HELP

 

 

 

Tutorials:

 

Web based guided tour.  Pretty good but not enough for a novice to become productive:

http://www.asp.net/webmatrix/tour/getstarted/intro.aspx

 

Downloadable PDF introduction document.  Limited depth but useful as an introduction:

http://asp.net/webmatrix/web%20matrix_doc.pdf

 

 

ASP.NET Web Matrix Curriculum – 15 downloadable lessons

http://msdn.microsoft.com/academic/techdown/techprod/aspnet/aspnetmatrix/default.aspx

 

 

 

 

Books I ordered:

 

"Microsoft ASP.NET Web Matrix Starter Kit" – by Pope - Used from Amazon 3rd party $8.79 including shipping.

 

 

"Web Matrix Developer's Guide" – by Mueller - Used from Amazon 3rd party $13.99 including shipping

 

 

HELP/Resources:

 

Forum

http://asp.net/Forums/ShowForum.aspx?tabindex=1&ForumID=30

 

Debug Your Web Matrix Code

http://www.fawcette.com/vsm/2003_04/online/aspnet_jgoodyear_04_04_03/

 

 

 

3) Microsoft Visual Studio .NET 2003

 

Microsoft’s premier web development environment.

 

 

Advantages:

 

 

 

Disadvantages:

 

 

 

 

Tutorials:

 

 

 

 

Books I ordered:

 

"Sam’s Teach Yourself Microsoft Visual Basic .NET 2003 (VB .NET) in 24 Hours Complete Starter Kit" – Used from Amazon 3rd party $12.49 incl shipping.

Had good reviews from two MS Access developers who found the book very good when transitioning to VB.NET and ASP.NET

 

 

HELP/Resources:

 

ASP.NET

http://asp.net/Default.aspx?tabindex=0&tabid=1

 

 

Forums at ASP.NET

http://asp.net/Forums/default.aspx?tabindex=1&amp;tabid=39

 

 

4) Visual Web Developer Express 2005 Beta 2

 

Microsoft released Beta 2 of another new product for web development.  This one you can download for free

http://lab.msdn.microsoft.com/express/vwd/

 

This product is like Visual Studio and Web Matrix.  The key here is that you can create several kinds of forms, that show data in a grid or a form with one record at a time, with retrieval as well as add/delete/change – ALL without any coding.  Now this is getting interesting!

 

This seems to be brand new – as of May 2005.  The books aren’t even out yet – target for them is September/October 2005.

 

There is some documentation available.  Check out this thread for more info:

http://forums.asp.net/940634/ShowPost.aspx