jm302
asked on
SQL Statement with JOIN in Excel VBA returns no records to Excel
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER