Lesson 18: Database Connections

To access a database, you need a database connection.

In this lesson, we'll look at how you can create a database connection. There are several options, but the simplest is a so-called DSN-less connection, which means a connection where you do not have to set up a Data Source Name (DSN) on the server, but create a shortcut to the database directly in your code instead.

If you have your own website, you should read about database connections on your host's support pages. Web hosts may have special rules for how to make your database connections.

In which folder should the database be stored?

The database can be placed in any folder. But if you want to add data to the database, it is important to have write permissions.

Again, you should consult your host's support pages for more information. Usually, you will have one folder with write permissions, which are often called "cgi-bin", "log", "databases" or something similar. Conditions vary from host to host, so it is not possible to give a more precise answer. If you work locally on your own computer, you can set the permissions yourself: right-click on the folder and choose "Properties".

How to make a DSN-less connection?

To create a DSN-less connection, you must specify the physical location of the database on the server. If you have your own server, you can find the location in Windows Explorer (or similar), but if you have a hosted website, you can use the method DocumentationServer.MapPath to find the location as described in lesson 14. For example, if the database is in the folder cgi-bin of your website, you will find the physical location like this:



Now we are ready to create the connection. It is done as follows:

	' ADODB connection object

	Set Conn = Server.CreateObject("ADODB.Connection")
	' Remember to specify the correct path to your database
	DSN = "DRIVER = {Microsoft Access Driver (*. mdb)}; DBQ =" & Server.MapPath("/cgi-bin/database.mdb")

	'Open database connection
	Conn.Open DSN

This way we have created a database connection, and can begin to retrieve and update data from the database. This is exactly what we will look at in the next lessons.

By the way, keep in mind that it is good practice to close the database connection again when you're finished retrieving or inserting data. This is done with the following code:

	' Close the database connection
	Set Conn = Nothing

In the next lessons, we will look at how you retrieve and update data in the database.

<< Lesson 17: Databases

Lesson 19: Retrieve data from a database >>