|
Database-Driven Web Sites
Introduction to Databases on The Web
By D. C. Conlin
Connect to your data, no matter where you are.
(Thousand Oaks, CA -- Feb. 28, 2006) -- How many times have you been in a meeting with clients at their office and found yourself repeating the phrase, "I'll have that information for you as soon as I'm back in my office"? If you're like me, that inability to grab data at any time was an annoying problem that you made sure was solved, as quickly and as cheaply as possible. I've made sure that important data is in a database always within my reach, whether I'm in my office or on the road. You can, too.
Modern databases provide convenient storage for data, but accessing that data isn't always so convenient. A database can be placed on a network where anyone connected to the network can access the database. However, an inability to connect to the home network means that the database can't be accessed, either. A common solution is to set up Microsoft Windows Terminal Server or some other remote access software to access the home network from a remote location, generally through a Virtual Private Network (VPN) to ensure security. The remote access software acts like a "window" on the home network, displaying screen shots on the remote workstation as if you were using a workstation directly connected to the home network. Providing these screen shots is much faster than transferring the actual data, so this solution has the effect where it's "just like being there." However, Terminal Server or other remote access software can be expensive, so this may not be an option for everyone.
Another common solution is to Web-enable the database by creating a Web application front end (a front end is a "window" application used to view and manipulate the data in the database) and placing it on a Web server, which is then connected to the Internet. The back end database (a back end is where the actual data is stored) remains on the home network, which includes the Web server, or the database may even be placed on the Web server. Anyone with a Web browser who can access the Internet can access the front end to retrieve data from, or store data in, the database. The Web application front end is often referred to as a "thin client," since it doesn't require much in computer resources to operate, as the Web server is doing most of the work.
Microsoft Access provides a rich environment for developing useful database solutions very quickly and cheaply, but Access is a multiuser database application intended for desktop access via a network. An Access front end isn't a "thin client," since it requires far more computer resources than a Web front end, and the Access objects (forms, reports, modules, etc.) can't be accessed directly from the Web, only the data in the tables.
So how do you take your investment in Microsoft Access database development and put the data on the Web? The most common solution is to develop an Active Server Pages (ASP) or ASP.Net front end for the Access database back end and place both on a Microsoft Internet Information Services (IIS) Web server. However, IIS and ASP are not very secure, so placing critical business data in an Access database on the Web with this platform may not be such a good idea. Additionally, most Web host providers use Linux operating systems and Apache Web servers, so finding a Microsoft Windows server with IIS to host your ASP or ASP.Net Web site may be difficult, and it's nearly always more expensive. And if you already have a Web host hosting your Web site and that Web host doesn't offer a Microsoft Windows server with IIS as the Web server, you won't be able to use the ASP/ASP.Net with IIS solution unless you're willing to move your Web site to another Web host provider. The amount of effort and time to do this can range from being fairly inconvenient to unacceptable, so it's advisable to select a Web host that can meet current and expected future needs to avoid ever moving a Web site.
Fortunately, you have plenty of options to make your data accessible from the Internet. In this introduction I'll briefly cover a number of the options available, ranging from those that require a minimal amount of work and no prior programming knowledge to those that require uploading the data directly from your Access database file to another online database, such as MySQL, Microsoft SQL Server, Sybase or even Microsoft Access if no other option is available, and building a Web application front end in various Web scripting languages. Over the next year, we'll publish step-by-step tutorials for how to accomplish such feats, so you'll be able to do this yourself.
Static data publishing
If the data doesn't often change, the Access database file doesn't even have to be placed on the Web server. For example, a Web site creation tool such as Microsoft FrontPage, Microsoft Publisher, or NetObjects Fusion can be used to create a catalog of products by creating the HTML file for each record in the "Products" table. Such Web sites are known as static database-driven Web sites. Even if there are thousands of records in the table, it only takes minutes (with broadband Internet access) to "publish to the Web" by automatically generating the HTML files, then uploading them onto the Web server. These HTML files can be hosted from any Web server, regardless of the Web server's operating system.
Of course, a static database-driven Web site doesn't have "live" data. However, as the data changes in your Access database, republishing to the Web will automatically make the Web pages "current." The Web site creation tool will again generate an HTML file for each record, then upload these HTML files to the Web server, reflecting the most recent changes in the database.
If you start with a static database-driven Web site with one of the Web site creation tools listed above and then later decide to make it a dynamic database-driven Web site, then be aware that even the current version of Microsoft Publisher (2003) cannot create a dynamic database-driven Web site. Perhaps Microsoft Publisher 2007 will offer this feature, but don't bet the farm on it. Microsoft FrontPage 2002 and 2003 offer the Database Interface Wizard for publishing dynamic data. NetObjects Fusion can easily switch from static to dynamic data publishing by using a component known as a "connector." Both NetObjects Fusion Connector for Macromedia ColdFusion (1.08 MB) and NetObjects Fusion Connector for Microsoft Active Server Pages (2.23 MB) support dynamic data publishing.
Benefits
The static data publishing option will allow you to keep your data secure, because your Access database remains in your hands, not on the Web server where hackers could easily reach it from the Internet. How do hackers reach the database from the Internet? A Web page that connects to a database must use a connection string to point to the Access database file, which any hacker can use to copy the file, update tables in the file, or overwrite the file on the Web server, so preventing access to the Access database file is the best way to keep the data from being stolen or altered.
In addition, the Web site creation tools automatically create the Web pages without any programming on your part. If the NetObjects Fusion connectors are used, then these will automatically connect the Web pages to the database without programming on your part, too, but the Access database will need to be uploaded to the Web server, so it will no longer have the security of being inaccessible to hackers.
Dynamic data publishing
Dynamic data publishing requires that the database be placed on the Web server, so security is an issue, especially with an Access database. Access databases can easily be compromised, even when user-level security (secure workgroup *.MDW file) or shared-level security (database password) has been applied, so don't store data that needs to be safeguarded, such as SSN's and credit card numbers in your Access database.
Security for online databases
To help reduce the risk of data compromise, place the Access database in a hidden directory on the Web server and place appropriate Windows security permissions on this directory. A hacker won't be able to copy the entire file nor overwrite it with a bogus file, but if you allow write permissions, then he'll still be able to alter the data with action queries via the Internet. However, unless this is a read-only database, you won't be able to block hackers from updating records without blocking the legitimate users as well. If users will only be reading the data from an online Access database, then the best option is to make the file read-only.
A more secure method is to load a SQL Server, Oracle, Sybase, MySQL or other client/server database located on a Web server with the data from the Access database located on your home network. A client/server database requires authentication and doesn't give users access to the database files themselves, unlike a file-based database like Microsoft Access. The data in the Web-based client/server database would be duplicated data from the Microsoft Access database on your home network, so there will be a time delay between data synchronizations of the two databases. Depending upon the circumstances this may not be acceptable, but for most applications this situation won't cause confusion or other problems, as long as users know what the time delay is between data synchronizations.
Options for the front end
A Web front end needs to be built to access the online database. Web scripting languages are used to build Web pages, but not all Web scripting languages can be used to connect to the online database. For example, JavaScript is a very common scripting language, but only server side JavaScript (scripting used for the Web server) is able to connect to ODBC databases. Client side JavaScript (scripting used for the user's browser) cannot connect to ODBC databases. Only Netscape Web servers are capable of running server side JavaScript, but unfortunately the majority of Web servers are Apache, IIS, and the like, so don't count on being able to use server side JavaScript to access your online database.
One workaround is to use Asynchronous JavaScript Technology and XML (AJAX), which makes asynchronous calls to the Web server to retrieve XML documents to dynamically manipulate Web pages from JavaScript. The relational data must first be converted to XML documents and be stored on the Web server, but recent versions of Access are capable of exporting table data to XML documents, so this won't be a problem unless you're stuck with an older version of Access.
JavaScript is by no means the only scripting language to consider when building Web pages that must connect to online databases. VBScript, Perl and PHP are also popular scripting languages for online databases.
Since Java applets can connect to online databases, Web pages can use these Java applets for the connections. However, Java applets can take a long time to load in the browser, and the user must have a Java-enabled browser for these applets. Many people have simply turned this off to avoid security risks. Also, Java applets tend to use Sun's JDBC-ODBC driver (a bridge between Java Database Connectivity and Other Database Connectivity technologies), which shouldn't be used for production databases since it's not as reliable or as stable as third-party ODBC drivers.
As previously mentioned, ASP/ASP.Net is the most common front end for Access databases on the Web. This combination requires a Windows server, so if the Web server platform is on UNIX or Linux, this combination is out of the question.
Also as previously mentioned, many Web site creation tools are capable of creating Web sites that automatically connect to online databases, thereby alleviating the need to manually script the dynamic Web pages. However, Microsoft Publisher, including Microsoft Office's 2003 version, cannot be used as a dynamic data publisher, since it cannot connect to a database in real time to select, update, add, or delete records.
FrontPage 2002 and 2003 offer the Database Interface Wizard for publishing dynamic data, as well as for displaying database content on a static Web page. While non-Web developers can use FrontPage to easily and quickly create a data-driven Web site, FrontPage offers less flexibility and customization that professional Web developers get from other Web site building tools and Web scripting languages.
Macromedia's Dreamweaver, Microsoft's Visual Studio .Net, and Microsoft's Atlas provide sophisticated programming tools that can create Web sites connected to dynamic data. (Atlas is available as a free download, where you'll also find links to videos and tutorials.) These tools allow the Web developer to customize the front end to any degree necessary, since they are highly flexible and are limited only by the Web developer's skill and imagination. Of course, these skills must include knowledge of the proprietary programming languages of these tools.
For those Web developers who would rather write Web applications in any scripting language of their choice, many generic scripting language editors are available, including HTML-Kit, which is also available as a free download. HTML-Kit is a highly customizable and extensible integrated development environment for programming applications using multiple file types, including HTML, XHTML, XML, CSS, XSL, JavaScript, VBScript, ASP, PHP, JSP, Perl, Python, Ruby, Java, VB, C/C++, .NET C#, Delphi / Pascal, Lisp, SQL, and more. Numerous tutorials, even in multiple languages, are available for HTML-Kit.
For newcomers who may not have prior knowledge of Web scripting or of Web site design tools, Microsoft offers some free Web site building tools for beginners to get their feet wet. Microsoft's Visual Web Developer Express Edition and Microsoft's ASP.NET Web Matrix can help you create a Web site that will connect to online dynamic data, including Microsoft SQL Server and Microsoft Access databases. You may download these free tools and find links to tutorials and starter kits at the links I've provided.
Yet another option for building the Web front end application is Java Server Pages (JSP). JSP's are platform independent, so it doesn't matter whether the Web server is on a UNIX, Linux, or Windows computer. And developers no longer need to learn the Java language to write Java scriplets. However third-party servlet containers are required to run Java servlets on Apache Web Servers, Microsoft IIS, and other Web servers, so lack of these containers on a Web host's Web server would prevent their usage.
Summary
A Web-enabled database can provide an effective and cost-efficient means to collect and distribute data and make that data available at any time, anywhere. The computer platforms, Web technologies, and database engines available, as well as whether the Web site will display dynamic or static data, will dictate which of the many choices to make in designing a database-driven Web site.
The job of displaying database content on a Web page can be made easy with the right tools and skills, but if you've never done it before, finding the right tools and figuring out how to use them can be daunting. Over the next year, we'll be publishing our own step-by-step tutorials and providing links to other tutorials on the Internet that will help you create a database-driven Web site that will put your data always within easy reach.
You can read about your favorite expert contributors.
Copyright © 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 28 Feb. '06:
|
