Solved

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

Posted on 2013-10-29
5
1,432 Views
Last Modified: 2013-10-30
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
Comment
Question by:StudmillGuy
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39610617
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
 

Author Comment

by:StudmillGuy
ID: 39610631
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39610810
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39611297
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
 

Author Closing Comment

by:StudmillGuy
ID: 39611590
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now