refreshing tables in excel

[spinkung]

Hi

I am opening an excel file and refreshing some tables (list objects) on 3 different sheets.

so...

        Dim appExcep As Excel.Application
        Dim objActiveWkb As Excel.Workbook
        Dim objActiveSht As Excel.Worksheet
        Dim objLo As Excel.ListObject

        Set appExcel = CreateObject("Excel.Application")
        appExcel.Visible = False

        appExcel.Application.Workbooks.Open (reportFile)

        Set objActiveWkb = appExcel.Application.ActiveWorkbook
        'Set objActiveSht = objActiveWkb.Sheets(1)

        For Each objActiveSht In objActiveWkb.Sheets
            For Each objLo In objActiveSht.ListObjects
                objLo.QueryTable.Refresh
            Next
        Next

        objActiveWkb.Close savechanges:=True
        appExcel.Application.Quit
        Set objActiveWkb = Nothing: Set appExcel = Nothing

problem is that i get prompted for the password to my sql db each time i run the vba and for each table.

can i pass in the password info so i don't get the prompt??

Thanks.

 

[steveboydon]

It does depend on how the SQL server has been setup. If it is in native SQL then passwords are needed. Firstly check with the DB admins. If the SQL server is in mixed mode, addition to a group with access to the sql db will remove the need for password.

It maybe that your sql connection string contains a username and it doesn't need to. If SQL is in mixed mode, and groups set up correctly, the access should be seemless

Steve