Refresh ODBC Query on Current Worksheet Only

[Brumbot]

Hi all,

I am quite new to VBA and am working on something that requires a macro to copy some columns from my worksheet, refresh a query and then paste those values in (to maintain the order of them since they are not included in the query and ODBC does not preserve the correct order for these columns). Now I thought I had it working fine, but then I realised that other queries in the workbook were refreshing too.

Would anyone be able to advise on how to prevent this from happening and to make it a worksheet specific refresh? Here is my code:

Please Login or Register  to view this content.

Also the 'Application.ScreenUpdating = False' command doesn't seem to work in this case either. The worksheet I would like the refresh on is called "NS 2013".

Any help would be appreciated. Thanks.

[PingPing]

When you originally set up the Connection, just before you hit the 'Finish' button there was a button called 'Advanced' on the bottom left of the dailog box. If you clicked on this the resulting dialog box contains options for Refresh. By default, the 'Enable background refresh' is selected. You probably want to disable all refreshes so that it's done manually.

Additionally, in the same dialog box there's another tab called 'Definition'. In there you get the option to set 'Command type:'. Set this to 'SQL' and type in your SQL statement in the box below it ('Command text'). You can order the columns in your SELECT statement. That will save you unecessarily re-ordering the columns.

[Brumbot]

When you originally set up the Connection, just before you hit the 'Finish' button there was a button called 'Advanced' on the bottom left of the dailog box. If you clicked on this the resulting dialog box contains options for Refresh. By default, the 'Enable background refresh' is selected. You probably want to disable all refreshes so that it's done manually.

Additionally, in the same dialog box there's another tab called 'Definition'. In there you get the option to set 'Command type:'. Set this to 'SQL' and type in your SQL statement in the box below it ('Command text'). You can order the columns in your SELECT statement. That will save you unecessarily re-ordering the columns.

Thanks for the quick reply.

So I've disabled background refreshing, thanks for that. The command type is auto-set to SQL and is greyed out. The command text box is full of text that I imagine refers to the columns that are pulled from the query? You are saying I can add the extra columns that I've added on to the end of the query into this box and it will refresh them and maintain the correct order?

Currently the box looks like this...

Please Login or Register  to view this content.

... which looks like alot of gibberish to me :P

[PingPing]

It's easier to read SQL when it's formatted, eg:

Please Login or Register  to view this content.

The 'SELECT' rows (13 of them) are the columns that come back from the query and pasted into your worksheet, ie. your worksheet has 13 columns in it in the order you see them above. Just change that order in the query so that the next time you run it, the columns go into your worksheet in the order you want and you don't have to do any tedious, manual copy-and-paste of columns.

You can add extra columns at the end of the 'SELECT' part, ie. after the last column (STAR.NS_VIEW_GLOBAL NS_VIEW_GLOBAL) however, not that the columns are separated by commas. Make sure any columns you add are comma separated as well as puting a comma at the end of the current last column, ie. "STAR.NS_VIEW_GLOBAL NS_VIEW_GLOBAL,".

You can only add columns to those in the SELECT part, that are in the 3 tables the query looks at. Those three tables are shown in the 'FROM' clause. They take the form [Database Name].[Table Name], ie. the name of the database you're querying is 'STAR'. Also note that the 'SELECT' rows take the form [Table Name].[Column Name].

Remember: you don't need to take all of the columns and you shouldn't. You should only SELECT those columns that you need.

The 'WHERE' clause places the conditions on the selection from the 3 tables such that only the rows that match the conditions will be returned to you in what Database developers/administrators call 'the Result Set'. I wouldn't recommend messing with the WHERE clause unless you know what you're doing.

I hope this helps solve your problem.

[Brumbot]

It's easier to read SQL when it's formatted, eg:

Please Login or Register  to view this content.

The 'SELECT' rows (13 of them) are the columns that come back from the query and pasted into your worksheet, ie. your worksheet has 13 columns in it in the order you see them above. Just change that order in the query so that the next time you run it, the columns go into your worksheet in the order you want and you don't have to do any tedious, manual copy-and-paste of columns.

You can add extra columns at the end of the 'SELECT' part, ie. after the last column (STAR.NS_VIEW_GLOBAL NS_VIEW_GLOBAL) however, not that the columns are separated by commas. Make sure any columns you add are comma separated as well as puting a comma at the end of the current last column, ie. "STAR.NS_VIEW_GLOBAL NS_VIEW_GLOBAL,".

You can only add columns to those in the SELECT part, that are in the 3 tables the query looks at. Those three tables are shown in the 'FROM' clause. They take the form [Database Name].[Table Name], ie. the name of the database you're querying is 'STAR'. Also note that the 'SELECT' rows take the form [Table Name].[Column Name].

Remember: you don't need to take all of the columns and you shouldn't. You should only SELECT those columns that you need.

The 'WHERE' clause places the conditions on the selection from the 3 tables such that only the rows that match the conditions will be returned to you in what Database developers/administrators call 'the Result Set'. I wouldn't recommend messing with the WHERE clause unless you know what you're doing.

I hope this helps solve your problem.

Thanks for the reply.

Actually, the columns that I'm having problems with are not part of the query. They are additional columns that calculate from the columns in the query, apart from one where the user must input into the column. When the query is updated, the order isn't preserved so I made this macro to copy the columns before the refresh and paste them after. I believe what you're talking about is adding extra columns from the tables. Sorry if I'm confused.

The main problem is with the refreshing of other queries using the ActiveWorkbook.RefreshAll command. I would like this to work for one worksheet, not the workbook.

[Brumbot]

Bump. Still awaiting an answer.

EDIT: Found the problem. The code I needed was:

Please Login or Register  to view this content.

This updated the current table only.