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

• Index already exists message when linking tables
• Coordinates of the third point
• On Error code
• WeekNumber(Date)
• Command line parameter
• Change report size
• ShopOnline4All.com’s Special Discounted Items
• Aquarium Equipment Needs
• Secure a database
• Poor man’s mastering: Mastering without a plugin

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

Random Posts

• Gibson plus solide que Fender?
• Connecting with tools such as cash drawer
• Ma webradio de blues: 1203 the Blue Cybersouk
• Des légumes qui poussent à l’ombre?
• Program Header
• Dirty Flag for unbound form
• Nettoyage de la touche
• Template driven websites
• Access Speed?
• On Error code

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

Random Posts

• Program Header
• Corporate gifts online
• Truncate long path names
• will that speed things Up
• A2K Performance Tips
• Poor man’s mastering: Mastering without a plugin
• Connecting with tools such as cash drawer
• WeekNumber(Date)
• Des légumes qui poussent à l’ombre?
• Create a linked table with VBA

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

• BuildQuery()
• will that speed things Up
• AddReference()
• Disable shift key
• All books by author
• I like this game
• Opening an ADO recordset
• Performance
• Format a floppy in ACCESS without creating a batch file
• Nettoyage de la touche

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

• BuildQuery()
• Relink all tables
• WeekNumber(Date)
• Max number of users in an Access 97 Database
• Des légumes qui poussent à l’ombre?
• Splitting File and Path of folder: NoPath() & FilePath()
• ADO program calling an Oracle PL/SQL stored procedure
• Guitare-Live 2004/2007
• Secure a database
• All books by author