Excel Access Query
 

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 =---

Excel Access Query
 

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
=---

Excel Access Query
 

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


"Roger Whitehead" wrote in message
...
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
=---






----== 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 =---

Excel Access Query
 

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" wrote in message
...
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


"Roger Whitehead" wrote in message
...
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
=---






----== 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
=---

Excel Access Query
 

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

"Roger Whitehead" wrote in message
...
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" wrote in message
...
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


"Roger Whitehead" wrote in message
...
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
=---






----== 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
=---






----== 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 =---

Excel Access Query
 

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" wrote in message
...
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

"Roger Whitehead" wrote in message
...
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" wrote in message
...
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


"Roger Whitehead" wrote in message
...
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
=---






----== 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
=---






----== 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
=---