Problem with ODBC Query

[Guest]

Hi,
I have a code as given below:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" + CStr(datab) + ";DefaultDir=" + CStr(direc) _
+ ";DriverId=25;"), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("AR1"))
.Sql = Array("SELECT DISTINCT " + CStr(field) + _
"" & Chr(13) & "" & Chr(10) & "FROM `" + CStr(datab) + "`." + CStr(table_name) _
+ " " + CStr(table_name) + "")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False

Actually the result of this query is stored in column AR and table name is the option selected from the ComboBox. The problem is that for all but few options in the ComboBox, this query runs fine. But for few options , it gives error on line

.Refresh BackgroundQuery:=False

Any idea why?
TIH
Shilps

 

[Don Guillett]

Try removing the " : "


--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi,
I have a code as given below:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;DSN=MS

Access Database;DBQ=" + CStr(datab) + ";DefaultDir=" + CStr(direc) _

+ ";DriverId=25;"), Array("FIL=MS

Access;MaxBufferSize=2048;PageTimeout=5;")), _

Destination:=Range("AR1"))
.Sql = Array("SELECT DISTINCT " + CStr(field) + _
"" & Chr(13) & "" & Chr(10) & "FROM `" + CStr(datab) + "`." + CStr(table_name) _
+ " " + CStr(table_name) + "")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False

Actually the result of this query is stored in column AR and table name is

the option selected from the ComboBox. The problem is that for all but few
options in the ComboBox, this query runs fine. But for few options , it
gives error on line

 

[Guest]

Sorry Don, but it didn't help. The point to be noted here is that it is giving error only for a single table of the same database. This table was added later on. Can this be a reason? If yes then whats the solution
TIH
Shilps