Thread: Querytable OLEDB connection

Hi I'm having a problem with creating a querytable via VBA, everything seems to work fine but I always get the initialization details window popup when the connection is actuall made i.e. when the querytable is refreshed.
The OLE DB initialization window has the correct data source but the other parameters are default or blank, if I click OK it works, or if I edit in the correct details it works... but why wont it just work silently without the popup window? I'm guessing there is something wrong or missing from my connection string.

This is the code snippet:

By the way this is coded in Access (hence the appXL Excel object), access creates a spreadsheet then creates the querytable.

    sConnection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" & _
                  "Data Source=" & sDatabase & ";Mode=Share Deny Write;"
                              
    Set rngDestination = appXL.Sheets("Template").Range("D8")
    
    'Create QueryTable
    Set qryTable = appXL.Sheets("Template").QueryTables.Add(Connection:=sConnection, Destination:=rngDestination, SQL:=sSQL)
    
    'Populate the querytable
    appXL.Application.ScreenUpdating = False
    qryTable.Refresh

Any help appreciated!

Also further on I attpemt to delete the connection before closing the workbook but it doesnt seem to work, I havent debuged that yet but any thoughts welcome!

    With appXL.ActiveWorkbook
        .Connections(1).Delete
        .SaveAs FileName:=sServerPath & "\" & sReportName & " " & sMonth & " " & sYear & ".xls"
        .Close
    End With

Edit: I forgot to mention, the data source is the database that this code module is in, so sDatabase contains the value of Application.CurrentDb.Name probably not important...