Microsoft Access:

MS-Access is the popular desktop database system from Microsoft. It is one of the easiest and most user friendly database management systems currently available and is very widely supported.

Your Default Access Database

Each ZeeHosting.com website hosting plan includes a default MS Access database (.mdb file and ODBC DSN) that you can begin using immediately with your website. The actual database file was placed in your /database folder during account activation, with a name based upon your domain or chosen user name.

You can choose to use this default database, or you can upload your own Access 97 or Access 2000 file to use with your website.

Your Default ODBC DSN

To perform READ and WRITE operations on your Access database file, an ODBC Data Source Name (DSN) has also been created. The name of your DSN is contained in your ZeeHosting.com Welcome Kit.

Note: While you may choose to upload your own Access database file (for which you can request a DSN), we strongly encourage you to use the default .mdb file and Data Source Name. To make use of the default DSN, simply rename your own database file to the same name of the .mdb file found in your /database folder, and upload the file to the /database folder, overwriting the file that is there.

Connecting to Your Database

As a starting point for your own database-driven web pages, we have provided you with an example page that queries your Access database and returns a simple record set. You can test this page at:

http://www.yourdomain.com/scripts/testodbc.asp

The Connection Code

The code snippet below contains an example of ASP/VBScript code that can be used to connect to an Access database and return a recordset.


Sub TestODBC

dim strODBC    ' Connection string
dim strSQL     ' SQL query string
dim d1         ' Data connection object
dim r1         ' Recordset for query results
dim i          ' Counter

' Define a connection string for the database
'   The connect string is used to login to the database.
'   DSN=DSNwbnet is the ODBC data source name and
'   PWD=testpassword is the password for the database.
strODBC = "DSN=DSNmyweb; PWD=myPwd;"

' Define an SQL query
'   The query is to retrieve the ID, Name and Description fields
'   from a table named Test, and will return the records in
'   order of ID.
strSQL = "SELECT ID,Name,Description FROM Test ORDER BY ID"

' Create a database connection object
set d1 = Server.CreateObject("ADODB.Connection") 

' Open the connection to the database
'   The OPEN method of the database connection object is used to
'   connect to the database as defined by the ODBC connection
'   string.
d1.Open strODBC

' Execute the SQL query
'   This will pass the SQL query to the database.  The EXECUTE
'   method will return a recordset object containing the
'   records that match the query.
set r1 = d1.Execute(strSQL)

' Check to see if any records were found
'   If no records were returned by the query, both the BOF
'   (Beginning of File) and EOF (End of File) properties
'   of the recordset object will be set to TRUE.
if (r1.EOF = True) and (r1.BOF = True) then

' process 'No Records Found' condition here

else

' Set the counter variable to zero
i=0

' Move to the first record
r1.movefirst

' Start sending an HTML table to the browser...

End Sub
 

 

Security with MS Access Database Files

When your website is first created on our servers, the /database folder is set up with the appropriate file permissions for READ-WRITE access to your .MDB files located in the folder. These permissions are required in order to allow your website scripts to add, modify and delete data from the database. The only users allowed to perform these tasks is the anonymous internet user (commonly known as the IUSR), and your own FTP account.

To prevent the public from downloading your MDB files from the web, we have removed HTTP permissions from the /database folder. This ensures that nobody can use a URL such as "www.mysite.com/database/mydb.mdb" to trigger a binary download of your database. This also means that you cannot place ASP scripts, HTML files or other files normally accessed via HTTP in the /database folder.

If you place a database file in a folder other than /database, or if you delete and recreate the /database folder, your files will not have the permissions required for proper database operation. Your database may also be exposed to public downloading. If you need the appropriate database permissions set on a particular folder, please contact Technical Support who can set or restore your database folder security settings.

For added protection of your data file, we recommend that you use a database password. This password helps protects your data in the event that the .MDB file is obtained by unauthorized users. If you set a password on your .MDB files, you will need to code that password into your scripts. Note that if someone has access to your script source code, this will reveal the password to the database file. Take care to ensure your development and backup copies of your source code and database files are kept in a secure place.

You may also wish to encrypt your .MDB files to ensure further protection. This will prevent a user who obtains your database from using a binary scanner (or even a text editor) from reading your database in its raw format. There is some additional processing overhead incurred when using ODBC and other DAO methods to access an encrypted .MDB file, however on small to medium database files, we have found that this does not cause any noticable delay in your overall site performance.

Database passwords and the database encryption options are found within Access itself. See the Tools -> Security options found in the main Access menu.

Related links:
a) Odbc for MS Access.
b) Create DSNs Instantly.
c) Databases.

Created: 2003-09-15
Modified: 2005-01-15