Needed to refresh data connection (SQL server connection consisting of multiple tables) in my excel worksheet on a regular basis. However, when I perform a "Refresh All" excel basically retrieves all data from the SQl server from scratch and hence takes a long time to retrieve data. I was hoping to speed this by pulling in only the required data and then appending this to my tables in my connection. Is there a way to do this using VBA?

I have an Excel sheet with a connection to a SQL server from which I bring in multiple tables to build dynamic reports. While refreshing the data to bring in new rows to all different tables, excel takes a really long time to bring in the data as it retrieves all rows from the sql server from scratch. I was hoping I could just bring in the new data and then feed it into the tables in my data connection and refresh the data this way using a VBA script. I did come across code to get the required data from the SQL server but didn't find a way to then feed this data back into the tables and refresh the tables this way instead of using the "refresh all" option and avoid having to wait a long time.

Dim sConnString As String
Dim conn As ADODB.Connection

    Dim rs As ADODB.Recordset

    sConnString = "Provider=SQLOLEDB.1;Initial Catalog=WELLTHY_ALL;" & _
             "User ID=username;Password=password;Data Source=NEWINSTANCE;"

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.Open sConnString
    query = "SELECT * FROM table where date >" & " '2019-05-03'"
    Set rs = conn.Execute(query)

I want to then append this data that I got above in rs into the table bought into excel via the connection and avoid the time-consuming refresh all method

0 Answers