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