Problem with ODBC Query

S

Shilps

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
 
D

Don Guillett

Try removing the " : "


--
Don Guillett
SalesAid Software
(e-mail address removed)
Shilps said:
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
 
S

Shilps

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top