close connection and query from excel vba

[vineet78]

I am trying to close connection but getting error that data is being refreshed in background

 

[Worf]

Are you working with a very large data set? If not, you can turn background refresh off from the beginning.

 

[vineet78]

Yes it is a very large dataset.

 

[vineet78]

If I set up background query refresh as false then how will i get the data based on query please.
Following is the code i am using to extract data

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbname" _
, _
"Jet OLEDB:Bypass UserInfo Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("SELECT * From [BPC_1] WHERE Region like '" & Reg & "' AND [Zone] like '" & zn & "' AND [Country] like '" & cty & "'AND [Fn L1] like '" & fn1 & "'AND [Fn L2] like '" & fn2 & "'AND [Fn L3] like '" & fn3 & "'AND [Geography] like '" & geo & "' AND [Cate L4] like '" & cat & "'")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.SourceDataFile = _
"\\pnccfsdg01\Dubai_AC_Finance\Dubai AC Controllership\GL Close Accountants\vg\GOE-APAC.accdb"
.ListObject.DisplayName = "Table_ExternalData_14"
.Refresh BackgroundQuery:=True
End With

 

[vineet78]

Thanks Worf !! _
I could solve it now by putting backgroun refresh as false , earlier was missing it

Cheers

Thanks a tonne !!