Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1854
  • Last Modified:

Query of SQL database incorrectly returning no records using ADO from VBA in Excel

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:

Sub QueryRecords()
 
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;" & _
    "Data Source=ifppa-db"
cnn.Open sConnString

strSQL = "SELECT ID, SolnName FROM Solutions"
Debug.Print strSQL
Set rst1 = New ADODB.Recordset
rst1.Open strSQL, cnn
Debug.Print rst1.RecordCount

cnn.Close
 
Set cnn = Nothing
Set rst1 = Nothing
End Sub

Open in new window


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.

USE [Merch]
GO

/****** Object:  Table [dbo].[Solutions]    Script Date: 10/29/2013 6:44:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Solutions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SolnName] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


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.

Any ideas?
0
StudmillGuy
Asked:
StudmillGuy
  • 2
  • 2
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
Ok, I'm not much of a programmer myself, but it's not because you declare rst1 as a NEW ADODB.Recordset twice is it? Once in the variable declaration and again when you're creating the object.
0
 
StudmillGuyAuthor Commented:
Nope.  That's not it.  I set the top one to
Dim rst1 As New ADODB.Recordset

Open in new window

and i still have the same problem.  I know the second one has to be like that.

Thanks for taking a stab at it though.
0
 
Koen Van WielinkIT ConsultantCommented:
Is there a way for you to verify that the connection was actually established correctly? Perhaps check if a connection actually exists on the database?
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

The RecordCount property will always return -1 for a forward-only cursor, the actual count for a static or keyset cursor, & -1 or the actual count for a dynamic cursor.

I would suggest this change to your code (line 18):

From...
rst1.Open strSQL, cnn

To...
rst1.Open strSQL, cnn, 3

(Where 3 is the value of adOpenStatic).


See also:
[ http://msdn.microsoft.com/en-us/library/office/aa141422(v=office.10).aspx ], &
[ http://msdn.microsoft.com/en-us/library/windows/desktop/ms681510(v=vs.85).aspx ].

BFN,

fp.
0
 
StudmillGuyAuthor Commented:
Bingo fp.  I know nothing about ADO and have pieced together everything I've done from snippets I've found on the web.  The links you've provided have explained the problem to me.  Thank you so much for including them.  It kind of makes sense now.

Do you have any suggestions for how I posted my question to make it easier for experts to zero in in topics within their area of expertise?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now