MSSQL Data Import via Macro 1

[smithcza]

Hi

I need to import data from a MSSQL database, using a macro. It is working well, problem is, the DB password has to be hardcoded the way I do it, which is not ideal. I also do not want the user to enter login information everytime the macro runs.

I can pass the password into variables using classes from a thirdparty product. This works, the variables picks up the values OK. Problem comes in when trying to pass the variables into the connection string. Can this be done?

The code I have is as follows:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=sVaraible1;UID=sUID;PWD=sPWD;APP=Microsoft® Query;WSID=CHRISTIAAN-IBM;DATABASE=sDBName" _
, Destination:=Range(sRange))
.CommandText = Array( _
"SELECT ... ***query***" _
, "o.HD_RESULTS HD_RESULTS WHERE HD_RESULTS.EXPORTED_WAT = '0' ")
.Name = "Query from BLEH_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Can anybody please advise.

Regards

SmithcZA

 

[PHV]

Something like this ?
"ODBC;DRIVER=SQL Server;SERVER=[!]" & [/!]sVaraible1 [!]& "[/!];UID=[!]" & [/!]sUID [!]& "[/!];PWD=[!]" & [/!]sPWD [!]& "[/!];APP=Microsoft® Query;WSID=CHRISTIAAN-IBM;DATABASE=[!]" & [/!]sDBName [!]& "[/!]" _


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smithcza]

Thanks a million, works like a charm!!!!!

SmithcZA

 

[SkipVought]

SmithcZA,


FYI, a mistake that is often made is to ADD that QueryTable each time you want a fresh set of data. The code that you posted need only be run ONCE.

All you need do is a Data/Refresh or

    Sheets("YourSheetName").QueryTables("Query from BLEH_1").Refresh

If you are changing the query, then you will need a new CommandText string (SQL). If you are chaging databases, then you will need a new Connect string.

Skip,

_[red][/red]

 

[smithcza]

Thanks for the info, SkipVought.

Question, will this still be tru if the workbook is re-saved as another workbook? Sheet names will stay the same, just file name will change.

Regards,



SmithcZA

 

[SkipVought]

No change

Skip,

_[red][/red]