Query

[Leanne M (Aussie)]

Range("A7").QueryTable.Refresh BackgroundQuery:=False

Can someone please explain why this is = False.

This line of code is for refreshing a query so I do not understand why it is
False.

Every time I try to refresh this query on a computer that is not the
development pc I get an error which points to this line of code after I have
entered the parameters.

I have created a new data source on this pc to point to the same file as the
original pc and called it the same. Is there something else I am not doing?

 

[FSt1]

hi,
if the background guery is set to false then code execution is halted until
the refresh is complete. otherwise if there is additional code after the
refresh command that may try to manipulate the query data and background
query is set to true(default is true), the additional code will execute and
start manipulating query data before is has refreshed. always best to let the
MSQ complete the refresh.

regards
FSt1

 

[Leanne M (Aussie)]

Ok thank you for that info. The refresh keeps failing on this line so
thought it may be something but turns out to be nothing.

 

[Dave Peterson]

What kind of error are you seeing when you run it. Maybe that cell isn't
associated with a querytable?

Maybe turning off all the background refreshing querytables would work???

Option Explicit
Sub testme()
Dim QT As QueryTable
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
For Each QT In wks.QueryTables
QT.Refresh BackgroundQuery:=False
Next QT
Next wks
End Sub

This should be a one time thing--that setting will stay that way until you (or
your code) changes it.

 

[Leanne M (Aussie)]

Hi Dave,
The error I am currently getting reads -
ODBC Excel Driver Login Failed - Failure creating File and I can click OK to
this. When I do it allows me to select the workbook that is my database so I
do this and then it works.

I have had other errors but at the moment as I am getting this one I am not
seeing the others to let you know what they are.

Should I try the code you have or has this information changed things. What
do you mean by 'it should be a one time thing'? Do you mean it should be
placed in workbook open code or that I should be able to add it in - see if
it solves the problem and then remove it? Sorry to ask so many questions but
I really need detailed instructions - I have tried to follow instructions
given to me recently and am starting to realise that I know less and less
about what I am doing!!

Thanks

 

[NoodNutt]

G'day Leanne

Your living in the UK for it's tropical climate and frosty cold beer,
right!.LOL

Kind regards
Mark
Melbourne, Australia.

PS. Collingwood beat Essendon in the ANZAC Day match. Go Pies!

 

[Dave Peterson]

You are now officially over my head. I have no idea what that error message
means!

But I meant that this setting shouldn't ever change on its own. You should be
able to set it once (manually or via code) and it should stay that way.

Maybe someone who knows ODBC and its error messages will chime in and set us
straight????