SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trying to open MS Access file remotely with ASP Classic from a different computer

    Hello guys

    I have two computers in a network running Windows Server 2003 R2 SP1. One computer is used as a webserver and the other computer has several files and used mainly for storage. On the webserver machine I have created a website. Users can log into the website through a page that is using ASP Classic to access an MS Access 2003 database. This database is in the same directory as the webpages. Users authenticate alright and they get redirected to another ASP Classic webpage that is trying to read data from a different database zkp.mdb, again in MS Access 2003 format. Now this second database is in the second computer, the one used for storing files. I am trying to use ODBC DSN to connect to this second database and it is not happening!

    here is the connection string code:

    Set Cn = Server.CreateObject("ADODB.Connection")
    Cn.Open "DSN=zkpdatabase"
    Set Rst = Server.CreateObject("ADODB.Recordset")
    Rst.Open "SELECT * FROM tbl_data order by DateOfCall DESC",Cn

    here is the 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.

    the line that has the error is Cn.Open "DSN=zkpdatabase"

    I tried moving zkp.mdb to the webserver so that it resides locally with all the webpages, changed the path accordingly for the ODBC DSN and it works fine! So my guess is that the connection string is working OK. None of these two databases have a password associated and I know that the two computers can see each other on the network. Also I need to mention that I have not installed MS Office on any of the two computers.

    Where could the problem be? I have spent half a day searching on Google and got literally exhausted. The first results direct me to Microsoft Knowledge base advising me to update my database drivers, however it says my version of Windows is up to date.

    I thought it could be the permissions of the directory of zkp.mbd on the second machine, but all permissions are given. Could this be an issue with a guest account? Do windows Server 2003 have that?

    Surely I can't be the only with this issue, I mean trying to access an .mdb file remotely using DSN.

    Any help would be really appreciated.

    -Mawgar

  2. #2
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I am going to upgrade to SQL Server. I turned the web upside down and reached to the conclusion that you need professional software to do this. A very interesting read is found here:

    http://cfpayne.wordpress.com/2006/07...l-server-2005/

    I am putting the following URL as helpful hint for others who might find it useful. My opinion is that connecting to a database on a network share with ODBC is a joke, even Microsoft knowledge base states it is a known problem hence the error 80004005 I am getting.

    A list of connection strings is found here, all possible ways gathered and categorised nicely for whoever needs them:
    http://www.carlprothman.net/Default.aspx?tabid=90

  3. #3
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @webber123456

    I found this webpage myself yesterday but wasn't of much help.

    I focused on the last item in the list, about Network Location. I used the local store computer name as it appears on the network for servername, and the letter used in My Computer for the particular directory I mapped on the web server (I did it through Tools->Map network drive) for the share name. I tried supplying the credentials of the administrator account for the local store computer as username and password. Simply this wasn't the way to do it as it didn't get me anywhere. Also I doubt if Microsoft.Jet.OLEDB.4.0 driver is any good for this sort of task. Which is why I moved to SQL Server.

    Thanks for your time.

  5. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally I managed to connect to my database on the remote SQL Server through ASP Classic.

    I installed SQL Server 2005 on both computers, the one used for local storage (back-end) and the web server (front-end).

    On the computer used for storage I made a database called zkp_database and inside that a table called zkp_users with one field lastname type char(20). I added some names in it. Then on the object explorer window I clicked the server name, expand security, then logins and added a new user with a password. I chose SQL Server authentication and ticked the option where the user should renew password on next login. Then I closed the SQL Server, re-started it, connected to my server and was prompted to type a new password. I got in the database I just created fine. So far so good there.

    On the web server computer I started SQL Server and in the registered servers window, right-clicked on database engine->new->server registration and found the SQL server on the back-end computer. I chose SQL Authentication and typed the username and password. Things went OK and I could manipulate the zkp_database through the SQL Server on the webserver.

    Now my original aim was to connect and manipulate the database on the web server through ASP Classic.

    I provide the code I used for the ASP Classic webpages as a sample. The hardest thing to find and setup was the connection string. The username and password are needed.

    <%
    Set Cn = Server.CreateObject("ADODB.Connection")
    set rs=server.CreateObject("adodb.recordset")
    Cn.Open "driver={SQL Server};server=dataone-06f8edd;Database=zkp_database;uid=john;pwd=2310425400;"
    strSQL = "select * from zkp_users"
    rs.open strSQL, Cn
    Do While Not rs.EOF
    response.write (rs.Fields.Item(0) & "<br>")
    rs.movenext
    loop
    Cn.Close
    %>

    Hope this will be useful to someone else, this post already got like 100+ reads.

    -Mawgar


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •