Tutorial on How to Connect to MS Access Using DSN Method
Microsoft Access is the database software included in the Microsoft Office Professional Suite. DSN (Data Source Name) is a method of naming a database so that it can be accessed from outside Microsoft Access. DSNs are used in ASP applications to obtain a connection to a database. This allows ASP Web forms to input data directly into an Access database. To connect to an Access database via DSN, the DSN must be initiated and accessed with the correct login information.
Other People Are Reading
Instructions
-
Assign the DSN
-
1
Launch the "ODBC Data Source Administrator" dialog box by clicking "Start," "Control Panel," "Administrative Tools," then "Data Settings."
-
2
Click the "System DSN" tab.
-
-
3
Add a data source. Click "Add." A dialog box will appear on the screen. Click "Driver do Microsoft Access (*.mbd)" then press "Enter." Another dialog box will appear asking for a name for database information. At the text box labeled "Data Source Name," type the name you would like to use for the database. Click "Select," then browse to the database that you will be accessing. Press "Enter" three times to confirm the new settings.
-
4
Restart the computer.
Connect to the Access Database in ASP using the DSN
-
5
Launch a code editor or Notepad. For Notepad, click "Start," "All Programs," "Accessories," then "Notepad."
-
6
Copy the following code and paste it to the blank document:
<%
'declare the variables
Dim Connection
Dim DSN
Dim Recordset
Dim SQL
'initialise the dsn variable
DSN ="DSN=example_dsn"
'declare the SQL statement that will query the database
SQL = "SELECT * FROM CARS"
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
'Open the connection to the database
connection.Open DSN
'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection
'now lets see if there are any records returned
If Recordset.Eof Then
response.write "There are no records."
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("Name")
Response.write Recordset("Year")
Response.write Recordset("Price")
Response.write ""
Recordset.MoveNext
Loop
End If
'close the objects and free up resources
Recordset.Close
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
%>
-
7
Save the file by pressing "Ctrl" and "s" simultaneously. Put the file in any directory and give it a name with the extension ".asp" then press "Enter."
-
8
Change the DSN to the one that you created. Find the line:
DSN ="DSN=example_dsn"
Change the text "example_dsn" to the name of the DSN that you created.
-
9
Change the fields to fields in your database. Find the lines:
Response.write Recordset("Name")
Response.write Recordset("Year")
Response.write Recordset("Price")
Change "Name," "Year," and "Price" to the values that you want to read from the database.
-
10
Save the file by pressing "Ctrl" and "s" simultaneously.
-
11
Run the script. Navigate to the location of the ASP file and double-click the file. A Web browser will open and generate a list of the elements that you selected from the Access database.
-
1
References
Resources
- Photo Credit database on paper image by .shock from Fotolia.com
