Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Changing an Access Query in Excel | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |