Using Add Method of QueryTables gives error
 

Hi All
I am trying to create a QueryTable using Add Method but it is giving

Run-Time Error "458": Variable uses an automation type that is not supported in Visual Basi
the code is given belo

direc = Worksheets("QUERY_BUILDER").Range("BH1").value 'BH1 is the database pat
datab = Worksheets("QUERY_BUILDER").Range("BH2").value 'BH2 is the database nam
Dim connstring(), sqlstring() As Varian
sqlstring = Array("SELECT DISTINCT " & field & " FROM `" & datab & "`." & table_name & " " & table_name

connstring = Array(Array("ODBC;DSN=MS Access Database;DBQ=" & datab & ";DefaultDir=" & direc & ";DriverId=25;"
), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")

With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=Range("AR1"), Sql:=sqlstring
.Refres
End Wit

It gives error on lin
With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=Range("AR1"), Sql:=sqlstring

Please tell me what am I doing wrong
TI
Shilp

Using Add Method of QueryTables gives error
 

Shilps

I don't know for sure, but I would try

connstring = Array(Array("ODBC;DSN=MS Access Database;DBQ=" & datab &
";DefaultDir=" & direc & ";DriverId=25;" _
), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))


connstring=Array(Array("ODBC;DSN=MS Access Database; DBQ=" & _
direc & "\" & datab & "Default....

Maybe you need the path in the DBQ argument


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com