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

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...."

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
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Thank you Dick!

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

"Dick Kusleika" wrote in message
...
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...."

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
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

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. :)

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

Jim Hughes wrote:
Thank you Dick!

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

"Dick Kusleika" wrote in message
...
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...."

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
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

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!

"Dick Kusleika" wrote in message
...
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. :)

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