Remote connection 3709 Error

[rnashting]

Hello again everyone! I'm having an error trying to retrieve things into a form from a database. I'm just learning, so I'm using the Northwind template that access has, but I'm getting an error that I can't figure out. I let the connection establish during the form_load event, and I get a msgbox confirmation that the connection is established, but when I try to retrieve fields I get "There was an error retrieving information from the database. 3709, The connection cannot be used to perform this operation. It is either closed or invalid in this context." If anyone can decipher what I'm doing wrong, I'd be ecstatic...2 hours trying and I'm still stumped.

Option Compare Database
Option Explicit

Dim remoteConnection As New ADODB.Connection
Dim rsEmployees As New ADODB.Recordset
          
Private Sub Form_Load()
    connect
    SetRecordSet
End Sub

Private Sub Form_UnLoad(Cancel As Integer)
    Disconnect
End Sub

Public Sub Disconnect()
    
    On Error GoTo ConnectionError
    
    rsEmployees.Close
    Exit Sub
ConnectionError:
    MsgBox "There was an error closing the database." & _
        Err.Number & ", " & Err.Description
            
End Sub
Private Sub connect()
On Error GoTo ConnectionError
    Dim remoteConnection As New ADODB.Connection
    With remoteConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "C:\Home\Northwind 2007.accdb"
    End With
    
    MsgBox "Remote Connection successfully established."
       
    Exit Sub

ConnectionError:
    MsgBox "There was an error connecting to the database. " & Chr(13) _
        & Err.Number & ", " & Err.Description
End Sub

Public Sub SetRecordSet()
    Dim sql As String
    On Error GoTo DbError
    
    sql = "select * from Employees"
    
    rsEmployees.CursorType = adOpenKeyset
    rsEmployees.LockType = adLockReadOnly
    
    rsEmployees.Open sql, remoteConnection, _
        , , adCmdText
    
    If rsEmployees.EOF = False Then
        Me.txtAddress = rsEmployees.Fields.Item("address")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("Business Phone")
        Me.txtCity = rsEmployees.Fields.Item("City")
        Me.txtCompany = rsEmployees.Fields.Item("Company")
        Me.txtCountry = rsEmployees.Fields.Item("Country/Region")
        Me.txtEmail = rsEmployees.Fields.Item("E-mail Address")
        Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
        Me.txtFirstName = rsEmployees.Fields.Item("First Name")
        Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
        Me.txtJobTitle = rsEmployees.Fields.Item("Job Title")
        Me.txtLastName = rsEmployees.Fields.Item("Last Name")
        Me.txtMobilePhone = rsEmployees.Fields.Item("Mobile Phone")
        Me.txtNotes = rsEmployees.Fields.Item("Notes")
        Me.txtState = rsEmployees.Fields.Item("State/Province")
        Me.txtWebPage = rsEmployees.Fields.Item("Web Page")
        Me.txtZip = rsEmployees.Fields.Item("Zip/Postal Code")
    End If
        
    Exit Sub

DbError:
    MsgBox "There was an error retrieving information " & _
        "from the database. " _
        & Err.Number & ", " & Err.Description
End Sub

[NeoPa]

I suggest you have a look at one or two of the existing forms in the database.

Managing data with forms is supposed to be a whole lot simpler than you have it. Look particularly at the RecordSource property of the form, and the ControlSource property of the controls that reflect each field.

Let us know if further guidance is neede, though I expect once you see how it should be you'll find it all a whole lot easier.