Refresh Microsoft Query in Excel 2003 1

[johnisotank]

Hi All,

having a small problem getting my query to refresh. I know why it's happening but don't know how to solve it.

Here is my code:

Sub RefreshData()
    Range("A3").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

It is failing because I have not specified the SQL Server password for the database. Problem is that when I manually put it in, it doesn't remember it when I re-open the workbook and I can't see anywhere to hard code the login details in VBA.

Hope someone can advise.

thanks
John

 

[PHV]

Can't you put the password in the DSN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, have a look at the Connection property of the QueryTable object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

    Debug.Print Sheets("YOURSHEETNAME").QueryTables(1).Connection

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Thanks for this Skip

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

That was a further clarification of PHV's suggestion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Thanks for all your input,

after all that, it turns out I didn't need to do anything with connections strings or anything like that.

When you right click the query and select 'Data Range Properties" there is an option to 'Save Password'.

Don't know if I did something wrong in the initial setup but this was unticked.

Ticking it has made everything work perfect.

My apologies if I didn't make myself clear in original post.

Thanks again
John