Running an Access Application on a Website

[Wendell]

Data Access Pages were added in Access 2000 as a way to display data from a database in a web browser. However, they had a number of limitations - for one thing, the end-user had to be running Internet Explorer, not Firefox, Opera, Safari or Chrome, and they had to have Microsoft Office installed. In addition, they weren't very pleasing visually, and you had very limited error handling options. Thus, when 2007 was released three years ago, they removed that feature from Access, and the preferred path became either .net or Sharepoint, and that trend has been continued with Access 2010. As long as you are using 2003, you still have that option at your disposal - and your users should be able to edit data if things are configured correctly. Helen Feddema's Access 2002 Inside Out has one of the most complete descriptions of how to implement DAPs that I've run across - Chapter 18 is devoted to that topic. There are also a number of tutorials available on the web that give you step-by-step instructions.

[Carol W.]

Wendell,

Thanks so much for pointing me to http://accesshosting.com. Their pricing is pretty reasonable -- $79 (US) per month -- and I can set up a 30 day free trial to evaluate the service. I left a voice mail message for them and received a call back quickly. The fellow with whom I spoke said they would consider a discount for non-profits if they can get a discount on what they pay Microsoft.

I just finished installing Office 2010 Professional. After I get used to things (lots of new eye candy ), I will put together a prototype of the app in question. Then I will set up the 30 day free trial account.

Thanks again.

[Carol W.]

Wendell (or anyone else),

