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!
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
' Column Labels
.Value = Array("RankLvl", "Geo", "Total")