Whatever the problem is is going to be very simple, so please be gentle. I have a simple query of a table on SQL Server 2012 from VBA in Excel. I have already successfully inserted hundreds of rows of data into a table using this same set up, but now, trying to do a simple query, I'm stymied.
My code is as follows:
Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim sConnString As String
Dim strSQL As String
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Merch;" & _
strSQL = "SELECT ID, SolnName FROM Solutions"
Set rst1 = New ADODB.Recordset
rst1.Open strSQL, cnn
Set cnn = Nothing
Set rst1 = Nothing
I've tried everything I can think of, but the Debug.Print always prints -1 for the RecordCount. There are 4 records in it. If anyone is interested, here's the SQL to create the table.
/****** Object: Table [dbo].[Solutions] Script Date: 10/29/2013 6:44:37 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Solutions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SolnName] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
I've tried copying and pasting from the SQL to make sure my spelling is correct. I've tried brackets and no brackets, fully qualifying the table name. No luck.