Date: April 23rd, 2008

Author: Susan Harkins and Drew Wutka

An Access database often outgrows its original purpose. When that happens, you face applying band-aid technology or upgrading to a more powerful database system, such as SQL Server Express or even SQL Server. But before you toss Access out the window and start signing purchase orders for consultants, developers, licensing, and new hardware, consider one more option — turning your Access application into a Web-based application. Let’s look at some reasons why this might make sense.

Shameless disclaimer: If you truly need a more powerful database system and can afford its trappings, spend and grow!

Note: This information is also available as a PDF download.

#1: Client versus server

A server-side database, such as MySQL, SQL Server, and Oracle, evaluates requests on the server side (sent in the form of a SQL statement) and then returns data to the client. Jet, on the other hand, lets the client do all the work. Jet is the database engine behind Access. Even if the database (.mdb) is on a network server, the client still does all the work. The server simply responds to client file requests.

This arrangement retrieves more then just the data across the network. As a result, indexes and unused data clog the network and slow things down. An alternative is to place the Access database on your Web server’s local drive and then build the interface on the Web server. Doing so creates an ad hoc server-side database that handles transactions on the server (using your code). Requests from the client are in Hyper Text Transfer Protocol (HTTP) format instead of SQL.

Recommendation: Put the Access database (the .mdb file) in a folder that isn’t shared. That way, users won’t have direct access to the database. Their only access will be via the Web server. Your code will serve as the layer that allows users to interact with the actual data.

#2: No client installation

A Web-based front end minimizes installation issues. Users need only a browser. The database doesn’t care whether the user is sending requests via a Windows PC, a Mac, or a machine running Linux.

#3: Easy cross-platform usage

You’re free to use your language of choice to create the Web interface and the code that the server users to interact with the database. Users get clean and standard HTML that almost all browsers can use.

Recommendation: Keep the Web interface simple to ensure that everyone can use it. If you need the advantages of client-side tools, such as client side scripting, Flash media, and so on, go for it. Just keep in mind that not every HTML feature works in every browser. The back end can be as complex as necessary because the Web server is the only one using it.

#4: Simplified security

Storing the database in a non-shared folder (see #1) restricts access. Only the Web server’s administrator has access to the database file. That leaves security to the Web server. Now, you might argue either way as to whether this method is more or less secure than a server-side database. However, someone with direct access to a machine with a server-side database could probably also gain direct access to that database.

In addition, a server-side database requires a network connection. An Access database on a Web server isn’t directly available. You can access the Web server, but not the database. Only the Web server can access the database on the server’s local drive. On the other hand, Access has a security system known as Access User Level security (this isn’t available with Access 2007). Most server-side database security systems are more secure than Access User Level security.

Recommendation: Even though you impose an almost absolute-type form of security by placing a database on a Web server, it can’t hurt to apply Access User Level security. The database is still an .mdb that you can copy and open on any machine that has Access installed. As a developer, you will probably have local copies of that .mdb (and copies on backup tapes for your Web server). Be on the safe side and keep honest people honest by putting a little extra security in place. Users via the Web interface won’t even know the additional security is there.

#5: Easy use of NT authentication

Using Visual Basic for Applications (VBA), you can determine the NT name of users logged into an Access database and thereby restrict which users can do what. However, this method isn’t foolproof, and it doesn’t truly authenticate users. Your Web interface (on an IIS Web server) can use Integrated Windows Security to authenticate user credentials to individual web pages.

#6: Goodbye to corruption!

Most developers complain that Access is susceptible to corruption. Used incorrectly, it certainly is. With an Uninterruptible Power Source (UPS) and redundant drives, your Web-based database (.mdb file) won’t suffer from corruption.

#7: No version problems

With the quick pace of upgrades, many of us have users spread across two and three versions of Access. Unfortunately, not all versions play well together. A Web interface eliminates version incompatibility issues because the Web server uses Jet. That means the Web server doesn’t even need Access — it doesn’t load Access. Your Web server doesn’t care what version of Access the client uses.

#8: Live, behind-the-scenes interface updates

To update an Access front end, you must copy or modify an .mdb file. Access won’t let you make changes while people are using it. (Beginning with Access 2000, you can make some changes, but a few still require exclusive access to the database.) In contrast, you can change the Web interface files (.asp, .aspx, and so on) whenever you like. The changes are almost immediate.

#9: Portability

viagra dosing

Every Windows OS since Windows 98 has had personal Web server capabilities. That means you can develop and test a Web site using a laptop running Windows 98 (or later). Using an Access database as the data source has a few benefits:

Recommendation: Jet allows many transaction type SQL statements. You can build and modify tables and views using SQL, along with the typical data reading and altering capabilities. Sometimes, if you put a system on a remote server where you no longer have the ability to get to the actual .mdb, it’s pretty simple to whip up an .asp page that lets you run SQL on the fly against the database.

#10: More users

By their very nature, Web interfaces are unbound. In other words, once a page is loaded, the interface is no longer connected to the database. But a bound Access front end maintains a connection to the source, and Jet limits you to 255 concurrent connections. Your Web application, unless you have 255 users hitting the database at the exact same moment (which would require approximately 30,000 users a minute at a transactions speed of .5 seconds) can have more concurrent users.

---

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner’s Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

Drew Wutka is a Microsoft Access/Visual Basic/Web developer for Marlow Industries, Inc. He also does independent contract development and has developed many free projects, such as the Microsoft Access MiniCalendar, the Dynamic FrontPage Navigation ASP Sitemap, and the Password Enabled Enigma Encryption VB program. You can reach Drew at dwutka@marlow.com .

Date: July 25th, 2007

Author: Greg Shultz

Windows XP provides a host of fonts that you can use to enhance your documents both onscreen and in printed form. When you install certain applications, you’ll find that even more fonts have been added to your operating system. Even so, you may still have a longing for more fonts.

Check out Dafont, which offers a huge repository of fonts available as freeware, shareware, demo versions, or public domain, that you can download and install at will. If you know what you want, you’ll appreciate the fact that the fonts are sorted alphabetically, by author, and by themes. If you’re just browsing, check out the new fonts and top 100 sections. viagra discount What makes this site even more intriguing is that you can type in your own text and see what it looks like in any of the available fonts.

Note: This tip applies to both Windows XP Home and Windows XP Professional.

Date: September 9th, 2007

Author: Greg Shultz

Everybody likes a good Windows XP tip — something that eliminates an annoyance, streamlines a task, or offers useful customization of a particular feature. Of course, not all tips are ideal for all people, so it’s nice when a bunch of them are pulled together so you can pick and choose. That’s the goal of this download, which provides a healthy assortment of tricks we thought might come in handy. And for those who’d rather zero in on just certain tips on the site, here’s a list of links to enable you to jump straight to items of interest:

I spend a lot of time copying and moving files between folders. More often than not, when I click on a file in Explorer, I want to copy or move it to another folder. That means I spend a good deal of time dragging files around or copying and pasting them.

But with a Registry hack, you can save yourself time: you can add Copy To Folder and Move To Folder viagra directions for use options to the right-click context menu. When you choose one of the options from the menu, you browse to any place on your hard disk to copy or move the file to, and then send the file there.

To add the option, run the Registry Editor [Hack #68] and go to

HKEY_CLASSES_ROOT\AllFilesystemObjects\shellex\ContextMenuHandlers

shellex tells you it's a shell extension key that lets you customize the user shell or the interface.

Create a new key called Copy To

Set the value to

{C2FBB630-2971-11d1-A18C-00C04FD75D13}

Create another new key called Move To

Set the value to

{C2FBB631-2971-11d1-A18C-00C04FD75D13}

Exit the Registry. The changes should take effect immediately. The Copy To Folder and Move To Folder options will appear. When you right-click on a file and choose one of the options, you'll be able to move or copy the file using a dialog box like the one shown in Figure 3-20.

Figure 3-20. Specifying a destination using the Copy To Folder option

Date: April 23rd, 2008

Author: Greg Shultz

If you’re using MSN Messenger as your chat and videoconferencing tool, you may never use Windows Messenger anymore and have removed it from the startup group to keep it out of your way. However, you may have seen it pop up on occasion and had to struggle with closing it down. The reason that Windows Messenger makes these impromptu appearances is that Outlook, Outlook Express and even viagra cost some Microsoft Web pages can still make it load automatically. Fortunately, you can banish Windows Messenger from your desktop by making an alteration to the local group policy with the Group Policy Editor. Here’s how:

Note: This tip applies only to Windows XP Professional.