By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Remote connection 3709 Error

P: 17
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim remoteConnection As New ADODB.Connection
  5. Dim rsEmployees As New ADODB.Recordset
  6.  
  7. Private Sub Form_Load()
  8.     connect
  9.     SetRecordSet
  10. End Sub
  11.  
  12. Private Sub Form_UnLoad(Cancel As Integer)
  13.     Disconnect
  14. End Sub
  15.  
  16. Public Sub Disconnect()
  17.  
  18.     On Error GoTo ConnectionError
  19.  
  20.     rsEmployees.Close
  21.     Exit Sub
  22. ConnectionError:
  23.     MsgBox "There was an error closing the database." & _
  24.         Err.Number & ", " & Err.Description
  25.  
  26. End Sub
  27. Private Sub connect()
  28. On Error GoTo ConnectionError
  29.     Dim remoteConnection As New ADODB.Connection
  30.     With remoteConnection
  31.         .Provider = "Microsoft.ACE.OLEDB.12.0"
  32.         .Open "C:\Home\Northwind 2007.accdb"
  33.     End With
  34.  
  35.     MsgBox "Remote Connection successfully established."
  36.  
  37.     Exit Sub
  38.  
  39. ConnectionError:
  40.     MsgBox "There was an error connecting to the database. " & Chr(13) _
  41.         & Err.Number & ", " & Err.Description
  42. End Sub
  43.  
  44. Public Sub SetRecordSet()
  45.     Dim sql As String
  46.     On Error GoTo DbError
  47.  
  48.     sql = "select * from Employees"
  49.  
  50.     rsEmployees.CursorType = adOpenKeyset
  51.     rsEmployees.LockType = adLockReadOnly
  52.  
  53.     rsEmployees.Open sql, remoteConnection, _
  54.         , , adCmdText
  55.  
  56.     If rsEmployees.EOF = False Then
  57.         Me.txtAddress = rsEmployees.Fields.Item("address")
  58.         Me.txtBusinessPhone = rsEmployees.Fields.Item("Business Phone")
  59.         Me.txtCity = rsEmployees.Fields.Item("City")
  60.         Me.txtCompany = rsEmployees.Fields.Item("Company")
  61.         Me.txtCountry = rsEmployees.Fields.Item("Country/Region")
  62.         Me.txtEmail = rsEmployees.Fields.Item("E-mail Address")
  63.         Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
  64.         Me.txtFirstName = rsEmployees.Fields.Item("First Name")
  65.         Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
  66.         Me.txtJobTitle = rsEmployees.Fields.Item("Job Title")
  67.         Me.txtLastName = rsEmployees.Fields.Item("Last Name")
  68.         Me.txtMobilePhone = rsEmployees.Fields.Item("Mobile Phone")
  69.         Me.txtNotes = rsEmployees.Fields.Item("Notes")
  70.         Me.txtState = rsEmployees.Fields.Item("State/Province")
  71.         Me.txtWebPage = rsEmployees.Fields.Item("Web Page")
  72.         Me.txtZip = rsEmployees.Fields.Item("Zip/Postal Code")
  73.     End If
  74.  
  75.     Exit Sub
  76.  
  77. DbError:
  78.     MsgBox "There was an error retrieving information " & _
  79.         "from the database. " _
  80.         & Err.Number & ", " & Err.Description
  81. End Sub
Mar 13 '10 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 15 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.