database connectivity error message 1

[MikeT]

The connectivity between my access database and asp pages was working fine for about a month. Now, I get this error message. The folder the database is in was renamed and now I get this error message. The folder has the same permissions as the old folder, and the db is not open at all. I tried removing and adding the DSN twice. Any ideas? I plan to upgrade to MDAC 2.6 shortly.

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

 

[link9]

What is your connection string??? Please post up a code frag so we can take a peek.

Please make a note of the line number where the error is occuring, and mark that on the post.

 

[MikeT]

Sure, here you go:
**********************************************
set rs = server.createobject("adodb.recordset&quot
sql="SELECT [Office City] FROM Offices WHERE OfficeID = "&varOfficeID

rs.open sql, "DSN=agentact"
strOffice=rs("Office City&quot
rs.close
***********************************************
The error is on the 4th line. The error message has changed though:
***********************************************
Provider error '80004005'
Unspecified error
/Admin Tools/salereview.asp, line 14
***********************************************
Disregard "line 14", the code has been pasted and abridged.
I tried this using a DSN-less connection and it worked like a charm.
I am using MDAC 2.5, and am about to install the latest service pack for it; I lack the 'nads to upgrade to 2.6 becuase of sensitive data on the server, which has been acting up lately.

Thanks for your time!

 

[link9]

You *needs* yourself a connection --

set con = server.createobject("adodb.connection&quot
con.open ("agentact&quot
set rs = server.createobject("adodb.recordset&quot
sql="SELECT [Office City] FROM Offices WHERE OfficeID = "&varOfficeID

rs.open sql, con
strOffice=rs("Office City&quot

set rs = nothing
set con = nothing

See if that doesn't clear it up

Paul Prewett

 

[MikeT]

OK....I updated the MDAC and everything is peachy!
Now my question to you is:
Why was it working without the connection method you proposed? I tried your method for fun, and it worked (I expected it to), but I don't understand what the difference is.
Is there an advantage to one over the other?

Thanks; I owe you!

 

[link9]

I have no idea... but the important thing is that it's working now...

I have never seen a recordset successfully opened without a connection.

Maybe someone else can shed some light on that subject.

 

[MikeT]

I have to create a connection whenever I want to update/add info to the database, but if I'm only retrieving info the connection method originally described above always works fine for me.

 

[link9]

Actually, now that I think about it... you said that it was working if you specified the DSNless connection string up there with the recordset.open method--

I guess that actually makes sense... You are just passing all the variables it needs to create the connection, whereas maybe if you are using the DSN, you have to actually assign that to a connection variable.

Interesting... I suppose ADO treats them differently.

 

[kor]

When you create a RecordSet object without a Connection object, a new Connection object is implicitly created for that one use. My original theory was that problem occured because he didn't specify the ODBC provider type in the connection string with the Data Source Name. But if that were true, it should still not be working.

There are very few syntax changes between ADO 2.5 and 2.6, so I can't see why the upgrade would fix your problem. Must have been a behind-the-scenes problem with your DNS.

 

[MikeT]

I didn't upgrade to 2.6, only applied the latest service pack to 2.5. Also, as far as not specifying the ODBC provider type.....isn't that what the DSN is there for in the first place? Given the performance gains of DSNless connections, I'm considering going that route from now on,
are there any drawbacks to DSNless connections (other than database info in the source code)?