Using Add Method of QueryTables gives error

S

Shilps

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
 
D

Dick Kusleika

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
 

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