Help needed to get total of one field in access dbase table via Excel VBA

[sandrapoh]

Dear All Gurus,
I am trying to do a select query in excel vba like the below and would like to put the result in one of the macro variable in excel vba. But cannot, please advise.

lngAMT = accdb.execute "SELECT Sum(Summary.NQ) AS SumOfNQ FROM Summary HAVING (((Sum(Summary.NQ))<>0));"

Thanks in advance.

Regards
Sandra Poh

 

[namliam]

in order to read values from a query like you want to do you need something along the lines of:

        Dim rs2 As DAO.Recordset
        Set rs2 = CurrentDb.OpenRecordset("select bla bla from bla")
        Debug.Print rs2!SomeColumn
        rs2.Close

If you want to do this in excel, make sure to add the references (Module window menu: Tool > References), the Microsft DAO 3.6 and Microsft access xx object library to make things a little easier on you.

Also instead of currentdb in above code, you will need to define a DB and its location as well to extract the data from it.

 

[sandrapoh]

Dear Namlian,
Thanks for your quick response.
I have done the below as per your guidance.

Set accRS = accDB.OpenRecordset("SELECT Sum(Summary.NQ) AS SumOfNQ
FROM Summary)

varAmt = accRS.Field(1).Value
accRS.Close

But cannot. I would like to put in the value from accRS.Fields(1).Value to varAmt variable. When I run my excel program, it said "Run-time error :3265".

Please guide me again.

 

[namliam]

Pretty sure it is supposed to be accRS.Fields(1) if you want to use that syntax...
Somewhat easier would be accRS!SumOfNQ

If you still have problems please post the full code you are using

 

[sandrapoh]

Dear Namlian,
Thanks again. I here by attached my excel vba and accdb for your further guidance.
Both belong to Version 2007. The objective is to check if certain field in Summary table in msaccess db is 0, I will not execute the next step.

Please search for the statement "varAMT

Regards
Sandra Poh

 

[namliam]

Sandra,

This quickly re-fabricated code seems to work just fine:

Sub test()
            Dim strPath As String
            strPath = ThisWorkbook.Path & "\"

            Set daoWS = DBEngine.Workspaces(0)
            Set daoDB = daoWS.OpenDatabase(strPath & "UploadNew.accdb")
            Set daors = daoDB.OpenRecordset("SELECT Sum(Summary.NQ) AS SumOfNQ FROM Summary")


            Debug.Print daors!SumOfNQ
            MsgBox daors!SumOfNQ
End Sub

 

[namliam]

some alternatives to daors!SumOfNQ

daors.Fields(0)
I forgot that fields is a Zero based array instead of 1.... so the first field is field 0 not field 1
daors.Fields("SumOfNQ")