Need help connecting to an Access DB

**skyraider** · Apr 25, 2005 14:12

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

**Wullie** · Apr 25, 2005 14:47

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")

**skyraider** · Apr 25, 2005 14:51

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

**Wullie** · Apr 25, 2005 15:18

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

**skyraider** · Apr 25, 2005 15:26

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

**Wullie** · Apr 25, 2005 15:29

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?

**skyraider** · Apr 25, 2005 15:31

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.

**Wullie** · Apr 25, 2005 15:35

Ok, gimme 5 mins ill try mimic at my end

**Wullie** · Apr 25, 2005 15:50

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">

           Error accessing Database.   It may be being updated.   Please retry in a few minutes.
</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)
%>

**skyraider** · Apr 25, 2005 15:55

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.

**Wullie** · Apr 25, 2005 16:00

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)
%>

**skyraider** · Apr 25, 2005 16:00

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.

**Wullie** · Apr 25, 2005 16:01

I do? i never even knew I could get rep points. Just glad u got it going, Gongrats

**skyraider** · Apr 25, 2005 16:19

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

**Wullie** · Apr 25, 2005 16:27

lol, sounds like fun. Cheers for the rep points.

Wullie

User Tag List

Thread: Need help connecting to an Access DB

Thread Tools

Display

Need help connecting to an Access DB

Bookmarks

Bookmarks

Posting Permissions