VBA macro refuses to copy from SQL to Excel, even though was previously functional.

Previously I created a VBA macro that allows copying data from SQL 2008 to Excel sheet. And it works perfectly fine. However, when I try to use the same macro on a new Excel sheet connecting to a new database + SQL statement, a whole bunch of issues happens.

Here is the original code
Sub Main()

On Error GoTo EH
    'Step 1: Create the Connection String with Provider and Data Source options
    Dim sSQLQry As String
    Dim ReturnArray
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim DBPath As String, sconnect As String
    sconnect = "driver={SQL Server};server=server;database=db1;uid=ID;pwd=PWD"
    'Step 2: Open the Connection to data source
    conn.Open sconnect
    'Step 3: Create SQL Command String
    sSQLSting = "SELECT DayID, DieID, ActualQty FROM [table1].[dbo].[tblDataBody] order by DayID, DieID desc"
    'Step 4: Get the records by Opening this Query with in the Connected data source
       rs.Open sSQLSting, conn

    'Step 5: Copy the reords into our worksheet
        Sheet1.Range("A10").CopyFromRecordset rs
    'Step 6: Close the Record Set and Connection

           Exit Sub

     MsgBox Err.Description
End Sub

Open in new window

I have personally tested this on my new Excel sheet 1st and it worked normally. I even made sure to set the proper references similar to my old Excel sheet.

First I try to implement the whole SQL statement into the code:

DB path:
sconnect = "driver={SQL Server};server=server2;database=DB2;uid=ID;pwd=PWD"

Open in new window

sSQLSting = "select A.[Inv No], A.[Project No], [Date + Time], A.[Description], A.[Problem + Repair Details], A.[Status], A.[Accumulative Stroke], A.[Preventive Stroke], A.[PIC] from [SQLIOT].[dbo].[Die_Main] A left join (select [Inv No], max(Date + Time) as [Date + Time] from [SQLIOT].[dbo].[Die_Main] group by [Inv No]) B on A.[Inv No] = B.[Inv No] where [Date + Time] = Date + Time Order by A.[Status], A.[Date]desc"

Open in new window

I tried the SQL on Management Studio and it can show the data proper. But in my Macro, nothing happens when I click it. Even on Debug it shows the code passing through without error but nothing shows up in Excel.

Because of that I tried a simpler SQL:
sSQLString = "select [Project No] from [SQLIOT].[dbo].[Die_Main]

Open in new window

Once again tested on Management Studio, no problem. But once again Excel doesn't show anything.

I've been struggling with this for 2 days now and I'm still getting nowhere...
Hans J.HauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
So you mean that the code runs without producing an error i.e. connects to the database, opens the recordset but copies nothing. Right?
Maybe it's because you are not specifying the LockType and CursorType so try it like this and see if the data is returned.

rs.Open sSQLSting, conn, adOpenForwardOnly, adLockReadOnly

Open in new window

Also, are you sure that your select query is valid and returns data? To check if the recordset contains some data try below...

rs.Open sSQLSting, conn, adOpenStatic, adLockReadOnly
MsgBox rs.RecordCount

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
Do you get any errors when you remove/comment out this?
On Error GoTo EH

Open in new window

Hans J.HauAuthor Commented:
@Subodh Tiwari (Neeraj)

I ended up using

rs.Open sSQLSting, conn, adOpenStatic, adLockReadOnly

Open in new window

now it works fine.

But I wonder why exactly only now it needs this. The last time I didn't have this issue.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe those arguments were not required with SQL 2008, that's what I guess.

BTW adOpenForwardOnly is good enough instead of adOpenStatic.

Here is a link describing about all the possible CursorTypes.
Hans J.HauAuthor Commented:
In both cases I was using SQL 2008.

Also I tried adOpenForwardOnly first. It stops at the 3rd column.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
And referencing the same version of Microsoft ActiveX Data Objects Library?
Anyways, passing all the arguments required is always good.
Please continue with what worked for you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.