[SOLVED] Connect to Access database remotely

Issue

I am trying to connect to my access databse that I have hosted with godaddy. I have ASP 3.5, Php 5.2, and IIS 7. I have gone and set up a virtual directory in my IIS settings for the vb script. I am using Microsoft Visual Web Developer 2008. Could anyone please let me know if they see something wrong with my connection string to the remote database? I really need to use Access for this. Thanks

The code is similar to a tutorial I followed. Tutorial (it is in Spanish but code works locally not remotely)

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
        Dim objConn As Object
        Dim objRecords As Object
        Dim strConn As String
        Dim strQuery As String

        objConn = Server.CreateObject("ADODB.Connection")
        strConn = "Provider=MS Remote;" & "Remote Server=http://(IP Address here);" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/logs/App_Data/users.mdb") & "Uid=admin" & "Pwd="
        objConn.Open(strConn)

        strQuery = "SELECT * FROM tbl_usuarios"
        strQuery = strQuery + " Where str_usuario_ide='" + Login1.UserName + "'"
        strQuery = strQuery + " And   str_usuario_cve='" + Login1.Password + "'"

        objRecords = objConn.Execute(strQuery)
        If (Not objRecords.BOF) Then
            e.Authenticated = True
        Else
            e.Authenticated = False
        End If

        objRecords.Close()
        objConn.Close()
        objRecords = Nothing
        objConn = Nothing
    End Sub
End Class

Solution

You cannot connect to an MS Access database residing on a server unless you use a full UNC path name. That means a NON http:// technology

http: is a GALACTIC different kind of technology than the Windows networking system. To correctly open an Excel file, Word file or in this case an Access file you cannot use ftp, or something like http:

You need to use Windows networking. That means you need to setup a VPN and ALSO you need to be running WINDOWS networking (not Linux, and not Apple OS on that server). In the case of Linux, you could hope and "require" that SAMBA is installed to get you the standard windows networking system. (so for a windows computer to open files on a Linux computer, you need a "Windows compatible" networking system installed.

So a connection string to connect to a standard mdb or accDB file is a FULL QUALIFIED windows path name. The path name you supply MUST be a standard Windows path name, and MUST be based on a standard Windows networking system. So if you setup a VPN and THEN you can browse the files in that folder via Windows networking (not http and not FTP), then the standard Windows locking and opening mechanics will work and are required for opening of a standard Windows file sitting in a folder. So you IN ALL CASES Still require a folder on your local hard drive or one that mapped from Network Neighborhood. And as noted, the networking system in place here has to be Windows networking. I do not believe that GoDaddy has for basic plans the ability for a VPN and direct file use and manipulation of basic files sitting on their servers (you have to use ftp to place files on their servers – not a shared Windows networking folder). And MORE important such direct file use in the case of mdb/accDB files MUST be Windows networking.

I have read that GoDaddy does allow external connections to their database server options. So MySql or possible SQLServer for Windows package. However the caveat here is that by default for MOST plans they do not allow the wild internet to external connect to their internal database servers. In fact in any plan where they do allow such connections then they "move" the hosted website to another set of servers so that if at least compromised by allowing the wild world of the internet to "connect’ to these internal database servers then not all servers will be affected or compromised by such a security breach.

I would thus suggest you use a true database server. I see little (if any) reason to attempt to use a mdb file sitting on a web server anyway.

So SQLServer or MySql can accept a socket connection and can do so over ANY network that supports TC/IP. However, a MS Access "file" does not accept such connections. So no http, or ftp allowed. Remember an "mdb" or "accDB" file is simply a file sitting on the hard drive.

As such when you use Excel, Power-point, AUTO CAD, or a simple Access file, then no such "socket" connection is possible. So you can no more use a connection string to Power-Point then you can to a simple Windows file sitting on the web server hard drive that just happens to have an mdb or accDB extension.

A file is a file is a file. A horse is a horse is a horse. So you are talking about a plain Jane windows file sitting in a folder on that server.

If you going to use a Windows program (such as PowerPoint or in this case MS Access) to open and modify that file, then you need to open that file using standard Windows networking, and not some type of web based connection system.

So no connection string is possible to open a plane Jane Windows file, be it your favorite Paint program or simply an Access file.

Of course if the software you are building is running server side and the "JET" engine is correctly installed on that server (and it must be), then you can write some vb.net or any kind of code that has a standard oleDB connection string. So for server side code, you most certainly can use a connection string, but if you look CLOSE you will ALWAYS be using a full qualified Windows path name and as noted you have to be running a Windows compatible server with the JET database engine installed.

Answered By – Albert D. Kallal

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *