[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Connecting to a DB across computers

Hello,

I have been writing a job management system for work and have come across a bit of a problem.

The job management system is located on a computer called webserver which as the name suggests is the IIS webserver. I need an ASP script located on the webserver to connect to an access database on another server (called costing-server).

So first of all i have tried the obvious (but I don't know if this is the best way). I have mapped a network drive to the resource I want to use on the costing-server. This mapped drive is now designated as M on the webserver. If I create a DSN entry to the DB using this path:

<%
dim dsn
dim Conn
dsn="DBQ=" & Server.Mappath("M:/Sage Job Costing/DEMODATA.001/demodata.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn
%>

it doesn't work. if I use this path however:

<%
dim dsn
dim Conn
dsn="DBQ=" & Server.Mappath("192.168.16.4/Sage Job Costing/DEMODATA.001/demodata.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open dsn
%>

It does but I get the following error:

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 0x4f8 Thread 0x8b0 DBC 0x2166dbc Jet'.

/dsnSage.asp, line 6


So I have been onto google and searched for the error and found this:

http://www.attention-to-details.com/newslog/38n-temporary-volatile-jet-dsn-for-process.asp 

The person who wrote the article suggests the this error is due to permissions. I have checked the permissions for the costing server and there is full write permissions set on the directory holding the db.

Can anyone suggest another way of doing this? Or am I on the right track and have missed something obvious?

Thanks in advance

Chris

0
chrissp26
Asked:
chrissp26
  • 4
  • 3
  • 2
  • +1
1 Solution
 
fozyletCommented:
Am not really convinced that it is a good idea to do this (i.e. let the web server access n/w files)
However, let us get this working if thatz ur only go.

You need a network user to run the IIS (at least the page trying to connect to the mdb)
For this...

Browse to the directory where the asp file which connects to mdb is located (or root if many files connect)
  > Right-click to get 'Properties' form
    > Select 'Directory Security' tab
      > Click 'Edit' button under 'Anonymous access & authentication control' frame
        > Click 'Edit' button under 'Anonymous Access' frame. Make sure 'Anonymous Access' IS checked.
          > Change the Username and Password here to a n/w user having access to the directory where the mdb file is on the config server.


Guess that should do the trick.
0
 
chrissp26Author Commented:
Hello, thanks for the quick response.

The webserver is running Windows 2000 Advanced server. The information you give doesn't match what I see on the screen.
When I right click on the folder that holkds the ASP file and select properties , the tab is just called security then I have a field named Name and then Permissions. In there, there is no 'Anonymous access & authentication control' frame?

What am I doing wrong?

Thanks again

Chris
0
 
ajaikumarrCommented:
Hai,

Best method is using DSN based connection.

Bye
Ajai
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
fozyletCommented:
What I am mentioning is in the IIS management console, not on the file system...
0
 
donelCommented:
why not you lunch your web page on the sample server of db?


donel
0
 
chrissp26Author Commented:
I think that is working now. But I get the error that the path is wrong.

If I use the following path:

m:/Sage/Sage Job Costing/DEMODATA.001/demodata.mdb

I get the following error.

Server.MapPath() error 'ASP 0172 : 80004005'

Invalid Path

/dsnSage.asp, line 4

The Path parameter for the MapPath method must be a virtual path. A physical path was used.

If I use this path:

costing-server/Sage/Sage Job Costing/DEMODATA.001/demodata.mdb

or this path:

192.168.16.4/Sage/Sage Job Costing/DEMODATA.001/demodata.mdb

I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

/dsnSage.asp, line 6

Do you know how I should write the path out?

Thanks

Chris
0
 
fozyletCommented:
You don't need the Server.MapPath call. That function is to convert virtual paths to physical ones. If you're already giving a physical path, don't use the function - give the path directly.

Another way would be to add a virtual directory (say 'db' at root) mapping to the path m:/Sage/Sage Job Costing/DEMODATA.001/ and then
Server.MapPath("/db/demodata.mdb")
0
 
ajaikumarrCommented:
Hai,

Try like this

dsn="DBQ=\\ServerName\ShareName\Sage Job Costing\DEMODATA.001\demodata.mdb; Driver={Microsoft Access Driver (*.mdb)};"


ServerName => is the name of your system which database resides.
ShareName => Share mapped for your database path.
Bye
Ajai
0
 
chrissp26Author Commented:
Thank you all for your help and suggestions.

fozylet you truely are an expert and a credit to your profession. Thanks for your help.
0
 
fozyletCommented:
Thanks for the good words Chris! :)
glad to have helped...

::fozylet
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now