Help me understand queries in my code

[Datech]

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!