|
|
 |
 |
| |
Databases
|
|
| |
Question/Problem
How do I connect to my MS Access database? |
|
Answer/Solution
To connect to your MS Access database you need to embed a
connection string (strConnect) in the body of any
script which needs to talk to a database. Here's an example:
'Database connection info and driver
'strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="
& Server.MapPath("database\dbname.mdb")
Copy and paste this string directly into your code (replacing
the database\dbname with the name of your Access database) and
your script should work correctly.
Following is a script that you may use to connect to your MS
Access database:
<%
Dim adoCon 'Database Connection Variable
Dim rsConfiguration 'Holds the recordset
Dim strCon 'Holds the Database driver and the path and name of
the database
'Table fields to read in
Dim strContractsID
Dim strFirstName
Dim strLastName
Dim strEmail
'Create database connection
'Create a connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Database connection info and driver
'strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="
& Server.MapPath("database\dbname.mdb")
'Alternative drivers faster than the generic access one above
'strCon = "Provider=Microsoft.Jet.OLEDB.3.51; Data
Source=" & Server.MapPath("database\dbname.mdb")
'This one is if you use Access 97
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" & Server.MapPath("database\dbname.mdb")
'This one is for Access 2000/2002
'-----------------------------------------------------------------------------------------
'Set an active connection to the Connection object
adoCon.Open strCon
'Intialise the ADO recordset object
Set rsConfiguration = Server.CreateObject("ADODB.Recordset")
'Initialise the SQL variable with an SQL statement to get the
configuration details from the database
strSQL = "SELECT * From Contacts;"
'Query the database
rsConfiguration.Open strSQL, strCon
'If there is data in the recordset then read them in
Do while NOT rsConfiguration.EOF
'Read in the data from the recordset
strContactID = rsConfiguration("ContactsID")
strFirstName = rsConfiguration("FirstName")
strLastName = rsConfiguration("LastName")
strEmail = rsConfiguration("Email")
Response.Write vbCrLf & "<p>"
Response.Write vbCrLf & "ContactsID = " &
strContactID & "<br>"
Response.Write vbCrLf & "FirstName = " &
strFirstName & "<br>"
Response.Write vbCrLf & "LastName = " &
strLastName & "<br>"
Response.Write vbCrLf & "Email = " &
strEmail & "<br>"
rsConfiguration.MoveNext
Loop
'Reset server object
Set rsConfiguration = Nothing
%>
NOTE: The Server.MapPath statement makes it
unnecessary to know the physical path. |
|
|
| |
| |
 | |
