Managing querytables, connections, recordsets

PEDRO ABRANTES

New Member
Joined
Jun 11, 2014
Messages
1
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,176,147
Messages
5,901,611
Members
434,906
Latest member
butterthemuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top