Activesheet.usedrange.clearcontents
will delete the results of the last query

I use the following, called after the query results are delivered to clear
the NamedRange that would otherwise prevent further queries:

Sub DeleteExternalDataRange()
Dim xName As Name
Dim X As Integer
On Error Resume Next
For Each xName In ActiveWorkbook.Names
For X = 1 To Len(xName.Name) - 12 Step 1
If Mid(xName.Name, X, 12) = "ExternalData" Then
xName.Delete
End If
Next
Next
End Sub


--
HTH
Roger
Shaftesbury (UK)




"Nigel" wrote in message
...
Hi All
I have a Query Table set up with an ODBC connection to an Access Database.
The SQL select statement is changed from a value passed in from a
userform,
and a refresh is initiated to load the recordset for this value into the
worksheet. Everything works OK, except when the value passed in the SQL
statement is not found in the database, in this case the worksheet content
is not changed which continues to show the previous value(s).

How can I clear the worksheet result range before the search begins?

How can I test if the SQL select was successful and found the new value in
the database?

I have also noticed that everytime I run the SQL a new range name is added
to the workbook is this expected and will it cause a problem since I could
be running this 1000's of times ?

TIA
Cheers
Nigel




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---