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.