Access database sharing strategies

Question

What are the strategies you employ to let multiple people work on an access database?

Is it possible to host it online and have its features still functional without having to develop a custom frontend?

MS Access as a software has a few nice features that don't require any programming to configure:

• Drop down lists - choose one
• Multi Checkbox lists - choose multiple

Is it possible to get all of these features available even when hosted online? I'm basically thinking of an alternate way to quickly get people to work with data using GUI features like the above without going the webapp<>MySQL way.

Answer 1

I have never needed to support it myself, but from what I heard so far, performance dramatically breaks down as soon as you need to support multiple users writing simultaneously. I think this is because Access uses simple file locking to implement isolation, and this just is not the right technique for a concurrent database system.

Answer 2

Hosted on-line? Do you mean on the network? Technically it will work on a network but there is a reason MS-Access in not in Visual Studio - it is not considered a development platform - it is a desktop application. When MS-Access first hit the scene many people built applications using it. The multiuser functionality just is not there. Upto four or five users is ok. But I would not go for more.

Answer 3

One way i've heard of, is to import the access database into a SQL Server database.
(Almost any version will do.).

Then link to the SQL Server database with Access and let users use it as they did before.

Look at this link: http://office.microsoft.com/en-us/access/HA010345991033.aspx

If you want an online solution i'd recommend going with a normal web application architecture. (Talking to a proper database.).

Answer 4

Multi-user Access apps are pretty easy to do for small workgroup user populations in the 15-25 ranger or smaller. Above that, a developer should consider upsizing to a server back end, with the trade-off being greater administrative overhead for the server vs. having to program the app more carefully if you retain the Jet/ACE back end.

As to online access, you this isn't possible over HTTP, but if you have a Windows Terminal Server available, you can host your app there and give users access to that. This is actually an extremely easy and efficient and inexpensive way to support remote users of an app, though the larger the user population, the more problematic it becomes. But by the time an Access app has a user population that would strain a Windows Terminal Server setup, you're no longer going to be using a Jet/ACE back end.

And with a server back end, you could give access to a SQL Server on a VPN over the Internet, and if you write your Access app really efficiently, even over a standard broadband connection, your users could still work productively.

Then there's the future of Access: in Access 2010, a great deal of work has been done to integrate with a host of new features in Sharepoint 2010. If you create your A2010 app using the new type of Access web forms and reports, your app can be uploaded to a Sharepoint server running the new Access Services, and it can then be used running in a web browser (not limited to IE and not dependent on any plugins or web controls, as was the case in the past with the completely worthless Access Data Access Pages). The data store can either be a SQL Server, or you could keep it Jet/ACE for users not accessing it via the web browser, and have the data stored in Sharepoint for the online users. Also, you can have an app integrated with Sharepoint running locally in Access that uses Sharepoint when connected to the Internet, and still be able to work offline when disconnected. When connected again, you synch your local changes with the Sharepoint server, resolve any differences and continue working.

The features are really quite remarkable, and according to what I've heard and seen, if the Access app is built entirely of web forms and reports, it will look and function identically when run in Access and when run in the web browser via Sharepoint. And if you need to have client-side features that you don't expose to the users running the app in the browser, you can still use traditional Access objects!

The Access development team's blog has a number of posts on what's coming in A2010, and there's a good video posted there demonstrating how A2010 integrates with Sharepoint 2010's new Access Services.

This constitutes a quantum leap in Access's web capabilities, which were previously almost non-existent, and I'm quite excited about this. I was formerly quite wary of the changes being made to Access that seemed entirely to make it a servant of Sharepoint, but now I can see that the benefit to Access users and Access developers will be huge.

Answer 5

You have some good comments here. Keep in mind that things have changed quite a bit for access 2010.

Access 2010 allows you to build web applications. The development process is very much the same as it’s been for years, but you can’t use VBA in forms for these web applications (you use a new macro language). This new feature set allows you to publish applications you build to a website. Here is an video of an application of mine running in access 2010, and at the halfway point in the video I switch to running the access application 100% in a web browser:

http://www.youtube.com/watch?v=AU4mH0jPntI

The above is for access 2010…due out this year. The above will require you to be running SharePoint services, or use an hosting service that supports "access web" services.

For previous versions of access, for all intents and purposes, it’s not a web based system at all. Now when you say multiple users, you have to clarify what kind of users and where they plan to be. If your users are on a local office network, then MS access can be used as a multi user system right out of a box with no additional coding and programming required. It is recommended however that you split your application into a front end part that’s deployed on each user’s computer. This Concept as outlined in the following article of mine.

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Now, perhaps the users are going to be on notebooks and in different locations all over the country? In this type of case you are attempting to connect over a wide area network, or have users connect to the application over the Internet. This is a different problem. In this type of scenario, a good solution is to use something like SQL server for the backend, and you continue to deploy the Access front ends to each user’s computer. This application tends to be about the most cost affordable also. And using sql server + ms-access means you get to continue developing in Access for the most part like you always done. Another way to accomplish wide area use without resorting to sql server is to use something called terminal services. I outline these possibilities in the following article:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

As mentioned, a few others here posted links to some of the new SharePoint features that you can consider using, but they not out untill later this year.