MsAccess connection string doesn't work

[Judy]

I am having trouble switching from the 'Driver={Microsoft Access Driver... form of the database connection string to the more reliable 'Provider=Microsoft.jet.oleDB.4.0... form. I would greatly appreciate help! Judy

This works:
strconn = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' & Server.MapPath('\_database\mydb.mdb')
set dbconn = server.createobject('adodb.connection')
dbconn.open strconn
strsql = 'Select * from TableName '
set rst = Server.CreateObject('ADODB.Recordset')
rst.open strsql, dbconn, adOpenStatic, adLockReadOnly, adCmdText

This doesn't work, producing an error '80004005' on the recordset open statement:
strconn = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & Server.MapPath('\_database\mydb.mdb')
set dbconn = server.createobject('adodb.connection')
dbconn.open strconn
strsql = 'Select * from TableName '
set rst = Server.CreateObject('ADODB.Recordset')
rst.open strsql, dbconn, adOpenStatic, adLockReadOnly, adCmdText

[bruce]

can you post the complete error please?

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[Judy]

The error in its entirety:
-------------
error '80004005'
/dbtest/test2.asp, line 17
---------------

Line 17 is the line: rst.open strsql, dbconn, adOpenStatic, adLockReadOnly, adCmdText

It also gives an error on an SQL insert command, but I think it is a different error number.

Judy

[Judy]

Thank you for attempting to help me. I thought at first you were on to something...maybe the two opens used the parameters differently, but I can't get any form of a statement or execute to work. In answer to your specific questions, yes, the db constant file is included. It has values shown below related to the open statement parameters. I tried various combinations of them, I tried without the command type parameter, which you said worked for you, and I tried without any parameters, letting them all default. All the combinations worked with the other form of connection string. None of the combinations worked with the Provider=Microsoft.jet.OLEDB.4.0. Also, a direct execute as in: dbconn.execute strsql where strsql is a simple 'Select * from TableName' produces error '80004005' as well.

I am wondering if the rights on my discountasp.net _database folder are not set right? But if that were true, how would it be working for the 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' & Server.MapPath('\_database\mydb.mdb') statement?

Interestingly, tech support at discountasp.net sent me a sample page with logic that does work...it opens the database schema and displays the tables. Here is their logic:
Session('DSN') = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & Server.MapPath('/_database/mydb.mdb')
Set DBConn = Server.CreateObject('ADODB.Connection')
DBConn.Open Session('DSN')
Set rstSchema = DBConn.OpenSchema(20)

It defies my experience, and the experience of my associate, who has much more than I do, as to why this does not work!

Here's the values from ADOVBS.Inc, the include:
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

[bruce]

Several questions

1) What is adCmdText set to?

2) did you include the db constant file? ie. does adOpenStatic, adLockReadOnly have any value

I tested the code w/o specifying adCmdText and it worked fine.

strconn = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & Server.MapPath('\_database\mydb.mdb')
set dbconn = server.createobject('adodb.connection')
dbconn.open strconn
strsql = 'Select * from TableName '
set rst = Server.CreateObject('ADODB.Recordset')
rst.open strsql, dbconn, adOpenStatic, adLockReadOnly

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[bruce]

I tested the code w/ adCmdText on my test account, the code works fine.

I doubt that there's any problem w/ the directory setting but it doesn't hurt to try put the DB somewhere else and test.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)