Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2013-10-29
5
Medium Priority
?
2,050 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 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 2000 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 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