Managing querytables, connections, recordsets

[PEDRO ABRANTES]

HI ALL, FIRST OF ALL, SORRY ABOUT MY ENGLISH, IM NOT AMERICAN.

I HAVE AN ADODB.CONNECTION, FROM I QUERY DATA USING AN ADODB.RECORDSET, SO I ADD A QUERYTABLE TO PASTE THIS DATA ON MY SHEET USING REFRESH.

I HIGHLY WANT THAT THIS QUERYTABLE REFRESHS ALONE, SO THE DATA IS UPDATED WITHOUT THE NEED TO REDO THE VBA ROUTINE AND DO EVERYTHING AGAIN.

I KNOW THAT WHEN I ADD A QUERYTABLE, EXCEL CREATES A CONNECTION WITH EXTERNAL DATA (THAT I THINK IT'S MY RECORDSET), AND I CAN CHANGE MY CONNECTIONS PROPERTIES AND EXTERNAL DATA PROPERTIES.
THE PROBLEM IS: I CAN ONLY CHANGE THESE PROPERTIES ON VBA, VIA COMMANDS, CAN'T CHANGE MANNUALY AND THE DATA DOESN'T UPDATES.

I THINK THAT THE PROBLEM IS HOW I AM CONFIGURING THE CONNECTION, THE RECORDSET AND THE QUERYTABLE.

I STILL DON'T KNOW WHICH ONE OF THE PROPERTIES I SHOULD ALLOW, I SHOULD DISABLE, I SHOULD SET PROPERLY. CAN ANYONE HELP ME?

AT THIS MOMENT, I SET MY CONFIGURATIONS LIKE THIS


Dim conn_sinacor As ADODB.Connection
Dim rs As ADODB.Recordset
Dim qtbData As Excel.QueryTable




Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer 'I DONT KNOW IF I SHOULD PUT THIS OR adUseClient or else
rs.CursorType = adOpenDynamic 'I DONT KNOW IF ITS CORRECT TOO

Sql = "MY QUERY"


Set conn_sinacor = New ADODB.Connection


With conn_sinacor


.CursorLocation = adUseServer 'HERE I DONT KNOW IF ITS CORRECT
.Open "Provider=...;Data Source=...;User Id=...;Password=...;"



End With




rs.Open Sql, conn_sinacor

Set qtbData = ThisWorkbook.Sheets("ATUALIZA").QueryTables.Add(rs, ThisWorkbook.Sheets("ATUALIZA").Range("A1:D30000"))


With qtbData


.BackgroundQuery = True
.EnableRefresh = True
.RefreshPeriod = 1
.Refresh

End With



HERE I DONT KNOW IF I SHOULD CLOSE THE CONNECTION, THE RECORDSET, SET THEM TO NOTHING.

AFTER THE SUB RUNS, THE PROPERTIES OF MY EXTERNAL DATA, KEEPS THE PROPERTIES THAT I SET TO MY QUERYTABLE. THE BACKGROUND REFRESH IS ALLOWED AND IT REFRESHES EVERY MINUTE. BUT I CANNOT CHANGE IT MANNUALY, BUT DOESNT MATTER. BUT, WITH MY CONNECTION PROPERTIES, THE REFRESH EVERY MINUTE IS ALLOWED, BUT THE BACKGROUND REFRESH ISN'T, AND I DON'T KNOW HOW I CAN CHANGE IT, AND HOW IT'S SET. THE ADODB.CONNECTION DOENS'T HAVE A .BACKGROUNDREFRESH PROPERTIE.

CAN ANYONE HELP?

I JUST WANT THAT THE DATA THAT IS CONNECTED TO THE RANGE BACKGROUND REFRESHS EVERY 5 MINUTES WITHOUT THE NEED TO SET A TIMER TO MY SUB TO RUN IT EVERY 5 MINUTES.

THANKS ANYWAY.