MS Access Connection Issues

[PattyO]

Good day,

I'm having a go at my first website for a client and it involves a few dynamic
pages.

I've gone to the ISP and subscribed to a windows hosting account with ASP
support. Well, I've enabled Access database support and created my first
database through their site. (distributors.mdb Usedname=myUsername,
Password=myPassword) I also accepted the option of creating a file DSN
(access_distributors.dsn) for,as they indicate on their web page, "simple
reference."

I've also arranged the directory structure on my local and testing server
machine (they are one in the same) to replicate the structure on the ISP's
server thinking there would be minimal code tweaking to get it working when I
upload everything. The ISP indicates the DSN will be stored in a sub-directory
(_dsn) that cannot be viewed by anonymous Web users. Furthermore, they indicate
that the database will also be stored in a sub-directory (access_db)

The problem...I can open the ODBC Administrator and create a System DSN,
create a connection using the DSN I've just created that appears in the
drop-down box and have a dynamic page in minutes. But using a file DSN appears
to be a different story altogether.

I've opened the ODBC Administrator and created the file DSN and stored it in
the "_dsn" directory. However, when I attempt to create a connection in
Dreamweaver via the databases tab and select the "by DSN" method, the file DSN
I've created appears nowhere. Therefore, I've been attempting to use the
connection string method.

I'm really struggling with syntax but, more importantly, with the whole
concept of the connection string and how it fits into the larger picture. I've
done a ton of reading through various sources and made numerous attempts at
writing a connection string but seem to be getting nowhere.

Most attempts yield a "[Microsoft] [ODBC Driver Manager]Data source not found
and no default driver specified" message. Several of my latest attempts have
been:


"filedsn=C:\inetpub\wwwroot\vbx\_dsn\access_distri butors.dsn;DBQ=C:\inetpub\wwwr
oot\vbx\access_db\distributors.mdb;UID=Admin;"

and

"Driver={Microsoft Access Driver
(*.mdb)};DBQ=C:\inetpub\wwwroot\vbx\access_db\dist ributors.mdb"

These strings indicate "Connection attempt successful" when I press the test
button but when I expand them in the database tab the only listing under tables
is "None. The same goes for Views and Stored Procedures.

At one point LATE last evening I entered a string (I can't remember it or my
reasoning behing it) and it acctually worked. But the tables/queries
disappeared shortly thereafter (did I mention it was late and I was
exhausted???) In a related note I opened the Event Viewer after the failure and
noticed a Failure Audit on the Securitytab. The details were:

Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 529
Date: 5/22/2005
Time: 1:31:02 AM
User: NT AUTHORITY\SYSTEM
Computer: MY_COMPUTER_NAME
Description:
Logon Failure:
Reason: Unknown user name or bad password
User Name: MY_COMPUTER_NAME
Domain: MY_COMPUTER_NAME
Logon Type: 3
Logon Process: IIS
Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Workstation Name: MY_COMPUTER_NAME

For more information, see Help and Support

Center at [url]http://go.microsoft.com/fwlink/events.asp[/url].

Could this be related?? I think so but don't know what to do about it.

Finally, the ISP provided me with Access database connection scripts for both
file DSN and DSN-less connections but I've been unable to make a decent and
reliable connection thus far. They are as follows:

File DSN Script-
<%
Dim oConn, oRS
Dim qry, connectstr, sDNSDir
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(fielsname) & ": " & oRS.fields(fieldname & "<br>"
oRS.movenext
wend
oRS.close
end if

Set oRS = nothing
Set oConn = nothing

%>

DSN-less Script-
<%
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 = "yourfield"
tablename = "yourtable"

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(fielsname) & ": " & oRS.fields(fieldname & "<br>"
oRS.movenext
wend
oRS.close
end if

Set oRS = nothing
Set oConn = nothing

%>

Any and all help would be deeply appreciated!!

Thanks in advance,

Pat

[UltraDav]

is there a reason why u want to use a file dsn?

a system dsn is what the server will use. and as u've mentioned that works fine.

[PattyO]

Hi,

Believe me, I'd rather not but it's the only DSN the ISP offers. Don't ask me why.

Pat

[PattyO]

Hi,

I'm trying to establish a connection to an Access database on my local
machine. The ISP uses a file DSN (which I've created locally too via the ODBC
Administrator), but I've noticed that Dreamweaver MX doesn't really support
this option regarding listing them in dropdown boxes, etc.

So, I've turned to creating a custom connection string. When I click the
"test" button I am greeted with a successful connection alert box. I click OK
to save the connection and the connection is listed on the database tab.
However, when I expand that view and then click on and expand either Tables,
Views, or Stored Procedures, the only listing for objects is None.

I can assure you that I have created the tables in the database and have
created a few queries as well.

Is there a reason why Dreamweaver indicates a successful connection but won't
list the contents of the database?

For reference, I'm using Dreamweaver MX on an XP box with IIS installed. As
fas as site configuration, both the local and testing server sites are the same
(C:\inetpub\wwwroot\)

Any help appreciated,

Pat

[Clayton Lee]

I am also in the process of adapting an Access DB with DW. For the DSN, you
must create a system DSN, not a local DSN.

That said, I'm still having problems getting my testing server to operate.

I am using XP Professional, Access 2003, and DW 7.0.1.

I followed the tech note on how to set the permissions to the database, by
giving the anonymous account IUSR_computername full access. It doesn?t
actually show up as IUSR_computer name, but rather as ANONYMOUS LOGON. I?ve
also allowed sharing for the folder. I included Everyone, as well, again with
full access.

I tried the above on the individual mdb file, as well as the folder for the
site, and even the folder Inetpub. Likewise, the c:\temp folder. I've since
given all accounts full access, to no avail. Perhaps DW is looking for the
IUSR_computername explicitly, and I shall pursue that soon.

The message I get when trying to use live data is HTTP Error 404 (file not
found) saying that the files with the asp extension aren?t being executed, or
that my web folder does not map to the url on my testing server. And do I want
to change the location of the testing server?

I think I have the various boxes in the site done correctly. They are?
Local info:
Site name: xxxServices
Local root folder: C:\Inetpub\xxxServices\
Refresh local file list automatically is checked
Default image folder: C:\Inetpub\xxServices\images
HTTP address: C:\Inetpub\xxxServices\
Cache is checked

Remote info:
Access: FTP
FTP host: xxxservices.com
Host directory: wwwRoot/
Login: xxx
Password: xxx
Use passive FTP is checked

Testing server:
Server model: ASP VBScript (have also tried ASP JavaScript)
Access: Local/Network
Testing server folder: C:\Inetpub\wwwroot\xxxServices\
Refresh remote file list automatically is checked

Site map layout:
Home page: C:\Inetpub\wwwroot\xxxServices\

My DSN is a system DSN, as creating a user DSN does not show up in the DW list
to select when setting up a connection. Yet I tell DW to connect using local
DSN. The test button says everything is fine.

In creating a recordset, there?s my database, I create a query, and on testing
it, I see that I want. It?s testing with live data button that fails. I?m
thinking that early on, I actually had it display a page with my fields filled
in. But that was early on.

I?m at a standstill. My Dreamweaer for Dummies book and my 50 Neat Projects
book don?t mention any of these permission issues, and everything seemed so
straightforward up to this point. I can?t even make their sample databases
work.

My wwwroot folder has a whole host of asp files that were created, but not
necessarily on my last attempt(s). I shall do some file managing, and know a
little bit more after another session of banging my head on the wall trying to
get this to work.

Alas, if anyone is seeing my obvious error and wants to make my day - please
let me know and I shall name my next little tax exemption after you.

Clayton