Adodb Method 'Open' of object '_Recordset' failed

[drag1c]

Hi,

I am using three tables to make query run.

Could anyone inspect my code to see why I'm getting error from subject of this topic?

Private Sub cmdEdit_Click()

If Me.cmbRoutingNumber = "" Then
MsgBox "Please, select Routing Number", vbInformation
Exit Sub
End If

' Load default parameters for Revision / Change
With frmCirculationSheet
    .txtUsername = ""
    .txtUsername.Enabled = False
    .txtRoutingDescription.Enabled = False
    .txtRoutingNumber.Enabled = False
    .txtEngineType.Enabled = False
    .txtTargetDate.Enabled = False
    .txtUsage.Enabled = False
    .txtAdditionalInfo.Enabled = False
    .txtContentDesc.Enabled = True
    
    .cmbRountingNumberLoc.Enabled = False
    .cmdCal2.Enabled = False
    .cmdStart.Visible = False
    .cmdSave.Visible = True
           
' Get data about workplan from DB
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim i As Integer
    
    ' Create a new ADODB Connection
    Set conn = CreateObject("ADODB.Connection")
    
    ' Specify the database connection details
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\YKO0096\Desktop\SCM_TestDB.accdb"
    
    ' Open the database connection
    conn.Open
    
    ' Create a new ADODB recordset
    Set rs = CreateObject("ADODB.Recordset")
   
    ' Define the SQL statement to retrieve the data from the database
    strSQL = "SELECT tbWorkplanAtt.Owner, tbWorkplanAtt.Target, tbWorkplanAtt.Workplan, tbWorkplanAtt.Routing_Number, tbWorkplanAtt.Description, tbWorkplanAtt.Usage, tbWorkplanAtt.Engine_Type, tbWorkplanRevision.Content_Of_Task, tbWorkplanRevision.Additional_Info, tbOperations.Process, tbOperations.OPN_Number" & _
            " FROM (tbWorkplanAtt LEFT JOIN tbOperations ON tbWorkplanAtt.Workplan = tbOperations.Workplan) LEFT JOIN tbWorkplanRevision ON tbWorkplanAtt.Workplan = tbWorkplanRevision.Workplan" & _
            " GROUP BY tbWorkplanAtt.Owner, tbWorkplanAtt.Target, tbWorkplanAtt.Workplan, tbWorkplanAtt.Routing_Number, tbWorkplanAtt.Description, tbWorkplanAtt.Usage, tbWorkplanAtt.Engine_Type, tbWorkplanRevision.Content_Of_Task, tbWorkplanRevision.Additional_Info, tbOperations.Process, tbOperations.OPN_Number" & _
            " HAVING (((tbWorkplanAtt.Workplan)='S50-Test'))"
    
    ' Execute the SQL statement and retrieve the data
    rs.Open strSQL, conn
    
    
    ' Check if any records were returned
    If Not rs.EOF Then
        ' Populate the textboxes with the first record's data
        .txtUsername.Value = rs("Owner").Value
        .txtTargetDate.Value = rs("Target").Value
        .txtRoutingNumber.Value = rs("Workplan").Value
        .cmbRountingNumberLoc.Value = rs("Routing_Number").Value
        .txtRoutingDescription.Value = rs("Description").Value
        .txtUsage.Value = rs("Usage").Value
        .txtEngineType.Value = rs("Engine_Type").Value
        .txtContentDesc.Value = rs("Content_Of_Task").Value
        .txtAdditionalInfo.Value = rs("Additional_Information").Value
        
        ' Clear the listbox before populating it with data
        frmCirculationSheet.lstActiveOPN.Clear
        
        ' Loop through the retrieved data and populate the listbox
        i = 0
        While Not rs.EOF
            ' Read the values from the recordset columns
            Dim column1Data As String
            Dim column2Data As String
            
            column1Data = rs("Process").Value
            column2Data = rs("OPN_Number").Value
            
             ' Add the data to column 1 in the listbox
            .lstActiveOPN.AddItem column1Data
            .lstActiveOPN.List(i, 1) = column2Data
                       
            ' Move to the next record in the recordset
            rs.MoveNext
            
            i = i + 1
        Wend
    Else
        ' No records found, clear all the textboxes and the listbox
        .txtTargetDate.Value = ""
        .txtRoutingNumber.Value = ""
        .cmbRountingNumberLoc.Value = ""
        .txtRoutingDescription.Value = ""
        .txtUsage.Value = ""
        .txtEngineType.Value = ""
        .txtContentDesc.Value = ""
        .txtAdditionalInfo.Value = ""
        
        .lstActiveOPN.Clear
    End If
    
    ' Close the recordset and the connection
    rs.Close
    conn.Close
    
    ' Release the objects
    Set rs = Nothing
    Set conn = Nothing
    
    .Show
End With

End Sub

I assume there is some error in strSQL, which is readable by access but not by Excel VBA Adodb connection.

 

[Micron]

I suspect it is caused by how you've placed double quotes into the connection string. Perhaps this page will help given that there are different syntaxes for different types of data sources.

Examples of ADO Connection Strings - Database Tour Documentation

See connection strings examples you can use for opening databases in Database Tour.

www.databasetour.net

 

[drag1c]

It's not issue. I already used multiple times the same connection string with no issue. It works completely fine in rest of the queries...
This is something else.

 

[drag1c]

Topic is solved. Solution is to add each column into square brackets, example:
tbOperations.[Workplan], etc...

Moderators can lock it.

Micron thanks for reaching to help !

 

[Micron]

It seems I misunderstood your problem. I thought the title was referring to opening the connection. Seems odd that brackets are required around the field names in your sql statement when their names do not contain spaces. Even odder that they'd be required around the fields but not the table names. Thanks for posting the solution!