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