FredixBlog

Oracle’s stored procedures are a bit different than SQL Server or Sybase. With them, returning records is obvious but we can get some trouble if we want to do the same with an Oracle stored procedure. The use of a package is necessary. You will find here an example on how to manage this issue.
See here the EE topic:

http://www.experts-exchange.com/Databases/MS_Access/Q_20252878.html

Random Posts

It is sometimes needed to show the running total of the records. On a report, there is no problem as this feature in built-in. But on a form, a bit of coding is needed.
Here is a function that will work on any form. The most of it is that you can sort or filter the records, the running total still works because it works with the form’s recordsetclone.
Parameters of the function:
[Amountfield] is the field name we want to totalize
[Keyfield] is the field name of the primary key of your form’s table or query, or simply an unique field.

Public Function RunTotal(AmountField, KeyField)
Dim SumRs As Recordset
Dim frm As Form
Dim RunningValue As Double
Dim IsCancelled As Boolean

Set frm = Forms(AmountField.Parent.Name)
Set SumRs = frm.RecordsetClone

With SumRs
    ' Setting first amount
    RunningValue = 0
    On Error Resume Next
    .FindFirst "True"
   
    ' Summing all other
    While .Fields(KeyField.Name) <> frm.Controls(KeyField.Name) And Not IsCancelled
        RunningValue = RunningValue + .Fields(AmountField.Name)
        On Error Resume Next
        .FindNext "True"
        If Err <> 0 Then IsCancelled = True
        On Error GoTo 0
    Wend
       
    ' Do the current one
    RunningValue = RunningValue + .Fields(AmountField.Name)
End With

Set SumRs = Nothing
RunTotal = RunningValue
End Function

• AddReference()
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Dirty Flag for unbound form
• Custom Autonumber Access 2000
• Create ODBC DSN

The dirty flag doesn’t work when the form is unbound. Here the technique is to use the validation rule like an event in order to set the flag. Easy: A simple function returning the control’s text itself can do the job!
I bother for the performance so my code also checks for the existence of a former validation rule. Setting properties of many controls could make the form open slower without this check.
Once the form is first opened with this method, then saved, there is no need of re-doing the function so the form opens faster because for sure testing for the existence of SetDirty validation rule will make an exit.

Private Sub Form_Open(Cancel As Integer)
Dim ctr As Control
Dim frm As Form
Set frm = Me
For Each ctr In frm
    On Error Resume Next
    if ctr.validatonrule="SetDirty()" then ' Already done, let's not smash an open door!
        exit sub
    On Error Goto 0
    if isempty(ctr.Validationrule) then
        ctr.ValidationRule = "SetDirty()"
    end if
Next ctr
End Sub

And, of course don’t forget the SetDirty function:

Public Function SetDirty()
    IsDirty = True
    SetDirty = Me.ActiveControl.Text
End Function

The full EE thread can be read here:

http://www.experts-exchange.com/Databases/MS_Access/Q_11879478.html.html

• Site Map
• Running total on a form
• Replicate a database

When migrating a database from A97 to Access 2000, it is very possible that you experience a damn slow down of your database. For me, it even turned into something very critical so I took the time to study this issue closer. Some EE experts contributed with very useful stuff too and I don’t find a better place for a list of Access 2000 performance tips.
See here the EE topic:

http://www.experts-exchange.com/Databases/MS_Access/Q_20264154.html

Still alive after two years, this is a must read.

Random Posts

Quertion: I am useing Access 97 to operate a table with about 1 million records, and 30 queries, and about 40 reports, and 10 of these reports use subreports.
When I try to run these reports it is very, very , very SLOW. 5-10 Minutes.
My computer is a 1gig 256Mg Ram 20 Gig HD
If I upgrade to Access 2000 or XP will that speed things Up?
Fredix gave this response on 1/4/2002:
Hi!

There is not much to do with so many records, except focus on indexes to optimize the queries:

1) Where you have a relation between two tables, create an index for the MASTER column. Don’t create an indes on the CHILD column: This may make things slow down in some cases.

2) Where you have a selection criteria, create an index.

3) Avoid criterias like: LIKE “*blah”. LIKE “blah*” is all right.

I have one database big like yours. Yes, everything is slow, fortunately my client is very patient. I think that for such an amount of data, Access is too small. I would develop with a more powerful database like Oracle, SQL Server, Sybase, etc..

While writing, there are some more improvements ideas:

4) Minimize the field sizes, in order to minimize the table sizes: use Byte datatype for integers if possible. look for the largest text data and set the column size accordingly.

5) Split your tables: Create one for the most used columns, and one for the least used.

I don’t about Access 2002, but I’ve found 2000 slower (!) than 97, as it uses much more memory. 256Mb may be allright, but not for 1 mio records.

Regards.

Random Posts