Excel Access Query

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

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
 
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 said:
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
Click to expand...
=---
 
Roger,

Thanks both work well.

Is there a test I can do to see if the search was successful ?

I can obviulsy read the contetns of the destination but was hoping for
something a little more elegant.

Cheers
Nigel
 
Nigel,

I usually accept no records returned as an unsuccessful query.

What do you mean by 'More elegant'?
Perhaps (after the query has run)

lastrow = activesheet.range(A65536").end(xlup).row
if lastrow > 1 'Assume Row 1 will be field names
msgbox "No records matched your query"
end if


--
Regards
Roger
Shaftesbury (UK)




Nigel said:
Roger,

Thanks both work well.

Is there a test I can do to see if the search was successful ?

I can obviulsy read the contetns of the destination but was hoping for
something a little more elegant.

Cheers
Nigel


value




----== 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
Click to expand...
=---
 
Hi Roger
Actually since I now clear the range before querying the database I have a
blank range if unsuccessful, so in terms of elegance that's it. Nothing =
Unsuccessful. Thanks for your help on this one.

On another note I store the database (path and file) in the worksheet in
order that if the application is moved the adiministrator can change the
location and/or file if neccessary.

If they do not enter a valid file type, I get the xldialog box asking for
the database login, the code that triggers this is as follow...

cConnection = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & mdbPath & ";" & _
"DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"

If the mdbPath is not an mdb file then the xldialog activates. Any idea how
after using this dialog to select the required mdb I can store the file name
in the worksheet?

Alternatives if I could get VBA to trigger this dailog then the
administrator could use this to navigate and select the right database. I
would still need to be able to store it in the worksheet though.

TIA
Cheers
Nigel
 
The connection object settings are part of a string, rather than separate
Properties. By a combination of Instr, Mid functions (etc), you may be able
to hook out the file name and save it to a cell, Name, or
CustomDocumentProperty for editing/retrieval.

Just a thought
Application.GetOpenFilename
might offer something you can use.

Regards
Roger


Nigel said:
Hi Roger
Actually since I now clear the range before querying the database I have a
blank range if unsuccessful, so in terms of elegance that's it. Nothing =
Unsuccessful. Thanks for your help on this one.

On another note I store the database (path and file) in the worksheet in
order that if the application is moved the adiministrator can change the
location and/or file if neccessary.

If they do not enter a valid file type, I get the xldialog box asking for
the database login, the code that triggers this is as follow...

cConnection = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & mdbPath & ";" & _
"DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"

If the mdbPath is not an mdb file then the xldialog activates. Any idea how
after using this dialog to select the required mdb I can store the file name
in the worksheet?

Alternatives if I could get VBA to trigger this dailog then the
administrator could use this to navigate and select the right database. I
would still need to be able to store it in the worksheet though.

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
Click to expand...
=---
 
Back
Top