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.
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top