troubleshooting Question

Using ADO to connect to a database: Factors preventing connection

Avatar of 9XqUwH3S
9XqUwH3SFlag for United States of America asked on
DatabasesMicrosoft Access
4 Comments1 Solution413 ViewsLast Modified:
Dear Experts,

What could be causing this textbook example form from connecting to the Northwind database whose file path is correct?

I've just checked the reference library selections, the file type of both Databases and the code. I get a 424 error which says it cannot connect tot the database.

Line 5 is highlighted as the problem.


Option Compare Database

Private Sub Form_Load()

Dim remoteConnection As New ADODB.Connection
Dim rsProducts As New ADODB.Recordset
    Connect
    SetRecordset
    
End Sub

Private Sub Form_Unload(Cancel As Integer)

    Disconnect
    
End Sub

Public Sub Disconnect()

    On Error GoTo ConnectionError
    
    rsProducts.Close
    remoteConnection.Close
    
Exit Sub

ConnectionError:

MsgBox "There was an error closing the databse." & _
    Err.Number & ", " & Err.Description
    
End Sub

Private Sub Connect()

    On Error GoTo ConnectionError
    
    With remoteConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "C:\Users\S B\Documents\Databases\Northwind.accdb"
        
    End With
    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 Products"

rsProducts.CursorType = adOpenKeyset
rsProducts.LockType = adLockReadOnly


rsProducts.Open sql, remoteConnection, _
    , , adCmdText
    
    If rsProducts.EOF = False Then
    'Using three different techniques to access items in a recordset
    Me.txtProductID = rsProducts!ID
    Me.txtProductCode = rsProducts.Fields.Item("Product Code")
    Me.txtProductName = rsProducts.Fields.Item(3)
    
End If

Exit Sub

DbError:

MsgBox "There was an error retrieving information " & _
       "From the database." _
       & Err.Number & ", " & Err.Description
        
End Sub
Capture.JPG
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros