I’m about to build a system that will require significant web presence. I’ve done some Google searches and found a little here, a little there. What is your preferred method of posting simple forms on the web that would have a live connection. For example if you wanted to have field personnel update task list, or customers select items from a combo box type object. These would need to be accessible from a phone or a computer without additional licensing, and it would require a basic password scheme.
I am reluctantly reaching the conclusion that Microsoft Access is a great way to learn about databases, a great way to build a single-site client-server database for a restricted population of users, and effectively useless in a web environment.
Access 2010 databases integrate fairly well with Microsoft Sharepoint Server, but only in its Enterprise guise. This means that the TCO of web-hosted Access databases is high, whether you install Sharepoint Server locally (needs powerful server, incurs high software licensing costs...), or use hosted services such as AccessHosting.com or Office 365. In addition, what actually happens is that SharePoint lists are linked and synchronised with restricted web-compatible objects in your Access tables. That means that the functionality available is the subset of what SharePoint is capable of and of what Access can do. Therefore a whole host of features simply can't be used or are highly restricted in their usefulness. These include table design, form design, report design, the relationships window...
These user accessible design features are precisely those that have for nearly twenty years now made Access great. My guess is that the Access product marketing people have lost the inside-Redmond war with the SharePoint people, and have won a skirmish by being allowed to do the necessary developments to make Access into a SharePoint client. But Access is much better adapted to being an SQL Server client, since SQL Server is also a relational database - which, for all its strengths as a content management server, SharePoint emphatically is not.
Therefore it seems to me that to create a web-accessible database application, the choices available include:
1. Use a desktop database which is web enabled. The only candidate of which I am aware that is end-user accessible is FileMaker Pro. Be careful again - the TCO gets high if you need a server edition of FMP.
2. Make the leap to a server-oriented database. The main candidates include MS SQL Server and MySQL. The TCO for SQL Server can be quite low - you can start with the SQL Server Express Edition - but the costs go up when you want features like a true database management interface. MySQL is a very capable database available in open source and premium editions. You can find tools that make it reasonably user-friendly to access and to build in. But even with those tools, the learning curve is steep by comparison with Access. In particular, there is no equivalent to macros and the programming language usually used with it (PHP) is less approachable than VBA.
I genuinely think that Microsoft had and have a great product in Access - but that they have shirked the challenge of bringing it into today's Web 2.0 world. Failures of courage have expensive consequences: for them, loss of "mind share"; and for you and I, who have to learn new ways. My choice - however reluctantly - is MySQL, PHP and an open source approach.
Personally, I prefer using access databases for every web project that does not process huge number of records. One of the great advantages of using Access database on the web (IMHO) is that you can easily download the database file, and do a lot of complex tasks offline, using MS Access installed on your computer and also it's very easy for backup. If you put it in a directory that is not located inside wwwroot of your server, you do not have to worry if someone will download it and steal your data (or you can simply forbid downloading files from the directory where the mdb file is located, or you can forbid downloading mdb files on a server level).
If you use access database with PHP, WhizBase, ASP, ASP.NET or some other server-side scripting engine, your users do not need a license for MS Access to be able to use your application.
PHP is open-source and ASP comes with IIS (without additional cost AFAIK) but they require significant programming skills.
On the other hand, WhizBase (which I prefer) is a proprietary software (not free) but has a very short learning curve and is much better solution for people without programming experience.
Regarding PHP and Access databases, some may say that it does not work but I had no problems with it. Here's some sample PHP code:
Code:
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=".getcwd()."\Northwind.mdb");
$sqlQ = 'SELECT ProductName, QuantityPerUnit, UnitPrice FROM Products ORDER BY ProductName';
$rds = new COM('ADODB.Recordset');
$rds->Open($sqlQ,$conn,1);
ASP uses pretty much the same way of connecting the access database (as in the example above) and WhizBase has native support for access databases (you simply provide the name and location of the mdb file and name of the table/query you want to use).
So IMO there's no reason why you should not go with access database and build a web solution around it. I'd suggest to do some reading about ASP (and ASP.NET), WhizBase, PHP and even some other solutions (I have no experience with Python, Ruby, Perl but they might also be a good solution) and to find what suits you the best.
I use all three solutions depending on particular project demands, but more than 80% of those I develop using the WhizBase.
