Excel - A million queries in the Name Box 1

[jonescj911]

Hi,

I am trying to write a macro in Excel that allows a user to enter a parameter into an input box which will be the criteria for a query to an Access database. The input will determine what is returned from the database my worksheet is connecting to.

In the sheet's NameBox is a never ending list of previous query names. These query's results are populating the cells I want the macro's query to return to whenever I test the macro. I can right click on the cell the data is returned to and delete the queries from the External Data Range Properties window one at a time but it is very time consuming.

Is there any way to delete them all at once? Is there any way to keep them from being stored? Why are they there?

Any help is appreciated. Thanks.

Cliff

 

[xlbo]

How are you creating the queries? manually or with code ?

You should just be able to select all teh cells at once and delete them. You should get a message asking if you want to delete the external query as well - which you do...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[zahead31]

I ran into the same thing while I was writing VBA code that queried a database. I was also defining my own named ranges manually within the sheet, so I added this code to delete only the external data names.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next

Dim nm As Name
'delete names created by data query
For Each nm In Names
    If Left(nm.Name, 22) = "listNames!ExternalData" Then
       nm.Delete
    End If
Next nm

End Sub

"listNames" is the name of my worksheet that the named ranges are defined on. You will probably need to change the name in quotes to suit your needs, and hopefully this will work for you.

HTH,
Matt

 

[jonescj911]

Hi Matt,
Thanks for the response. I originally created the query with the macro recorder and then I tweaked it from there. The funny thing is it works great for SQL2005 databases but I'm getting hung up on this problem while querying Access databases. It also appears to have something to do with the BackgroundRefresh property of the query. It always dies whenever I try to set that property.

I'm going to try what you posted and get back to you. I appreciate your input.

Thanks.

Cliff

 

[xlbo]

If you tweaked code from the macro recorder and used that to create queries, your problem is that the macro recorder uses Querytables.ADD so every time you run it, a new querytable object is created

You need to change the code to work with an existing querytable

Sheets("Sheetname").querytables(1).refresh

Background refresh refers to the code continuing to run before the query has finished executing. Generally, this is not a good option as 9 times out of 10 you want the rest of the code to work with the new data that has been returned. I always set the background refresh property to FALSE

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jonescj911]

Hi Geoff - I'm not really sure how to create a QueryTable without using ADD. I tried doing this:
------------------------
Dim qTable As QueryTable

Sub CreateQueryTable()

Sqlstring = "SELECT table1.column1, table2.column2
FROM table1, table2"

'Create a new query table.
Set qTable = ActiveSheet.QueryTables.Add ("ODBC;dsn=AccessCnxn", _
ActiveSheet.Range("A1"), Sql=:Sqlstring)

'Refresh the query table.
qTable .Refresh BackgroundQuery:=False
Sheets("Sheet2").QueryTables(1).Refresh

'Display the number of rows returned to the query table.
msgbox qTable .ResultRange.Rows.Count & " Rows Returned"

End Sub
-------------------------
As you can see I used ADD.

I get 3000 rows returned. I don't need that much data so I put a WHERE clause in there and change the SQL statement to:

SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.column3 = table2.column3

After doing so I get a "Runtime error: 1004 General ODBC error".

Do I have to store the original result set from the SELECT..FROM statement somewhere and then Query that? This is driving my cuckoo so any advice would be appreciated.

Thanks,
Cliff

 

[jonescj911]

Hi Matt,

I just wanted to let you know that the subroutine you posted cleared out all those saved queries. Of course now my query doesn't work at all - but I'm pretty sure it has to do with something I'm doing wrong. I'd rather get no data at this point than data from a database I don't even want to connect to. That's what was happening.

Thanks a million and if you have any more advice - I'm all ears.

Cliff

 

[xlbo]

Once you have ADDED a querytable (as you have done), you don;t need to add it again

Once you had run your 1st query that returned too many rows, if you want to amend that you need to work WITH the querytable

The querytable has several properties that you can reference including the CONNECTION and the COMMANDTEXT among others. The commandtext is your SQL. You can thereafter change the SQL used by the query and re-run it by using the following:

sub UpdateQuery()

With sheets("Sheetname").querytables(1)
 .commandtext = "New SQL statement goes here"
 .refresh
end with

End Sub

What I tend to do when dealing with querytables is set up a very basic query manually to create the querytable object and then completely amend it in code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jonescj911]

Geoff,

I have taken your advice have been able to create a query table on the fly and then make some changes to the properties of it i.e. .RefreshStyle = xlOverwriteCells etc..

I just have a little bit of tweaking I need to do but you definitely got me over the hump. I appreciate it.

You too Matt.

Thanks.

Cliff

 

[xlbo]

happy to help

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question