none
import records from access where field name = excel named range RRS feed

  • Question

  • I can't figure this out. 

    #1)

    mypath throws a compile error, but when i hard-code it, it works fine.  However, in another Macro in the same Workbook, iI can assign a dynamic path. 

    #2)

    Also, if I hard code the Where Clause, like this:

    WHERE ([Level3]='Transfer Risk')

    It works.  However, I want to reference a Named Range on my Sheet, like this:

    WHERE ([Level3]=Range('rc_RBP')

    It keeps throwing an error that reads: 'undefined function Range in expression'

    Sub GetMyData()
        Dim mypath As String
        mypath = ThisWorkbook.Path
            Const strDb As String = mypath & "\Risk_DB.mdb;"
            Const strQry As String = "SELECT * from [qryLevels_Of_Risk] WHERE ([Level3]=Range('rc_RBP'))"
            
            Dim rs As ADODB.Recordset
            Dim cn As ADODB.Connection
         
            Set cn = New ADODB.Connection
            cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
            Set rs = New ADODB.Recordset
         
            With rs
                Set .ActiveConnection = cn
                .Open strQry
            End With
            Worksheets("Economic Downturn").Range("AL10").CopyFromRecordset rs
         
            rs.Close: cn.Close
            Set rs = Nothing: Set cn = Nothing
    End Sub
    



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, December 7, 2015 11:13 PM

Answers

All replies

  • This is how you do it.

    Where [Level3] = '" & Worksheets("Economic Downturn").Range("rc_RBP").Value & "'"


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, December 7, 2015 11:48 PM
  • Hi ryguy72,

    I am glad your issue has been resolved, I suggest you mark the reply as answer to close this thread, and then others who run into the same issue could find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 8, 2015 2:48 AM