Beginning ASP and Microsoft Access - Getting Started

(Page 2 of 4 )

Firstly, we all know how to communicate to a database for an application in Windows using a DSN. If you are not sure or can’t remember here is a brief demonstration.

By navigating to "Start->Settings->Control Panel->Administrative Tools", we get an icon "Data Sources (ODBC)". If we double click on this icon we would open a window. This is the ODBC Data Source Administrator window.

Let's say we wish to communicate to a MS Access database. We select the "System DSN" tab option and click on the "Add..." button:

This will open a new window where you select the appropriate driver -- in our case the "Microsoft Access Driver (*.mdb)". When we do this another window will now pop-up, this is dedicated to set up a DSN with properties.



We click the "Select..." button and navigate to the MS Access database in the new window. Now click OK:

 

You will see that you have successfully created a DSN for your system.

The DSN-Less Approach
Setting up a DSN is a great idea for an application that is built for your system. But if you want a user to locate a remote database from a remote location, a DSN-less method would deem more appropriate. This implication is suggesting that you might consider DSN-less methodology when it comes to dynamic web pages, which may be database driven.

So we will now consider this example. To tackle the task at hand -- that is dynamic web pages -- we will create a database with your data inserted and use a language such as ASP to communicate with the database and manipulate the HTML page. Once this is done, it is time to start programming. The first thought that comes to mind is how we can find the database we seek in its remote location.

As stated previously, the database will be sent to the web server. Hopefully, it will be located in the same location as the home page for efficient site structure. The Server class in ASP allows us to create an ADODB object. To find the database that we are after, we must map its path. That is, find its physical location. Once this is done, we use the object to open to database and retrieve the contents we require.

In the body of the page the data retrieval will look somewhat like this. Note that this example contains no user id or password. The reason is that this information is targeted at beginners. So, the code is designed in a simplistic manner and it may get a little confusing if all the information is thrown at you all at once:

<%

'Creating a connection object and opening the database
Set v_connection_object = Server.CreateObject("ADODB.Connection")
v_mapped_path = Server.MapPath("yadayada.mdb")

v_connection_string = "Driver={Microsoft Access Driver (*.mdb)};DBQ="&v_mapped_path&";"

v_connection_object.Open v_connection_string

'Query database and retrieve a set of ids from the table id
v_query = "SELECT id FROM yadaTable1"
Set v_recordset = v_conn.Execute(v_query)

'Listing ids
WHILE NOT v_recordset.EOF
%>
<LI> id = <%=v_recordset(“id”)%></LI>
<%
v_record_set.MoveNext
WEND

'Closing connection and setting the variables to nothing
v_users.Close
v_connection_object.Close
v_record_set = Nothing
v_connection_object = Nothing
%>

Notice that ASP script is very similar to Visual Basic code. The ASP scripts are embedded in the HTML using <%,%> tags.

If you have studied SQL programming then you would know that you are able to do a wide range of data manipulation and extraction of data from a database. You may want to do, something like this next example.  

Here we will update the database when a user wants to change some personal information:

<%
v_update = "UPDATE yadaTable1 SET name = '" & v_name & "'"
v_connection_object.Execute(v_update)
%>

Insert a record into a database when a new user is created:

<%
v_insert = "INSERT INTO yadaTable1 VALUES name = '" & v_name & "'"
v_connection_object.Execute(v_insert)%>
%>

Delete a record in a database when a user wants to be off the system:

<%
v_delete = "DELETE * FROM yadaTable1 WHERE id =" & v_id
v_connection_object.Execute(v_delete)%>
%>

...where v_name and v_id are variables that are active on the page, such as the users first name and their numeric login identifier.

Next: Name/Value Pairs in the URL >>
 

More ASP Articles
More By Ben Shepherd

Comments On: Beginning ASP and Microsoft Access

>>> Be the FIRST to comment on this article!