locked
Correct source object reference for creating record set from table in Excel RRS feed

  • Question

  • I am trying to create a recordset from a table in excel that is linked dynamically so that any changes to the recordset would also reflect in the original table. While trying to implement the solution provided in a thread listed below, I get a run-time error stating: "The Microsoft Access database engine could not find the object 'Table1'.
    In the provided solution, the SQL script query refers to 'ZipDemoData' which is a named range and I assumed that using a named range in the excel file I am working from would suffice. The named range referred to in my SQL script is Table1 (a table in Sheet1). 

    My current code is:
        Dim DBFullName As String
        Dim Cnct As String, strSQL As String, strTag As String
        Dim Cn As ADODB.Connection
        Dim Rs As ADODB.Recordset

        DBFullName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

        Worksheets("Sheet1").Activate

        Set Cn = New ADODB.Connection
        Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFullName & "';Extended Properties='Excel 12.0;" & _
                "HDR=Yes;IMEX=1';"
        Cn.Open ConnectionString:=Cnct

        Set Rs = New ADODB.Recordset
            strTag = "PC-001"
            strSQL = "SELECT T1.[Tag Number], T1.[Description] " _
                & "FROM Table1 AS T1 " _
                & "WHERE (((T1.[Tag Number])='" & strTag & "'));"

    Rs.Open strSQL, Cn, adOpenDynamic, adLockReadOnly

    My question is therefore: Can you please assist me to understand how I should reference the source object (Table1)?
    I noticed that the original thread has been marked as answered in 2012 and have therefore opted to ask a new question regarding the reference to the source object in case further comments to the original thread do not receive attention.

    Thank you kindly for any assistance offered!

    ----------------------------------

    Original thread:

    https://social.msdn.microsoft.com/Forums/en-US/71ecfd75-bae2-43f7-b7b9-6fbeb69f7095/use-an-excel-range-as-an-ado-recordset?forum=isvvba

    Monday, February 22, 2016 11:45 AM

All replies

  • Why do you want to do what you are attempting - it looks like you are using the data on another sheet within the same workbook... why not just use formulas?
    Monday, February 22, 2016 8:58 PM
  • Hi Bernie, thank you for engaging this thread.

    The table I am trying to create a recordset from is an index of equipment items. I want to query the data from the recordset to create a folder structure based on a predefined outline and copy templates into specific folders within this structure based on results from the queries performed on the Table1 recordset. Furthermore, information from one or more rows will then be copied into different templates depending on the nature of the information.

    I know that the application strongly suggests using MS Access, but I am sticking with Excel due to user base considerations.

    Please let me know should you require more information or more content from the table with example queries and actions to be performed based on query results.

    Tuesday, February 23, 2016 6:29 AM
  • I have a lot of macros that extract data from databases: to save to individual sheets or files based on the values in one or more columns, and they might be easily modified to do what you want. If you could post example data and how your would want the data manipulated....
    Tuesday, February 23, 2016 3:13 PM
  • Does something simple like this work:

    SELECT * FROM Table1

    I use name ranges like this and I have never had a problem.

    Also, try this

    SELECT * FROM [Sheet1$]

    • Edited by mogulman52 Tuesday, February 23, 2016 11:36 PM
    Tuesday, February 23, 2016 6:33 PM