?
Solved

Using ADO to connect to a database: Factors preventing connection

Posted on 2009-12-23
4
Medium Priority
?
393 Views
Last Modified: 2013-12-05
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

Open in new window

Capture.JPG
0
Comment
Question by:9XqUwH3S
  • 3
4 Comments
 
LVL 14

Accepted Solution

by:
Bill Ross earned 1500 total points
ID: 26122068
Hi,

Check to see that ADO is referenced for this statement.
Dim remoteConnection As New ADODB.Connection

The code below does not appear correct.  The Connect and SetRecordset are "hanging".  
Dim rsProducts As New ADODB.Recordset
    Connect
    SetRecordset

Bill
0
 

Author Comment

by:9XqUwH3S
ID: 26122470
Thank you for the gift of your comment, Bill Denver. Merry Christmas.

Yes, that line is in my project.

Removing all of the debug code seems to have worked. I'm in process of finishing this to confirm what the solution turns out to be.

I'll let you know. It might be 48 hours from now, but I look forward to telling what the solution was.

Sincerely,
Steven
0
 

Author Comment

by:9XqUwH3S
ID: 26156495
Could you clarify what you mean by "hanging" in regard to line 22 and 23. How would I complete that?
0
 

Author Closing Comment

by:9XqUwH3S
ID: 31669666
Thank you. The book example appears to not work. I am satisfied to move on because I have had success with my project for which the example was intended to help me accomplish,
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question