DSN - Puzzling

Report · Aug 20, 2006

Not sure if it is me or them. I have a number of DSN's set up pointing at different Windows servers all over the world. I have recently set up a new DSN with a new server who says that the connection string that dreamweaver creates is wrong. I can connect locally, I have checked numerous times that the naming is the same and tried with and without the UID and password. I keep getting the same response.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/register_test.asp, line 113

Here is the string:

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_gwl_STRING
MM_gwl_STRING = "dsn=access_gwl;uid=hidden;pwd=hidden;"
%>

On their site they talk about file DSN's. Here are the instructions:

<%
Dim oConn, oRs
Dim qry, connectstr
Dim db_path
Dim db_dir
db_dir = Server.MapPath("access_db")
db_path = db_dir & "\yourdatabasefile.mdb"
fieldname = "your_field"
tablename = "your_table"

connectstr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & db_path

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "
"
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

%>

As a novice I do not understand why a local connection that works fails on the server when whatever I have done elsewhere works right away.

Thank you fto anyone responding

Report · Aug 21, 2006

Hi,
Im not sure if im reading you wrong,
But To me it seems like the server your hooking up to doesnt have a DSN set up for your database and you may have to set up as DSNless connection to the database using a connection string..and give your full path or use mappath

eg:
Dim MM_gwl_STRING
MM_gwl_STRING = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

HTH
Regards
Tag

Report · Aug 30, 2006

I am getting confused between System DNS and File DNS. The following code I am told will work. We have a DNS set up on the server.

What do I alter with the code below. Is the code a connection string?

Dim oConn, oRs
Dim qry, connectstr, sDSNDir
Dim db_name, db_username, db_userpassword
Dim db_server, dsn_name

dsn_name = "your_dsn_name"
fieldname = "your_fieldname"
tablename = "your_tablename"

sDSNDir = Server.MapPath("_dsn")

connectstr = "filedsn=" & sDSNDir & "" & dsn_name

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "
"
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

Or am I not getting anywhere near understanding what I a being told?

Thanks

Report · Aug 30, 2006

I am tired at the moment and can't be too bothered with reading, but from wht I have absorbed, it does seem as though you might be running without a DSN server because you are getting an OLE DB error. I would suggest using a Server.MapPath in your connection file to see if that helps. I have posted a few examples in the past on this forum, but if you can't find any, try looking on www.charon.co.uk. There are numerous tutorials there for all aspects of database integration from beginner to experienced.

Having just checked on Jules' site, I have reminded myself (or rather he has) that it is all to do with permissions too. have a look at http://www.charon.co.uk/content.aspx?CategoryID=27&ArticleID=56 and that should set you on the right track.

HTH

Mat