Hi everyone, I'm stuck on a little problem. I am running some Access queries and tables from Excel vba, and returning the results to specific cells in Excel. However, although the queries run correctly, the acCmdSelectAllRecords always returns the number of rows from the first query. These should all be different. Any ideas anyone? My code is below:
Code:
Sub TESTING()
Dim accApp As Object
Set accApp = GetObject("c:\Property\Property.mdb")
'Query1 - should return 68 rows
accApp.DoCmd.OpenQuery "022 Property Query"
accApp.DoCmd.RunCommand acCmdSelectAllRecords
accApp.DoCmd.RunCommand acCmdCopy
Workbooks("Property Report.xls").Sheets("Input Sheet").Activate
With Sheets("Input Sheet")
.Range("A99").Activate
.PasteSpecial
End With
'Query2 - should return 8 rows
accAppDoCmd.OpenQuery "021 Property Query"
accApp.DoCmd.RunCommand acCmdSelectAllRecords
accApp.DoCmd.RunCommand acCmdCopy
Workbooks("Property Report.xls").Sheets("Input Sheet").Activate
With Sheets("Input Sheet")
.Range("A107").Activate
.PasteSpecial
End With
'Query3 - should return 4 rows
accApp.DoCmd.OpenTable "304 Property Table"
accApp.DoCmd.RunCommand acCmdSelectAllRecords
accApp.DoCmd.RunCommand acCmdCopy
Workbooks("Property Report.xls").Sheets("Input Sheet").Activate
With Sheets("Input Sheet")
.Range("A125").Activate
.PasteSpecial
End With
accApp.DoCmd.Quit
Set accApp = Nothing
End Sub