code to retrieve SQL table

[iknowu99]

Hi all,
I'm trying to get the following to work..except I cant understand whether this is info stored locally on my PC or is it getting new data every time from the Database?

Sub Macro2()
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=mkt-db;Use Procedure for Prepare=1;Auto " _
        , _
        "Translate=True;Packet Size=4096;Workstation ID=40-02;Use Encryption for Data=False;Tag with column collation when possible" _
        , "=False;Initial Catalog=Marketing"), Destination:=Sheets("Sheet1").Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("""Marketing"".""dbo"".""03""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\m\My Documents\My Data Sources\Marketing.odc"
        .ListObject.DisplayName = "Table_Marketing_1"
        .Refresh BackgroundQuery:=False
    End With

End Sub

 

[xenou]

This looks to be a live data connection to me - though you seem to have some connection information stored locally (probably the query instructions). There are various ways of "refreshing" (pushing data to the side, overwriting it) - if you are unsure, experiment with those options. ( your macro has recorded your choice as ".RefreshStyle = xlInsertDeleteCells")

I've never been able to make sense of what these options mean - this thread has a response suggesting using the "third option" as a general rule:
http://www.accountingweb.co.uk/item/167321

 

[parry]

Hi, it looks like your connecting to a SQL Server db
Provider=SQLOLEDB.1

The ODBC file C:\Documents and Settings\m\My Documents\My Data Sources\Marketing.odc contains details on the connection to the SQL Server db. This will also be referenced under the Control Panel > Administrative Tools > Data Sources.

cheers,
Graham