by David Hockenbroch

Created on: December 12, 2008

Microsoft Access is an excellent data solution for small websites. It's easy to use to set up the database, and it is very easy to access and manipulate the data for your website.

Access's graphical user interface will feel very familiar to most users almost immediately. The process used to enter data into tables in Access is very similar to Microsoft Excel, which many users have encountered before. Everything from setting the data type of a field to establishing relationships between tables is as simple as pointing, clicking, and perhaps dragging. This eliminates the need for the database designer to know a large number of Structured Query Language (SQL) commands to perform these actions. Reducing training time is always a plus.

Once the database is created, it can be uploaded to a web server. Then, it must be set up as an Open Database Connectivity (ODBC) data source. This process is very simple. The user just needs to tell the server where the database file can be found and choose a data source name, a user name, and a password for the data source. This will allow the data to be accessed using ODBC drivers.

What to do from there depends on the server-side scripting one chooses to use. PHP is a very popular server-side scripting language, and it works wonderfully with ODBC data sources. The user needs to know only three functions to use their data on their website with PHP. The function "odbc_connect" is used to establish a secure connection to the database. It takes the data source name, user name, and password that were set up when the ODBC data source was configured as arguments, in that order. The function "odbc_exec" is used to execute an SQL query on the connection established by "odbc_connect". It takes the connection from "odbc_connect" and an SQL query as arguments. It returns the data in a two-dimensional data array. The third function, "odbc_fetch_array," retrieves the next line from the results returned by "odbc_exec". It takes the array created by "odbc_exec" as an argument. The individual elements of this array will have the names used as the field names in the database.

So suppose we wanted to take a username and password that had been posted by a form and see if the password matched the one in the database for that user. That PHP would look something like this:

$connection = odbc_connect("mydatasource", "myusername", "mypassword");

$resultset = ($connection, "SELECT * FROM USERS WHERE USERNAME = \"$_POST["username"]\"");

$resultrow = odbc_fetch_array($resultset);

if($resultrow["password "] == $_POST["password"]) . . .

From there you, could do whatever you want to with that data. It would be treated just like any other string.

Thanks to Access's familiar, comfortable, easy-to-learn interface and the excellent support from one of the Internet's most popular scripting languages, Microsoft Access is a great choice for online data management.

Learn more about this author, David Hockenbroch.
Click here to send this author comments or questions.