Date: April 29th, 2008

Author: Mary Ann Richardson

Playsound ("C:I386Chimes.wav")

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: viagra alternative April 22nd, 2008

Author: Mary Ann Richardson

You’ve created a read-only form, yet the first control is highlighted when the form is opened. To prevent users from trying to enter data, you’d like no focus placed on any of the form controls. One way to do this is to create an unbounded text box, resize it so it’s barely visible, and then place it first in the tab order.

Follow these steps:

1. Click in the Width property box and enter .00001.
2. Click in the Height property box and enter .00001.

The resultant text box is shown below.

1. In Access 2007, click the Arrange tab in the Form Design tools group.
2. In the Control layout group, select Tab Order. (In Access 2002/2003, go to View | Tab Order.)

1. Under Custom Order, click to select the text box and drag it to the top of the list.
2. Click OK.

Now when the form is opened, the focus will be placed on the unbounded text box, which is too small to be visible.

Date: April 8th, 2008

Author: Mary Ann Richardson

Rather than add a command button to your form to open another object in your database, you can use a hyperlink. Using images, labels and command buttons as hyperlinks is an easy way to add a control that allows users to open and display an object without programming.

To create a hyperlink that opens the Product Categories List table from an unbound form in your database, follow these steps:

Note that instead of a label, you could also use a command button or image; just change its SubAddress property to the object’s address.

Unlike command buttons, hyperlinks only allow you to open an object and move to a location within it. Any programming is limited to the control’s on-click event.

Date: March 3rd, 2008

Author: Susan Harkins

Whether you take on freelance work or you support your organization’s IT needs by developing custom database solutions, you must build an efficient, easy-to-use database if you plan to stay employed. Like most things, there’s a right and a wrong way. You might not get credit for doing things right, but you’ll certainly hear about it if you do things wrong.

The road to the right database starts well before you start building tables. There are a number of things you can do before you build a database to make sure that the development process goes smoothly and that your custom database fits the organization’s purpose and goals. The following tips are aimed as Access users, but most of them apply to just about any custom database.

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

#1: Make nice

You’ll get nowhere without the support and guidance of two specific groups of people:

In a small company, one person might fill both positions, although they have different needs. However, that person’s experiences with the data are valid. It’s your job to find solutions that satisfy everyone, within reason.

#2: Bend but don’t break

Being just a developer won’t get the job done. Sometimes, you must be a diplomat. I recommend that you practice the art of persuasion: “Let me show you something…” will serve you better than “That can’t be done.” This may require you to think fast on your feet. Of course, “Let me work up an example” can always buy a little time.

#3: Actually review their specs

Sometimes, you get lucky and someone in-house supplies a list of specifications. If that happens, it’s information worth keeping, so don’t be too eager to trash the list. Working with those original specs will save you some time and might keep you from stepping on someone’s toes — never a good idea if you can help it.

#4: Compare the specs to the working environment

Most Access databases have just a few users, but Access can handle numerous users. You probably won’t build an interactive intranet database the same way you’d build the solution for a single user. Access seldom fails to meet the demands if you develop for multiple users from the beginning.

#5: How many keys are there to the front door?

Keeping hackers out of your intranet or Web-based database is much more complex than using Access’ workgroup security. In fact, if you need this article and you’ve taken on a Web-based database project, you might have bitten off more than you can chew — good luck! Access is certainly up to the challenge, but the truth is, most developers aren’t. That’s why IT professionals scoff at Access. The sad truth is, many developers don’t understand the Web. If you’re one of them, don’t take on a Web project hoping to learn on the job. You and your client will pay too high a price.

#6: Do the work

Sit down with the people who do the work and learn the process:

• Review all paper forms in the current process.
• How much data — both records and fields — will the database store?
• How much searching and sorting will the users require?
• Where does the data come from? Will the system need to accommodate foreign data?
• Will the system export data to foreign formats?
• Review the current reports and analysis. Talk with the people who use them, for insight.

In short, follow the data from beginning to end. There’s no substitute for knowing the data and the current motivations that push that data from collection to final form.

#7: Re-evaluate

Once you’re familiar with the specs and environment, you might have to shoot yourself in the foot. Access just might not be the best solution for your client. A more powerful system, such as SQL Server 2005 Express Edition, might be a better choice. Or Access might be just part of the solution. For instance, InfoPath’s XML-based forms or .NET forms might be more efficient than Access forms, especially if you’re publishing data to an intranet or to the Internet. Certainly, Excel’s analytical tools are superior to those Access provides. Don’t try to stuff the entire works into an Access-or-bust solution.

#8: Recommend the best route — not the easiest one

Don’t be afraid to suggest a major overhaul if you’re upgrading a legacy database. Neither the latest and greatest version of Access nor more expensive hardware will resolve performance issues that stem from bad design.

#9: Improve the process

Work with the end users to improve the manual process if there’s room for improvement, and there usually is. It’s a mistake to computerize the existing workflow until it’s the best it can be. Software alone won’t improve a bad routine — it’ll just change the problems.

#10: Define and redefine

Once you’ve gathered all your facts, compose a mission statement for the application. This might require one to several paragraphs. I’m not talking about a new set of specifications. Rather, give your client a realistic review of their needs versus reality. You’re simply restating the database’s purposes, but with the benefit of your insight into the workflow and organization’s needs.

#11: How’s that for quick response?

Once you believe you have a good feel for the client’s needs and the database’s purposes, create a series of mock-up forms to show the client. You’ll get a few oohs and ahhhs, but listen to the souls brave enough to say, “But wait…” Their insights may be valid and could save you some trouble down the road. On the other hand, this is where #2 can come in handy. Sometimes, people just can’t conceive of doing something any way but the way they know.

You can use graphics software to draw and print the forms or use Access — it’s a great rapid applications development (RAD) tool. And you can really impress your clients by actually using their data. Sometimes, a quick run at normalizing the data can help the mock-up process. It’s not strictly necessary, but it may show you some holes you might otherwise miss.

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.