• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Connecting to MS Access 2000 DB from ASP

I am trying to connect to an MS Access DB from ASP and have a strange situation!

My connection string is as follows:
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Path to MDB>;"

I have tried the following connection strings as well, just to make sure I connect properly:
objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=<Path to MDB;" AND
objConn.Open "FILEDSN=<Path to DSN connection file"
The result is always the same.

After executing this statement, I check the state of the connection object. The value of
objConn.State is 1, which means the connection is open. Still, I get the following error:
Error Number: 3001
Error Description: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Error Source: ADODB.Connection

To further test the connection, I create a recordset object and use this connection to execute a SQL statement. The statement executes correctly and brings back results too! Now, if I check the Err object, it still gives the same error as above.

Any ideas what the error could be for and how to correct it?      
2 Solutions
probably the error object is not cleared from a previous error. try reseting the error object using Err.Clear before you try to create the new connection and then check if err.description is still set to the same value. Hope you are not using on error resume next?

Have Fun!
If you are using On error resume next, you can check after the connection opens for the error.  I put the sql statement into a string to it can be reported to the error page for debugging  purposes.  This is handy especially if you build them on the fly.

Here's an example:
On Error Resume next

Dim DSNtemp
Dim sqlstmt
Set objConn = server.CreateObject("adodb.connection")

DSNTemp = "DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;"
DSNTemp = DSNTemp & "DBQ=" & server.MapPath("yourDatabase.mdb")
objConn.Open DSNtemp

if err.number <> 0 then
    Response.Write("An error has occured opening the connection.<br><br>" & vbcrlf)
    Response.Write("Error Number:" & err.number & "<br>" & vbcrlf)
    Response.Write("Error Description:" & err.description & "<br>" & vbcrlf)
end if

Set rs = Server.CreateObject("ADODB.Recordset")

sqlstmt = "SELECT * from YOUR_TABLE"
Set rs = conn.execute(sqlstmt)

if err.number <> 0 then
    Response.Write("An error has occured executing a SQL statement.<br><br>" & vbcrlf)
    Response.Write("Error Number:" & err.number & "<br>" & vbcrlf)
    Response.Write("Error Description:" & err.description & "<br>" & vbcrlf)
    Response.Write("Extra Info:  " & sqlstmt)
end if

sorabharoraAuthor Commented:
Hi Guys

Thanks for your responses. I was out of office yesterday, so saw the comments just now. I did have a On Error Resume Next statement in my code. I removed that and called the page to test. It gave me a more helpful error with the line number.
The problem was that I was trying to set the CursorLocation property for the Connection object. I guess that is not valid for Access databases. I commented that out and the problem got solved.

Thanks for giving me the right pointer to look at!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now