Solved

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

Posted on 2013-10-29
5
1,660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

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 13

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

724 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