Solved

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

Posted on 2013-10-29
5
1,602 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Power Query Grouping By 2 19
Hash on Excel 13 43
Sql server Error message 3 16
How to trim a value in SQL 2 25
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

749 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