Problem With Named Range in SQL Query

[masouder]

I have encountered a very usual error that I have tried to resolve for several hours with no luck. The code involved is spread over several procedures, so I don't think it would help to include it here.

As an overview, my code does the following:

• Prompts the user to open another workbook.
• Shows a form that prompts the user to select a sheet in the just opened workbook.
• Creates a named range (titled "DataQuery1") on the selected sheet that is then used in the FROM clause in a select query.
• Makes several updates in the current workbook and the just opened workbook.
• Closes the other workbook.

Here is the issue: when I run the code a second time I receive a -2147217865 run time error stating that, "Then Microsoft Access database engine could not find the object 'DataQuery1'. Make sure the object exists..."

What is odd is that after I click End in the debug window and then rerun the code then it works fine. So the code works fine every other time I run it!

A couple of other pieces of info:

• After receiving the error message if I type ?[DataQuery1].Address into the Immediate window it returns the correct range address, so the named range is being created properly.
• If I step through the code and stop it just before the final Exit Sub line then I have no problems when I rerun. This is also very odd.

I have never seen anything like this and it's driving me crazy. I have many other routines in this and other workbooks in which I create named ranges which I then use in select queries and have no issues elsewhere.

I would greatly appreciate any help!!

 

[John_w]

Can you post your code showing how the named range is created? Does this code reference the specific sheet and specific workbook (just opened) when creating the named range? Or does it default to the active workbook? Is the named range created with workbook or worksheet scope? Your ?[DataQuery1].Address implies workbook scope.

Also post the code for the SQL SELECT query, showing how you use the named range.

Ideally, post complete code which reproduces the issue.

 

[masouder]

Thanks for the reply, John.

Remember, the code works every other time, so I don't think the issue is with the named range or the SQL. That said, below is the subset of code that you requested. Note that DataSheet is a worksheet variable that is set earlier in the code. Please let me know if you need any additional info.

    ContractorBook.Activate
       
    With DrawSheet
        .Range(.Cells([DrawStart].Row, 1), .Cells([DrawStart].Row + [DrawRowCount], [DrawEnd].Column + 1)).Name = "DrawQuery1"
        .[DrawCostAccount].NumberFormat = "@"
    End With
    
    SQL = "SELECT * FROM [DrawQuery1] "
    SQL = SQL & "WHERE IIF(ISNULL([Cost Account]),[Cost Account],CSTR([Cost Account])) = '" & CostAccount & "' "
    SQL = SQL & "AND NOT IsNull([Payee Name]) "
    'SQL = SQL & "ORDER BY [Gross Amount] DESC "
    
    OpenRecordset SQL, conn, rs

Sub OpenRecordset(SQL As String, conn As Connection, rs As Recordset)
    'Create recordset
    Set rs = CreateObject("ADODB.Recordset")
    
    rs.CursorLocation = 3 'adUseClient on early binding
    rs.CursorType = 1 'adOpenKeyset on early  binding
    
    'Open the recordset
    rs.Open SQL, conn
    
End Sub

 

[John_w]

I haven't been able to reproduce your problem with the code you provided. I focused on the named range because that's what the error message was suggesting.

Is the SELECT querying an Access database table or an Excel range? The error message suggests the former, but the SQL string suggests the latter.

To help futher, can you provide code which reproduces the issue - the steps to open the other workbook and reference a sheet in it can be hard-coded. Also describe more completely the With DrawSheet part of the code, i.e. the other named ranges (DrawStart, DrawEnd, etc) - their exact cell addresses and whether these named ranges refer to the macro workbook or the DrawSheet workbook. What is the range address of DrawQuery1?