Excel and Database Queries (DSN)

[keith]

Does anyone know if a MSQuery query is named in Excel? I have a database query that I refresh from VBA. Works great multiple times on development machine, but when I distribute the file - the first time the user runs the VBA it pukes and asks them which DSN they want to refresh. After they pick the DSN it works in the future, but I am getting a ton of calls cause they don't know which DSN to pick initially. Soooo I need to be able name the specific DSN in the VBA code to refresh the query - I think.. any suggestions - thanks in advance

[Jack in the UK]

ok a long shot probaply miles off

try to record all you motions and look at the code ODBC will req passwords and all sorts, bit out of date (in thtat not very updayed) and the links you need will be visable, so others can use command to refresh.. or Extranal data tool bar

[keith]

Yea - I have viewed the recorded code. Same problem - it wants you to make the section active and then refresh. Problem is that it doesn't name the query or which DSN. So when the user runs the code for the first time it prompts asking which DSN to use. After the first time it remembers which DSN so it has to be storeing it somewhere!

Worksheets("data").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

[Jack in the UK]

im sure once you in ODBC you in utill you logoff... somethings out here, i see.

its this at work?

Yes of cause, over LAN, many users

[keith]

actually it remembers it forever.. even if reboot - its just the first time I need to get around

[Jack in the UK]

have a feeling

1 poss licence issues need to chat to It dept on that as they may have set to auto release the excel link over ODBC drivers..

like i thought should hold this info,,,, try input on others PC and see if you set it up all runs ok - if so sorry i dont know the fix

[Jack in the UK]

this is one i wrote fro my complany make what you can i nver sign in or logon or anything just run the code..

you keep saying DNS thats the data connection the bespoke is linked to.. so this code will allow that edit

i hope!



With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=emc;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=emc;", _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT ""Sales Ledger Customer File Co 01"".""Address 01"", ""Sales Ledger Customer File Co 01"".""Address 02"", ""Sales Ledger Customer File Co 01"".""Address 03"", ""Sales Ledger Customer File Co 01"".""Address 04" _
, _
"""" & Chr(13) & "" & Chr(10) & "FROM ""Sales Ledger Customer File Co 01"" ""Sales Ledger Customer File Co 01""" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
End Sub

[keith]

cool! I think that will work, I will try it tomorrow and let you know..