SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need help connecting to an Access DB

    Hey,

    First of all, I know absolutely nothing about connecting Access databases. However, I know a tad bit of VB.NET (my site is written in ASP, though). I need some help connecting to the DB. Here's the script I'm currently using (I removed the database, account name and site name for security):

    Code:
      <%@ Language=VBScript %>
      <% OPTION EXPLICIT %>
      <%
      Dim oConn
      
      on error resume next
      Set oConn = Server.CreateObject("ADODB.Connection")
      oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "D:\hshome\accountname\removed\removed.mdb"
      If Err.Number <> 0 Then
      %>
      <html>
      <head>
      <title>Error</title>
      <!-- #INCLUDE VIRTUAL="includes/style.css" -->
      </head>
      <body background="images/bg3.jpg" bgcolor="#000000" text="#C0C0C0">
      
     <p align="center"><br>&nbsp;<br>&nbsp;<br>&nbsp;<br>&nbsp;<br>&nbsp;<br><font class="x" color="#FF0000"><b>Error accessing Database.</font><br>&nbsp;<br><font class="l" color="#FF0000">It may be being updated.<br>&nbsp;<br>Please retry in a few minutes.</b></font></p>
      </body>
      </html>
      <%
      	Response.CacheControl = "no-cache"
      	Response.AddHeader "Pragma", "no-cache"
      	Response.Expires = -1
      	on error goto 0
      	Response.Flush
      	Response.End
      end if
      on error goto 0
      %>
    removed.mdb is in that directory. However, I'm getting the error message specified by the script. What exactly do I need to do to make this connection work? Please provide the exact code, as I don't know what I'm doing with Access connections.

    Thanks

  2. #2
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("PATH TO DATABASE")

    Replace PATH TO DATABASE with the relative route to the database. SO for example if it this page was index.asp and the database removed.mdb
    was in the subfolder removed then you would do the following.

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed/removed.mdb")
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wullie
    Try

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("PATH TO DATABASE")

    Replace PATH TO DATABASE with the relative route to the database. SO for example if it this page was index.asp and the database removed.mdb
    was in the subfolder removed then you would do the following.

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed/removed.mdb")
    oConn.asp is in the same folder as removed.mdb. So I have:

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")

    I'm still getting that error. Hmm... any more ideas? By the way, in my H-Sphere control panel, I've just enabled ODBC connections. It says this:

    Microsoft Paradox Driver (*.db ) Microsoft Access Driver (*.mdb) Microsoft Visual FoxPro Driver Microsoft dBase Driver (*.dbf) Microsoft Excel Driver (*.xls) SQL Server MySQL ODBC 3.51 Driver PostgreSQL

    Do I need to create a Microsoft Access driver?

    Thanks

  4. #4
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You shouldnt need to create an ODBC connection as you are specifying the connection in your .Open methods parameter.

    Try this. It asically opens a database, creates a recordset and closed them both. If it works then you know you can get a database connection and that error is in the code. If it doesnt work you know there is a problem connecting to a database on your server. NOTE: You will need to chang the SQL query to include one of your own tables.


    SET cn = server.createObject("ADODB.Connection")
    cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")

    SET rs = server.createObject("ADODB.Recordset")
    rs.open "SELECT * FROM tblName", cn

    response.write("Database open and recordset created")

    rs.close
    SET rs = NOTHING
    cn.close
    SET cn= NOTHING
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wullie
    You shouldnt need to create an ODBC connection as you are specifying the connection in your .Open methods parameter.

    Try this. It asically opens a database, creates a recordset and closed them both. If it works then you know you can get a database connection and that error is in the code. If it doesnt work you know there is a problem connecting to a database on your server. NOTE: You will need to chang the SQL query to include one of your own tables.


    SET cn = server.createObject("ADODB.Connection")
    cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")

    SET rs = server.createObject("ADODB.Recordset")
    rs.open "SELECT * FROM tblName", cn

    response.write("Database open and recordset created")

    rs.close
    SET rs = NOTHING
    cn.close
    SET cn= NOTHING
    I got:
    Database open and recordset created

  6. #6
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In that case you have successfully connected to the database and closed your connections again. Try implementing that database connection into your old code and see if it works. By the way, did this old script of yours ever work?
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  7. #7
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wullie
    In that case you have successfully connected to the database and closed your connections again. Try implementing that database connection into your old code and see if it works. By the way, did this old script of yours ever work?
    <%@ Language=VBScript %>
    <% OPTION EXPLICIT %>
    <%
    Dim oConn

    on error resume next
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")
    If Err.Number <> 0 Then
    %>
    Returns the same darn error message...

    Yes, it worked perfectly before when the site was hosting at brinkster.net. I simply changed the path in oConn.asp, hoping the DB would connect.

  8. #8
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, gimme 5 mins ill try mimic at my end
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  9. #9
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    works fine my end.

    <%@ Language=VBScript %>
    <% OPTION EXPLICIT %>
    <%

    dim cn, rs

    on error resume next


    SET cn = server.createObject("ADODB.Connection")
    cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")
    SET rs = server.createObject("ADODB.Recordset")
    rs.open "SELECT * FROM tblTest", cn
    response.write("Database open and recordset created")
    rs.close
    SET rs = NOTHING
    cn.close
    SET cn= NOTHING

    If Err.Number <> 0 Then
    %>
    <html>
    <head>
    <title>Error</title>
    </head>
    <body background="images/bg3.jpg" bgcolor="#000000" text="#C0C0C0">

    <p align="center"><br>&nbsp;<br>&nbsp;<br>&nbsp;<br>&nbsp;<br>&nbsp;<br><font class="x" color="#FF0000"><b>Error accessing Database.</font><br>&nbsp;<br><font class="l" color="#FF0000">It may be being updated.<br>&nbsp;<br>Please retry in a few minutes.</b></font></p>
    </body>
    </html>
    <%
    Response.CacheControl = "no-cache"
    Response.AddHeader "Pragma", "no-cache"
    Response.Expires = -1
    on error goto 0
    Response.Flush
    Response.End
    end if
    on error goto 0
    %>


    <!_____________________________________________________!>

    Try this and see what it says


    <%@ Language=VBScript %>
    <% OPTION EXPLICIT %>
    <%

    dim cn, rs

    on error resume next


    SET cn = server.createObject("ADODB.Connection")
    cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("removed.mdb")
    SET rs = server.createObject("ADODB.Recordset")
    rs.open "SELECT * FROM tblTest", cn
    response.write("Database open and recordset created")
    rs.close
    SET rs = NOTHING
    cn.close
    SET cn= NOTHING
    response.write(Err.Number)
    %>
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  10. #10
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get "databse open and recordset created" (only on oConn.asp), but none of the pages are actually connecting to the DB. I uploaded the db in Binary... is that the right way to do it?

    EDIT: Nevermind, there are two oConn.asp files. The one that is being called on the pages is in includes/. The database is /removed.mdb. I edited the includes/oConn.asp, but I'm getting the 'error accessing database' error.

  11. #11
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tell me a table and some of its fields names so I can write a little loop to see if data is being extracted for your database.

    While I do that you do a completely blank page and put on it

    <%
    response.write(err.number)
    %>
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  12. #12
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, I got it! Sorry. I was editing the wrong oConn.asp file (not sure why there were two!). Thanks so much for your time, Wullie. I would give you some rep points, but you have them disabled.

  13. #13
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do? i never even knew I could get rep points. Just glad u got it going, Gongrats
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  14. #14
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wullie
    I do? i never even knew I could get rep points. Just glad u got it going, Gongrats
    Yeah, now I just have to restore (well, not restore... more like hope I can find) all of the images for this site that's been sitting in my attic on my old HD for 3 years... lol... thanks again

  15. #15
    SitePoint Guru Wullie's Avatar
    Join Date
    Oct 2002
    Location
    Greenock, Scotland
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol, sounds like fun. Cheers for the rep points.

    Wullie
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •