Solved

SQL Statement with JOIN in Excel VBA returns no records to Excel

Posted on 2009-04-02
2
559 Views
Last Modified: 2012-05-06
Hi!

I'm trying to pull some records from Access 2007 into Excel 2007 using a SQL statement with a JOIN (shown below) in VBA. I've been able make a SQL statement with no JOINs (also shown below) work just fine.  And both sets of SQL statements work fine and return records in Access. So, what am I doing wrong in the JOIN statement in VBA?

BTW, I tried to alias the table names to shorten the statement, but after I got no records back the the first time, I restored full table names.  Since I'm still getting no records, that was apparently not the problem.

Thanks in advance for your help!

Joel
Sub GetRankData()
 

' Variables

    Dim MyConnect As String

    Dim MyRecordset As ADODB.Recordset

    Dim MySQL As String, strEmptyRS As String

    

' Connection String

    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= C:\DbaseTest\RankStaging.accdb"
 

' This statement works just fine

 MySQL = "SELECT 'Natl' AS RankLvl, Geo, SalesAmt AS Total FROM tblTestSalesScorecard WHERE Geo Like 'Terr%' " & _

         " AND Period = 'YTD' AND Type = 'Paper' ORDER BY 'Natl', SalesAmt DESC"

         

'  But this statement with a JOIN that ties in a different level of geographical hierarchy returns no records

 MySQL = "SELECT tblLookupTerritory.District AS RankLvl, tblStagingSalesVarTest.Geo, tblStagingSalesVarTest.Var " & _

         "FROM tblStagingSalesVarTest INNER JOIN tblLookupTerritory ON tblStagingSalesVarTest.Geo = tblLookupTerritory.Terr " & _

         "WHERE tblStagingSalesVarTest.Geo Like 'Ter*' And tblStagingSalesVarTest.Period = 'YTD' And tblStagingSalesVarTest.Type = 'Paper' " & _

         "ORDER BY tblLookupTerritory.District, tblStagingSalesVarTest.Var DESC"
 

' Create recordset

    Set MyRecordset = New ADODB.Recordset

    MyRecordset.Open MySQL, MyConnect, adOpenUnspecified, adLockReadOnly
 

' Copy recordset to Excel

     Sheets("Test").Select

     ActiveSheet.Range("W2").CopyFromRecordset MyRecordset
 

' Column Labels

    With ActiveSheet.Range("W1:Y1")

        .Value = Array("RankLvl", "Geo", "Total")

        .EntireColumn.AutoFit

    End With

    

    MsgBox "Done!"
 

End Sub

Open in new window

0
Comment
Question by:jm302
2 Comments
 
LVL 1

Accepted Solution

by:
witwit earned 500 total points
ID: 24060803
Simple, replace 'Ter*' with 'Ter%'

:-)
0
 

Author Closing Comment

by:jm302
ID: 31565815
Bah!  How embarrassing! Well, I can still swallow my pride and be thankful to get the solution.  So, thank you very much!  :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

861 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

25 Experts available now in Live!

Get 1:1 Help Now