Results 1 to 1 of 1
  1. #1
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40

    Help me understand queries in my code

    Lot's of questions in this one, I'll try to highlight them all as I'm describing the situation:



    I have a nice front end on client/back end on shared server set up going on. We have to use a VPN though, so Access over the VPN was really slow. Migrated the backend to MSSQL Server 2005 and cleaned up all the code. Still works great for what we need on almost everything.

    We have one very large block of code though that calculates the values of a field for 150-200 records. Right now, here's the basic structure of the code:
    • Open recordset of all individual employees
    • Grab first employee's unique ID
      • Open recordset of all resource allocations where employee = unique ID
      • Set value of Headcount field to false for all records in this recordset using With rs.Edit
        • If number of records > 1, cycle through all the records comparing a bunch of fields to figure out which one should be true
        • This step uses a bunch of If statements, another recordset for the comparison, and more With rs.Edits to update the value

    • Next employee, repeat process


    Full disclosure: this was written by two college students with no VB, SQL, or Access knowledge. Thankfully it works, but its slow. Over the VPN it take ~7 minutes to complete this code for ~175 records with the SQL backend, and ~10 minutes with the Access backend. When the DB isn't split, it only takes a few seconds. That tells me that the majority of the calculations are still being done client side, and it's asking the server for A LOT of data in between, which get severely bottlenecked on the VPN.

    I changed one 'With rs.Edit loop' to an Update query (the only one I could figure out, or else I'd keep changing them), and it reduced the calculation time over the VPN by a full minute and a half with the SQL backend.

    Here are the basic questions:
    • Am I taking the right approach by trying to optimize this code?
    • Am I right in saying too many calculations are happening client side, and not enough server side?
    • Is there a better method than the multiple recordsets when it comes to comparing this information?
    • Is there a way to define and run a query in the code (maybe with dynamic parameters) instead of creating it in the Access frontend and using OpenQuery?


    Alright, and now the piece-de-resistance... The actual code. I've attached it because it seems large to me. I'm sure you can open it in Notepad++ or something and look through it. There are a lot of artifacts in there from old Debug statements, but this is currently what we use and what works for determining headcount for a month. I warn you, this may be a catastrophe to most developers. I honestly don't know how good or bad it is, so take it easy on me. If anyone wants to go through it, I welcome your suggestions.

    But mainly:
    • Take a look at how we are doing the recordsets, comparisons, and edits, and let me know if there is a specific way to make this more server side

    ​Thanks everyone!
    Attached Files Attached Files

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

Similar Threads

  1. Someone to help me understand a guide
    By carrybag93 in forum Access
    Replies: 4
    Last Post: 06-04-2012, 12:20 AM
  2. Replies: 2
    Last Post: 05-03-2012, 08:03 AM
  3. Need to understand code
    By accessnewb in forum Programming
    Replies: 2
    Last Post: 08-03-2011, 12:03 PM
  4. Replies: 3
    Last Post: 01-02-2011, 07:17 PM
  5. Help me to understand forms
    By RTaylor in forum Forms
    Replies: 0
    Last Post: 04-10-2010, 01:36 AM

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