I used to be able to import an Access query into Excel with the following code (relevant snippet):

strConnection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=S:\2012 Financials\Income Statements.accdb"
Set rngDestination = Sheet1.Range("A1")

Set qryTest = Sheet1.QueryTables.Add(strConnection, rngDestination)
Application.ScreenUpdating = False

qryTest.CommandText = "qryTest"
qryTest.CommandType = xlCmdTable
qryTest.Refresh

But now with MS Office 2010 it's a hit and miss; sometime I get a "1004" run time error message stating that "the database could not be opened"....does MS Excel 2010 require a different/more efficient command for accessing the external data from Access?