asked on

ASP.net 4 Connecting to remote Access database on Server with IIS

Hi

I am trying to use the following ASP.net VB code to connect to a remote Access database that
resides on a LAN Server. I am getting the error ADODB.Connection not defined. I have never done this before so not sure whether if I am on the right track

Dim adoConn As ADODB.Connection
    Dim adoRst As ADODB.Recordset


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim strConString As String
        Dim strSQL As String

        'assign connection string
        strConString = "Provider=MS Remote;" & _
                       "Remote Server=http://192.168.1.1;" & _
                       "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=MyRemoteDB;Persist Security Info=False"

        'initialize connection object variable
        adoConn = New ADODB.Connection
        'open connection
        adoConn.Open(strConString, "admin", "")

        strSQL = "Select * from Orders"

        'initialize recordset object variable
        adoRst = New ADODB.Recordset
        With adoRst
            .Open(strSQL, adoConn, , , adCmdText)
            If Not .EOF Then
                Do While Not .EOF
                    'read each record here
                    '...
                    .MoveNext()
                Loop
                .Close()
            End If
        End With

        'destroy recordset object if necessary (or do it when you unload the form)
        'Set adoRst = Nothing

        'destroy connection object if necessary (or do it when you unload the form)
        'Set adoConn = Nothing

    End Sub

Open in new window

Norman Maina

change the sql connection string to:

strConString ="Provider=MS Remote;" & _ 
           "Remote Server=http://myServerName;" & _
           "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\somepath\mydb.mdb", _
            "admin", ""

Open in new window

Murray Brown

ASKER

Hi. Thanks but the editor doesn't seem to like that last line. What about the ADODB.Connection?
What Imports.... should I be using?

Norman Maina

Imports system.data.OleDb

Murray Brown

ASKER

Hi. I already did that. For some reason it still doesn't work. Do I need to also add a reference? Thanks

Murray Brown

ASKER

Scott McDaniel (EE MVE )

I would strongly advise that you NOT do this with an Access databse. MS Remote is the old "RDS" technolog introduced by MSFT, and is obsolete:

http://www.carlprothman.net/Default.aspx?tabid=93

You would be doing yourself a very big favor if you moved the web-based database to a true server database and set that up to allow remote connections. Otherwise, you're just asking for troubles (corrupt data, invalid updates/inserts/retrievals, and vanishing databases).

With that said: Are you sure the Jet.OLEDB.4.0 provider is installed on the remote server?

Have you tried removing the "Persist Security Info" stuff?

Norman Maina

can you add a catch try block between the adoConn.Open line of code and post it here.

Try this code:

'initialize connection object variable
        adoConn = New ADODB.Connection(strConString)
        'open connection
Try
        adoConn.Open
        strSQL = "Select * from Orders"

        'initialize recordset object variable
        adoRst = New ADODB.Recordset
        With adoRst
            .Open(strSQL, adoConn, , , adCmdText)
            If Not .EOF Then
                Do While Not .EOF
                    'read each record here
                    '...
                    .MoveNext()
                Loop
                .Close()
            End If
catch ex as exception
messagebox.show(ex.message)
End Try

End With

Open in new window

ASKER CERTIFIED SOLUTION

Norman Maina

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Thanks very much