problem with background query
 

Hi everyone,

I have a wb that runs a query from code and gets selected data from a
network wb. We added another column to the external wb so i altered
the query code to accomodate this, however although the query still
runs the new data is not retrieved along with it. Can anbody suggest
why this might be and how I can alter it? (I have checked the column
exists and that the column name is correct).

This is the code I have now;


BDENO = Sheets("DETAILS").Range("C1").Value
PATH = Sheets("DETAILS").Range("U1").Value
FILNAME = Sheets("DETAILS").Range("C2").Value
FILNAMES = FILNAME & "$"
PATHANDNAME = Sheets("DETAILS").Range("U1").Value & "\" & FILNAME
PATHANDNAMEANDTYPE = Sheets("DETAILS").Range("U1").Value & "\" &
FILNAME & ".XLS"
Dim shtname As String
If Dir(PATHANDNAMEANDTYPE, vbNormal) = vbNullString Then
MsgBox ("There has been an error, please check your geolink / network
connection and try again")
Worksheets(shtname).Visible = False
Exit Sub
Else
If FILNAME = "South" Then
shtname = "Sheet1"
Sheets(shtname).Visible = True
Sheets("Sheet1").Activate
ActiveSheet.AutoFilterMode = False
Sheets("Sheet1").Range("A1").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=Excel Files;DBQ= " & PATHANDNAMEANDTYPE &
";DefaultDir= " & PATH &
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;"
..CommandText = Array( _
"SELECT `" & FILNAMES & "`.`Siebel Id`, `" & FILNAMES & "`.Outlet, `"
& FILNAMES & "`.`Street Address`, `" & FILNAMES & "`.Locality, `" &
FILNAMES & "`.Town, `" & FILNAMES & "`.County, `" & FILNAMES &
"`.`Outer Postcode`, `" & FILNAMES & "`.`Inner Postcode`, `" &
FILNAMES & "`.`Phone No#`, " _
, _
"`" & FILNAMES & "`.`Outlet Status`, `" & FILNAMES & "`.Tenure,
`" & FILNAMES & "`.`Primary Streetmap`, `" & FILNAMES & "`.`Last BDE
Visit`, `" & FILNAMES & "`.Owner, `" & FILNAMES & "`.Operator, `" &
FILNAMES & "`.`BDE Territory`, `" & FILNAMES & "`.Region, `" &
FILNAMES & "`.Division, `" & FILNAMES & "`.`Contact Full Name`" &
Chr(13) & "" & Chr(10) & "FRO" _
, _
"M `" & PATHANDNAME & "`.`" & FILNAMES & "` `" & FILNAMES & "`" &
Chr(13) & "" & Chr(10) & "WHERE (`" & FILNAMES & "`.`BDE Territory`='"
& BDENO & "')" _
)
.Refresh BackgroundQuery:=False
End With

Apologies for the formatting. The part I added was the `" & FILNAMES &
"`.`Contact Full Name`" .