Error in DSN-less connection

[Sensibilium]

I'm trying to create a DSN-less connection to my Access Database as below:

[tt]Set oConn=Server.CreateObject("ADODB.connection&quot
Set rsProdType=Server.CreateObject("ADODB.Recordset&quot
strSource = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\

http://wwwroot\FairmontDB\fairbd\fairprod.mdb;

UserID=User"
oConn.ConnectionString = strSource
oConn.Open
Set rsProdType=Server.CreateObject("ADODB.Recordset&quot
rsProdType.Open "SELECT * FROM tblProductTypes", oConn[/tt]

An error occurs when it gets to the emboldened line above, this is the error:

[tt]Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'tblProductTypes'. [/tt]

There is no password for 'User', and all the correct user permissions are set up within the database.

Any ideas?

 

[RobSchultz]

Your connection string doesn't appear to be quite right. Try this...

strSource = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\

http://wwwroot\FairmontDB\fairbd\fairprod.mdb;Uid=User;Pwd=;"

Check out...

http://www.able-consulting.com/ADO_Faq.htm

for a very good article on DSN / DSN-less connections.

Rob [sig][/sig]

 

[Sensibilium]

Thanks Rob!

I have actually now connected using OLEDB, the solution to which I found on the FAQ page you mentioned.

And it works!!! )

Many thanks once more. [sig]<p>ahdkaw<br><a href=mailto:ahdkaw@ots.zzn.com>ahdkaw@ots.zzn.com</a><br><a href=

http://sensibilium.com/>Sensibilium</a><br>'If

anythign can go wrong, it will'[/sig]

 

[scroce]

after reading this posting, I can't discern any difference in the code that was posted by ahdkaw and the code in the response by RobSchultz other then spacing and captialization. I'm having the exact same trouble as ahdkaw, but even after analyzing the responses here, I can't see how there is any real difference between what I have on my asp page and what is written here. Any comments? How much water would there be in the ocean if it weren't for sponges?

 

[scroce]

never mind, i found it,(UserID vs. uid) but I'm still having the same problem. once again I ask, anyone care to comment? How much water would there be in the ocean if it weren't for sponges?

 

[vasah20]

scroce -
from experience, when you do a DSN-less connection with Driver={Microsoft Access (*.mdb)} it needs to be exactly correct. I have had spacing off before, and capitalization off too, and I couldn't get it to work just because of that.

Personally, I recommend going the OLEDB route, because you get to skip the ODBC Layer (which I've read can be a very flaky connection at times) and use the OLE DB source. Why use the middleman when you get go straight to the manufacturer?

Ok that's enough from me, time to get back to school...
leo

 

[scroce]

in this example, how would the OLEDB code look as compared to the code that utilizes the ODBC layer?

I'm still rather new at ASP and data connectivity, and the difference between these two are still a little unclear to me. How much water would there be in the ocean if it weren't for sponges?

 

[jonscott8]

The only difference is in the connection string:

strSource=&quot;Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=User;Data Source=C:\Inetpub\

http://wwwroot\FairmontDB\fairbd\fairprod.mdb;Persist

Security Info=True

See also:
FILE: MDACCON.EXE Using Connection Strings with ODBC/OLEDB/ADO/RDS

http://support.microsoft.com/support/kb/articles/Q193/3/32.ASP

Jon Hawkins

 

[scroce]

..the idea being that i would use this (OLEDB)to connect directly to anything that uses a microsoft jet database as opposed to going through ODBC which is a third party extra step.

In other words, it's using microsoft's connection parameters directly with no need for translation,(which is what ODBC does) and is therefore supposed to be easier and eliminates that step. But if you were not using a db built with OLEDB, (like Sybase or something possibly), then you would have to use ODBC - Is this correct reasoning or am i mucking it all up?

btw, thank you for you responses and for the links. They are very helpful. How much water would there be in the ocean if it weren't for sponges?