Do you know if Access 2010 databases, when published to the web via Access Services (Sharepoint), support the use of linked tables as we use them in client based Access applications? I would like to keep the tables in a separate location (I was going to say mdb file but I know 2010's format is an accdb file) from the other objects (forms, queries, reports etc.).

Thanks.

[Wendell]

Unfortunately, that is one of the limitations as I understand it - the tables have to be stored as lists in SharePoint. It seems a bit odd, as there seems to be a strong connection between SharePoint and SQL Server, but that's my understanding. See Creating Web Databases with Access 2010 and Access Services for a description of how a database is published. If you need to have the tables available for existing Access apps that use the data, they would need to be connected to SharePoint as well (works like attached SQL Server tables sort of). BTW, Office 2010 still works with .mdb databases, and future versions may well also.

[Carol W.]

Wendell,

Thanks for the reply.

The reason I asked about linked tables is that I am trying to develop a strategy for modifying Access objects once an app has been published to the web without wiping out existing production (web) data. Inevitably, this will need to be done.

One thought (suggested by my husband, "PaulW" on this board) was to keep the tables in a separate mdb/accdb file (let's call it data.mdb) on the development computer, linking them to an mdb/accdb file containing all the other Access objects (let's call it code.mdb). The only file that would then be synched with the Sharepoint server would be code.mdb.

Question: Using the above strategy, would the tables on the Sharepoint server be wiped out because no tables were present in code.mdb? I would think not but . . .

[Wendell]

I'm not up to speed much on the maintenance of a SharePoint published Access database, but I believe the strategy is you always leave the tables in SharePoint Lists, and when you are doing maintenance on objects, the Access database links to SharePoint for it's tables. The reason for doing that is the users need to be able to continue to use the system while you are doing design or maintenance work. Note that there are some functions and controls that don't upsize to SharePoint. Let me do some digging and see if I can come up with a document that describes the publishing and maintenance processes in more detail. Afterthought: I believe what I heard in Portland was that the database must be in .accdb format.

[Wendell]

I did some digging through my notes and asked a couple of questions, and I was correct that the database must be in accdb format. It also appears that all you need is the Foundation part of SharePoint, rather than the System version as I indicated earlier. In your design of reports and forms you only want to use controls that are compatible with web forms - when you publish the compatibility checker will complain if you have something that isn't supported. You can also run it before publishing, which is recommended, but certain issues will only show up when you try to publish.

One of the new features that you will want to make use of is the data macros capability. They function sort of like triggers in SQL Server, and let you do validation, which has always been a pain on web based applications. On the issue of ongoing development and maintenance, there is a process that will let you copy existing web forms and convert them back to Access objects using the SaveAsText process. It is also possible to have "hybrid" applications where some people use web forms and some use Access forms, but I couldn't find much documentation on that concept.

Here are some documents I found that will hopefully provide some background on the process:
Microsoft Access Team Blog - Publish to SharePoint (part 1)
Microsoft Technet - Changes in Access 2010
Microsoft Access Team Blog - Data Macro Aliases and the Where Condition Demystified
Link to Channel 9 video demo of Access Services restaurant application - I think you may find this video with Kerry Westphal and Jeff Conrad useful as an overall view of the process.

[Carol W.]

Wendell,

Thanks for the links. I haven't had a chance to review them yet today but I will do so in the next few days.

[Wendell]

Carol, another possibility you might want to consider that I stumbled into this morning. Microsoft has a product called Windows Azure which they are commercially rolling out. I've not spent much time looking at it, but it appears to offer database support both for developers and users. It appears their AppFabric might be a viable development environment for you, although it looks to be .net with Visual Studio as the primary development environment. Be curious to know what you think - they do have a free testing and small business version.

[Carol W.]

Wendell,

I've never really done any programming in Visual Studio. I do have the software but haven't gotten into it at all. If I were more conversant in VS, Windows Azure might be an option for me, personally. I can see how it could be very useful for someone with VS experience.

I've done some reading on the links you sent yesterday. I also found part 2 of Ric Lewis' blog. The maintenance section in part 2 seems to answer my question, if I'm interpreting correctly.

In your published database, all of your tables are linked SharePoint lists. Any data updates you make on the Access client or through the web browser change the server data immediately. When you make schema changes on the client, these schema changes are propagated immediately to the server.

So, I think that a "test" accdb that is not synched to the web database might be a solution.

Will continue reading. Thanks for your interest.

[Carol W.]

Thought I'd post an update on my progress or lack thereof.

I've been playing around and have developed an accdb file in Access 2010. Everytime I add a table, form, query or module (no reports yet) I run the Compatibility Checker. So far, the Compatibility Checker reports that my app is compatible with the web.

Here's the part about which I'm unclear. A number of my forms (almost all of them, in fact) have VBA code behind them. This is how I'm used to developing an app. Is the compatibility checker ignoring the VBA code or is a form with VBA code really web compatible?

I know that I could find out the answer instantly by publishing it to a web host that offers Sharepoint 2010 and Access Services. My problem is that the one toward which I'm leaning nicely offers a 30 day free trial. However, I'm going on vacation on June 17 and don't want to waste any of the precious 30 days. So, I'll probably have to wait until I return to sign up for the trial with them.

In the meanwhile, does anyone have any insight (or predictions) about whether my forms with VBA code attached (and one module) will really be "web compatible"?

Thanks in advance.

[Wendell]

Carol, as I understand it, web forms are not compatible with VBA, and you need to use Macros. Some of them are compatible, but not all, and the compatibility checker will tell you which ones are or aren't when it looks at a form. I'll confirm that once I get back in the office, but I'm travelling for the next several days, so it will probably be early next week.

[grovelli]

Since you were talking about Azure, here's the latest: http://blogs.msdn.com/b/access/archive/ ... azure.aspx" onclick="window.open(this.href);return false;

[Wendell]

I'll confirm that once I get back in the office, but I'm travelling for the next several days, so it will probably be early next week.

I did check, and VBA is not supported on forms that are hosted on Sharepoint, so macros are the only option. However, as grovelli has noted, there is another option, that being SQL Azure. That is sort of the mixed environment that might make lots of sense in your case. For people who connect using Access they can use the regular Access forms. However people who use web forms will still only have macro capability. I'm not sure what your VBA code is doing - if it's data validation, lots of that can be done with macros, but if you are doing DAO or ADO behind the scenes, that can be a real challenge. BTW, enjoy your vacation, and forget about databases!

[Carol W.]

Wendell,

Just saw your 6/15 post. Am back from vacation and plan on signing up for the free 30 day trial at this site this week.

What puzzles me is that the compatibility checker says "The database is compatibile with the Web". Maybe it's ignoring the VBA code altogether?

I will know soon enough and will report back.

Thanks.

[Wendell]

As I understand the situation, it won't complain about the VBA until you actually try to move it to SharePoint - its a limitation of the Compatibility Checker. I hope you enjoyed your vacation as well - I got a short break in over this long weekend, and it was a welcome respite.

[BobSullivan]

I am a late-comer to this discussion, but I too have tried to work with Access over the web along with SharePoint. There are some nice benefits but also some ugly consequences involved with using SharePoint to hold Access tables.

1. SharePoint will not respect Access keys. SharePoint lists are autonumber driven, and a person with appropriate rights can go into a SharePoint list that is being used as an access table and create as many duplicate entries as they want. Chilling, isn't it?

2. Don't ever, ever, delete a SharePoint list used as an Access table and think that you can re-create it with the same name with no consequencies. Access establishes links to SharePoint lists through GUIDs, not names, and each SharePoint list has a unique GUID. While it may be possible to establish dymamic connection strings to maintain the link to the list through code, all the internet examples I've seen don't work (at least at my level of expertise).

3. We feel that SharePoint really begins to slow down when lists approach around 7500 records. Since Access tables can handle hundreds of thousands of records, this is a limitation.

Actually, if you have Office 2010, you might want to take a long look at developing your user interface forms and the like by using InfoPath. Microsoft beefed up InfoPath 2010 and many of the limitations of 2007 browser-based forms no longer apply in the new version.

The story behind all of this is that I created an online registration system using Access, SharePoint, and InfoPath. The user filled out an InfoPath form, which pulled some of its information shown in drop-down boxes from the Access database. when finished, the information on the form was submitted to and stored in SharePoint lists.

[Carol W.]

Today's update:

I signed up for the 30 day free trial and almost immediately discovered that the practice app I had been writing was not a web database.

To publish an Access database on a Sharepoint server with Access Services and to successfully run that database in a browser, it's not enough that one needs to save the database in accdb format. The application must be a web database. See this screenshot from Access 2010 File | New |Available Templates. I had to choose the highlighted "Blank Web Database" to enable the app to run in my browser.

access 2010 templates.jpg

This essentially means that my practice app can be discarded. I tried importing objects from the practice (non-web) database into the web database but they did not convert to web objects. I did not expect they would.

Oh well, it's a good thing this is only a "play project" for me. It means that I have to start all over from square one .

[petern]

Unless things have changed radically in Access 2010, you should be able to export your existing database (i.e. the tables) to Sharepoint via an upsizing wizard similar to the one that has existed for years. I did a quick search and you should look in External Data/Export/Sharepoint List. You would have to do this for each table.
Here's a URL for publishing a full database. In the See Also section on the right of the page, there is info about exporting tables or queries.
http://office.microsoft.com/en-gb/acces ... 9.aspx#BM2

My guess is the Web Database option is if you are starting from scratch. If you are starting from an existing application, you should just want to export it. As I stated much earlier in this thread, if the database is at all complicated, the performance through Sharepoint is hideous. At that point, I think you would want to go to SQL Server and build a dedicated Web App.

[Carol W.]

Petern,

Thanks for the reply.

After I posted my last update, I discovered that I was able to import the tables from a non-web database into my web database. They appear as web tables in the web database. So far, so good.

The same unfortunately did not hold true for forms and queries. When I tried importing them, they did not convert to web forms and web queries respectively.

I'm rapidly coming to the conclusion that using an Access 2010 web database on a Sharepoint 2010 server with Access Services is not going to be a solution for my application. The few web database sample templates that do come with Access 2010 are very simple applications. Mine isn't .

I am reconsidering my options on this project. Thanks for your input.