Odbc/sql server access in Excel - repeatedly asking for password

[rossbritton26]

Hi,

I have created a macro which creates a SQL Server connection and returns data as requested - this works fine, however, my macro involves running the same code a number of times - also works fine, just with different connections - still all working!

However, because I am making a number of connections I am prompted for a password each time the macro creates one the connections.
The password is always the same, is there I way I can maybe add to my code below so it won't require me to input the password each time, e.g. maybe the password somewhere in the code?

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=(my server name here);UID=(user name here);;APP=2007 Microsoft Office system;WSID=2FDCZ2J;DATABASE=" & strDBName)), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array("sql query here"")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\" & Application.UserName & "\AppData\Roaming\Microsoft\Queries\" & strDBName & ".dsn"
        .ListObject.DisplayName = "Table_" & strDBName & "_(table name here)"
        .Refresh BackgroundQuery:=False
    End With

Thanks.

 

[mole999]

I think in here somewhere

"ODBC;DRIVER=SQL Server;SERVER=(my server name here);UID=(user name here);;APP=2007 Microsoft Office system;WSID=2FDCZ2J;DATABASE=" & strDBName)), Destination:=Range("$A$1")).QueryTable

you need

Password=, well the variant that works with server / access, looks like it is missing between the double semi colons

 

[rossbritton26]

Thanks for the response mole999,

I did try that originally, after reading your response I searched again and found an example where Pwd was used, not Password.

This seemed to work!

Thanks for your prompt