Member Avatar for shahriar2403

I need to get a total of a specific column of a table of access db in a text box in my visual basic application. What should be the code? I am using adodc to connect with the database.
Help urgent please..........

  • 4 Contributors
  • 7 Replies
  • 1K Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by choudhuryshouvi

Dani AI

Generated

Both approaches already shown are valid: 's loop works and is straightforward for small tables, while and were right to point out that an aggregate run by the database engine is faster and uses far less memory on large tables. The recommended pattern is to let Access compute the sum and then read the single result into the textbox.

Example (VB6 with ADO — put in Form_Load; requires a reference to Microsoft ActiveX Data Objects):

' Form_Load
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim total As Currency

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\rate.mdb;"

sql = "SELECT Nz(Sum([basic_pay]),0) AS Total FROM [report];"
Set rs = cn.Execute(sql)

If Not rs.EOF Then total = rs!Total
Text1.Text = FormatCurrency(total)

rs.Close: cn.Close
Set rs = Nothing: Set cn = Nothing

Notes and pitfalls: Nz() ensures a zero is returned instead of NULL when there are no rows; without it the field can be Null. If the DB file is .accdb or running on newer/64-bit systems, use the ACE provider instead of Jet (or run the app as 32-bit). For bound ADODC scenarios the same aggregate SQL can be used as the RecordSource and read from the control’s Recordset, but for large tables the direct query approach above is best. Also pick an appropriate numeric type (Currency or Double) to avoid rounding surprises when summing money.

Recommended Answers

All 7 Replies

Member Avatar for debasisdas

Simply try using SUM(column_name) using SQL query.

Member Avatar for shahriar2403

Would you please explain a bit more? I am not so expert in codeing. In formload i want to get it. Table name is report, Db name rate. So then......what?

Member Avatar for choudhuryshouvi

try this code. hope this will help you.........

Dim db As Database
Dim rs As Recordset
Dim tot As Double

Set db = OpenDatabase(App.Path & "\rate.mdb")
Set rs = db.OpenRecordset("report", dbOpenTable)

tot = 0

If rs.RecordCount > 0 Then
    rs.MoveFirst
    While Not rs.EOF()
        tot = tot + rs!basic_pay
        rs.MoveNext
    Wend
End If

Text1.Text = tot
Member Avatar for cometburn

in your sql query...

SELECT SUM(<column_name>) as SumOfColumn from <tablename>

Member Avatar for shahriar2403

many many thanks to all. thistime shuvi's code works well. thx again shuvi.

Member Avatar for cometburn

Querying is better than looping on a recordset since this will take a certain amount of time...

Lets just say you have 100,000+ records, then you will count them 1 by 1?

Member Avatar for choudhuryshouvi

many many thanks to all. thistime shuvi's code works well. thx again shuvi.

glad to hear that. but cometburn's syntax is also correct. you can apply that also.
(completely upto you).

ok....
if u got your answer then mark this thread solved.

regards
Shouvik

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.