?
Solved

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

Posted on 2009-04-02
2
Medium Priority
?
571 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
[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 Comments
 
LVL 1

Accepted Solution

by:
witwit earned 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Suggested Courses

777 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