Results 1 to 9 of 9
  1. #1
    BGF is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    4

    Retrieve Data Too Slow

    I am running a data base that as I update a field of no good part numbers it retrieves the production quantity and then calculates a percentage. This opperation currently takes way too long to complete. The vba code is shown below. Very simple code. It also seems that the entire database runs slow just opening forms and other simple processes sometime. Any ideas what to do. Or is access just no able to handle this amount of data. It is wierd sometimes it seems the entire database runs completely fine but this is rare.

    Private Sub PorosityQty_Change()
    Me.RetriveProdData
    Me.PercentCal


    End Sub
    Private Sub PorosityQty_Exit(Cancel As Integer)
    Me.RetriveProdData
    Me.PercentCal
    End Sub

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    It is Very simple code. but
    what is PorosityQty?
    what and how does RetriveProdData do?
    what and how does PercentCal do?
    how many records do you have, how many fields are in you table?

    When no other user is using the database, it will run much faster.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It should also be noted that those processes will probably run at least twice. The change event will fire with every keystroke, so if I type 10 they will run twice. They will then run again when I exit the field. Most likely you only want them in the after update event of the control, though obviously that depends on exactly what they do.

    You also ask if Access can handle "this amount of data" but don't give us any idea what that amount is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    BGF is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    4
    Sorry busy day should have put some of that info in my first post. the Table and of Production quantity has 15 fields and currently at this time has 5641 records. I did not create this db or code and the person who did is not here any more. From what I can tell after an update of the porosity or leakage qty (which is the number of defect parts from a certian machine and day) is updated the db then retreives the total number of parts made from that day from th production table. Then the percentage is calulated from the # of ngs to the total parts made. My form that i work with is the defect data entry form which is another table and just uses the production qty to update the percentage of no good parts.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    5600 records is trivial compared to what Access can handle. Certainly part of the problem is that the processes are likely running too often. It might help to see those functions to see what they're doing. Perhaps we can see a way to speed them up.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    BGF is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    4
    what do you mean see those functions? What exactly would like to see?

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    see those functions:
    Me.RetriveProdData
    Me.PercentCal

    see what they do, maybe they steal the time.

  8. #8
    BGF is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    4
    Ok sorry did not understand what you wanted. Here is the retrieve function:


    Public Sub RetriveProdData()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb ' This is the database you are currently working on
    Set rs = db.OpenRecordset("Select * FROM ProductionQuantities " & " WHERE [Model] = '" & Me.[Model] & "'" & " and [ProductionDate] = #" & Me.[DieCastDate] & "#" & " and [Shift] = '" & Me.[DieCastShift] & "'")
    If Not (rs.BOF And rs.EOF) Then
    Me.DieCastQty = rs!NumberOfGoodShots.Value
    Else

    Me.DieCastQty = ""
    End If


    rs.Close

    Set rs = Nothing
    Set db = Nothing
    End Sub

    And the calculate function:

    Public Sub PercentCal()
    Me.LeakPercent = (Me.LeakQty) / (Me.DieCastQty)
    Me.PorosityPercent = (Me.PorosityQty) / (Me.DieCastQty)
    End Sub

    this one does not even work all the time so I am pretty sure the retrieve is where it boggs down.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I see a couple of minor issues, but nothing that would cause noticeable speed problems. I can open a recordset like that on a table with a million records in it and it would run instantly. Is the db split (front end on PC, back end with data on server)? Does this help?

    http://www.granite.ab.ca/access/performancefaq.htm

    The couple of minor issues I noticed:

    1) Just select the field you need instead of all fields (this might be noticable):

    SELECT NumberOfGoodShots FROM...

    2) Get rid of the extra concatenation (this is just a few milliseconds):

    "...ProductionQuantities " & " WHERE..."

    can simply be

    "...ProductionQuantities WHERE..."

    Me.[Model] & "'" & " and...

    can simply be

    Me.[Model] & "' and...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search and Retrieve
    By sadath in forum Access
    Replies: 3
    Last Post: 07-02-2010, 04:54 AM
  2. Retrieve similar records
    By dodell in forum Queries
    Replies: 3
    Last Post: 03-31-2010, 11:48 AM
  3. Unable to retrieve data on Comboboxes
    By ditogui in forum Forms
    Replies: 8
    Last Post: 12-29-2009, 10:26 AM
  4. Replies: 3
    Last Post: 10-06-2009, 02:11 PM
  5. Replies: 1
    Last Post: 06-20-2007, 07:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums