Add QueryTable in code

[Jim Hughes]

In Excel VBA code, Runquery works, but Runquery2 does not.

Runquery2 gives a "Application-defined or object-defined error" on the Set
oQuery line

I would rather use the Runquery2 syntax as it is much shorter!

Any ideas why it is failing?


Public Sub runquery()
' required Tools | Reference to Microsoft Activex Data Objects Library
On Error GoTo e
Dim oQuery As QueryTable

Dim connstring As String
connstring = "Provider=sqloledb;Data Source=htivm;Initial
Catalog=pubs;Integrated Security=SSPI;"
Dim sql As String
sql = "SELECT * FROM AUTHORS"

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = connstring
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, conn, adOpenDynamic, adLockReadOnly
Set oQuery = Sheet1.QueryTables.Add(rs, Sheet1.Range("A1"))
oQuery.Refresh
rs.Close
conn.Close
Exit Sub
e:
Debug.Print Err.Description
End Sub

Public Sub runquery2()
On Error GoTo e
Dim oQuery As QueryTable

Dim connstring As String
connstring = "Provider=sqloledb;Data Source=htivm;Initial
Catalog=pubs;Integrated Security=SSPI;"
Dim sql As String
sql = "SELECT * FROM AUTHORS"

Set oQuery = Sheet1.QueryTables.Add(connstring, Sheet1.Range("A1"), sql)
oQuery.Refresh
Exit Sub
e:
Debug.Print Err.Description
End Sub

 

[Dick Kusleika]

Jim

I think you have to have "OLEDB;" as the first field of connstring

Public Sub runquery2()
On Error GoTo e
Dim oQuery As QueryTable

Dim connstring As String
connstring = "Provider=sqloledb;Data Source=htivm;Initial
Catalog=pubs;Integrated Security=SSPI;"

connstring = "OLEDB;Provider=sqloledb;etc...."

 

[Jim Hughes]

Thank you Dick!

That's the first time that www.ConnectionStrings.com let me down ;(

 

[Dick Kusleika]

Jim

You're welcome. I think it's a curiousity of MSQuery that it needs OLEDB or
ODBC at the start of its connection strings. I guess it needs to know if
you're using OLE or ODBC so you have to tell it. So don't be too hard on
ConnectionStrings.com.

 

[Jim Hughes]

Hard on them, no, I didn't mean to be

But I did forward a copy of this thread to them via email so that they can
include it if they so choose, least I could do after the help their site has
given me in the past! Thanks again!