Knowledgebase
Knowledgebase
How to make a connection to the MS Access database using Vbscript?
Posted by Robert Rolls on 02 April 2007 02:25 AM

This article describes how to use databases in Active server pages using ADO. This article assumes that you already have some working knowledge of Microsoft Access® and VBScript.

  • The ADODB.Connection object opens up an ODBC or OLEDB connection to a database through database drivers so you can do something with the database. You use it by first creating an instance of the object. Eg: <%
    Set conn = Server.CreateObject("ADODB.Connection")
    %>
  • When you are finished up with the connection it is a good idea to clean up. Eg: <%
    conn.close
    set conn = Nothing
    %>

The next step is to open up a database. You can do this by either using a Data Source Name (DSN) or a connection string. Most commonly, developers use connection strings because a DSN requires setup by the administrator of the server. A DSN is really just a shortcut name for a connection string. But with a connection string, you can move your database around without having to change the DSN.

  • There are two connection strings. One uses ODBC, the other uses OLEDB. We will use OLEDB in this example: <%
    Dim connStr
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
    Server.MapPath("mydata.mdb")
    %>
    Please note: the mydata.mdb should be replaced with the name of your MS Access® database.
  • When using a password protected database, do this: <%
    Dim connStr
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
    Server.MapPath("mydata.mdb") & "PWD=yourpass"
    %>
  • If you want to use a DSN:
    • Without a password <%
      Dim connStr
      connStr = "DSN=mydsn"
      %>
    • With a password <%
      Dim connStr
      connStr ="DSN=mydsn;UID=username;PWD=password"
      %>
  • And finally, you have to use the connection string to open up a connection to the database by using the open property: <%
    Dim connStr
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
    Server.MapPath("mydata.mdb") & "PWD=yourpass"

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.open connStr
    %>
  • So, if you have a table called table1 in your database with nm1 and phn1 as table column names, then you can use following code as an example to get some information out: <%
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.open connStr
    Set rs = objConn.execute("SELECT * FROM table1;")
    DO WHILE NOT rs.EOF
    Response.Write "NAME:" & rs(nm1) & "<BR>"
    Response.Write "PHONE: & rs(phn1) & "<BR>"
    LOOP
    %>
(530 vote(s))
Helpful
Not helpful