test

[minette]

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:

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

 

[minette]

Access queries from Excel

Apologies, wrong title......