Excel 2010 Run Time Error 1004 when trying to Import Access Query
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
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?