DSN-less connection help

DSN-less connection string example for ASP

[SpyderWeb]

Hello,

Have:
MS Access 2003
ASP
Shared host

Need:
DSN-less connection string example for ASP

Thanks!

[ziggystardust]

Hello,

try this:

strconn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("yourdb.mdb") 
set conn = server.createobject("adodb.connection") 
conn.open strconn 

//ZS

[defanjos]

You can also try this:

Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("dbname.mdb") & ";")

JET.OLEDB is supposed to be faster

[SpyderWeb]

No luck yet. In my code below I get the line 27 error at Connection.Open(sConnString). Any idea what’s wrong?

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x760 Thread 0x8dc DBC 0x99d38dc Jet'.
/test.asp, line 27

<%
'declare your variables
Dim Connection, sConnString,sql,recordset

'declare SQL statement that will query the database
sql = "SELECT * FROM mytable"

'create ADO connection and recordset object
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database driver and the location of the database
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("mydatabase.mdb") & ";"

'Open the connection to the database
Connection.Open(sConnString)

'Open the recordset object executing the SQL statement and returning the records
Recordset.Open sql,connection

'Lets loop through the records until we come to the end
Do while not recordset.eof
response.write recordset("symbol") & "<br>"
'move on to the next record
recordset.movenext
Loop
'We are done so lets close the connection and the recordset
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>

[defanjos]

Are you testing this in your own computer?
Are you running Win 2000 pro or XPpro?
If so, and if you open the Access DB to view or make changes, this could be the problem.

The only way I found to fix it, was to create another folder, for example "database1", copy the DB into it, than delete the folder were the DB was before. Now rename "database1" to "database" - assuming this is the name of the original folder.
Now go to Windows Explorer, find the folder, right click on it, click properties, then security tab > click Everyone > check full control > Apply

If anybody else knows an easier way, I'd love to know it.

[SpyderWeb]

Found the problem.
Changed
Server.MapPath("mydatabase.mdb")
to
Server.MapPath("/myfolder/mydatabase.mdb")
and it now works.

Thanks

[SpyderWeb]

I got defanjos faster way to work too:

Change:
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("/myfolder/mydatabase.mdb") & ";"

To:
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\myfolder\mydatabase.mdb")