Problem locating Next Record using "Next Record" Macro

[MSAccessRookie]

I am presently assigned to a project that requires the modification and conversion of an application that is a Multi-User MS Access environment where there is a common Network Based Back End, and each user has their own front end. The application is to be converted to use an SQL Server Back End, and as much of the Original Front End as necessary to keep the user interface the same.

So far I have moved all of the Data Tables to the SQL Server and have been able to divide the base query set into two parts:

o A Subset of 42 queries that are stored on the SQL Server as Server Views and have the naming convention View[view name]
o The remaining queries that remain on the Front End for varying reasons (some of which refer to the Views on the server).

While Most of the Converted system is working acceptably, I am currently having difficulty finding the Next or Last instance of a Person_ID in from within a list of WorkOrders.

The table containing the list of people contains about 8300 entries
The table containing the WorkOrders contains about 38000 entries.

A form has been set up with a Control Field called FindPerson that is populated by the query qryPeopleAlphabetic, which in turn refers to the View ViewPeopleAlphabetic (See Query and View Definitions Below)

There are two buttons associated with the Control Field called Last and Next

o The Last Button calls a Macro called FindLastPerson (See Macro Definition Below)
o The Next Button calls a Macro called FindNextPerson (See Macro Definition Below)

When Either button is selected, the Macro eventually finds the expected result, but the amount of time is not only unacceptable in its own right, but significantly longer than the original application. Is there any way to make the Macro Search faster? I have provided below as much information as I can. If there is anything else necessary, I will attempt to fill in any blanks.

Please forgive the long dialogue, but I wanted to provide as many details as possible.

-------------------------------------------------------------------

Structure of tblPeople

Person_ID AutoNumber
Researcher Text
L_Name Text
F_Name Text
M_Initial Text
Title Text
CommonName Text
Notes Memo
email Text
DateAdded Date/Time
SSMA_TimeStamp Binary

-------------------------------------------------------------------

Queries and Views

qryPeopleAlphabetic

SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial, L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic
ORDER BY L_Name, F_Name;

ViewPeopleAlphabetic

SELECT IIf(IsNull(L_Name), "", L_Name) & IIf(IsNull(L_Name), "", IIf(IsNull(F_Name), "", ", ")) & IIf(IsNull(F_Name), "", F_Name) AS Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial, L_Name, email, Notes, IIf(IsNull(Title), "", Title & " ") & IIf(IsNull(F_Name), "", F_Name & " ") & L_Name AS FormalName, IIf(IsNull(F_Name), Title & " ", F_Name & " ") & L_Name AS SemiFormalName, CommonName
FROM tblPeople;

-------------------------------------------------------------------

Macros

FindNextPerson

Find What is =[FindPerson]
Match is Whole Field
Match Case is No
Search is Down
Search as Formatted is Yes
Only Current Field is Yes
Find First is No



FindFirstPerson

Find What is =[FindPerson]
Match is Whole Field
Match Case is No
Search is Down
Search as Formatted is Yes
Only Current Field is Yes
Find First is Yes

-------------------------------------------------------------------

 

[MSAccessRookie]

I continue to seek what I still can not find

This issue has become the final issue in the first level of the project. It turns out that the average person using this functionality, is actively on the telephone with a client as they are looking up the information, so you can understand why time is of the essence.

I have looked many places for answers (Online articles, these forums, Microsoft Knowlege base, to name a few) and have not found a good lead yet. Any assistance or a gentle shove in the right direction would be appreciated.

 

[georgedwilkinson]

Your data set is so small that your forms should be updating almost instantaneously.

Make sure your indexes are correct on SQL Server for the table(s) in question.

You might want to replace the "IIf(IsNull" with coalesce. Not sure if that will speed it up but it might help.

Is there a lot of other clutter on the form? If there are multiple fields from multiple tables and/or multiple combo boxes with sql as the lookup, the database could be busy loading the (entire) record sets for all of those objects. Sometimes this can cause locking/blocking conditions on SQL Server that will impact other users (SQL Server promises not to change a result set until all data has been returned). It can also potentially cause deadlocks, which will slow everything down even more.

Check perfmon on your server. Check perfmon on your client(s).

See if you have a lot of dynamic SQL in your application. This is one of those silent application killers. Theory is that each query is unique to SQL Server and must be re-compiled, re-planned, etc. by the server. Not a big deal since it takes a couple of micro-seconds, right? Wrong. Each new, unique query that the server gets can eventually kick out old queries (that may actually be optimized for SQL Server) from the queue, requiring re-runs of previous queries to be recompiled, etc. Try to re-write any dynamic sql to use bound variables.

I'm sorry, I don't use macros so I can't comment on them. However, I can say that I'm terrified of them because I can't see what's going on (everything looks like a black box to me). Could be going from macros to a different method of navigation might help...I just don't know.

I'm only responding because no-one else has and you seem to need answers. I am by no means the best expert on this topic on these forums. I just know what I know from hard-core experience.

 

[MSAccessRookie]

---------------------------------------------------------

georgedwilkinson,

I am sorry that I did not see your response before now. Everything that you are saying makes sense, and I appreciate your assistance.

---------------------------------------------------------

 

[MSAccessRookie]

UPDATE: 5/12/2008

One of the users is manually able to create a filter that totally eliminates the problem. I think that this approach may provide the answer that I am looking for. I have not been able to make it work on-the-fly yet.

Any comments?

 

[georgedwilkinson]

Your last post still makes me think of index problems. Make sure your indexes are right on SQL Server. Since your user was able to improve performance by filtering, I would suggest that his query used an index and the "base" query does not.

 

[MSAccessRookie]

georgedwilkinson,

I am not sure that I know how to add an index to a query. I only know about the tables behind them. perhaps you have a suggestion as to which column(s) would benefit.

The purpose of the form is to display the information regarding WorkOrders, and the purpose of the Combo Boxes is to choose either an institution or a person to search the list for (Example would be to use the name Combo Box to locate all WorkOrders that are to be shipped to "George Wilkinson".

The filter would be to select only items from the WorkOrders table where the ShipToAddressID matches the Person_ID of the customer selected in The Combo Box.

----------------- Work Orders Base Query -----------------

SELECT
WorkOrderID_old,
DateOrdered,
Institution_ID,
ResearcherID,
PO_No,
Comments,
Previous_InvoiceNumber,
InvoiceNumber,
NeedBy,
MakeBy,
ShipBy,
BriefDescription,
ShipToATTNID,
ShipToAddressID,
BillToATTNID,
BillToAddressID,
InvoiceMailed,
InvoiceComments,
InvoiceDate,
OrderEnteredBy,
InvoicePreparedBy,
WorkOrderID_Less_Old,
StandingOrderInd
FROM
tblWorkOrders
ORDER BY InvoiceNumber DESC;

----------------- Work Orders Base Query -----------------

----------------- Combo Box Query -----------------

SELECT
IIf(IsNull(L_Name), "", L_Name) &
IIf(IsNull(L_Name), "",
IIf(IsNull(F_Name), "", ", ")
) &
IIf(IsNull(F_Name), "", F_Name) AS Expr1,
Person_ID,
Researcher,
DateAdded,
Title,
F_Name,
M_Initial,
L_Name,
email,
Notes,
IIf(IsNull(Title), "",
Title & " ") &
IIf(IsNull(F_Name), "",
F_Name & " ") & L_Name AS FormalName,
IIf(Not (IsNull(F_Name)),
F_Name & " ", Title & " ") & L_Name AS SemiFormalName,
CommonName,
SSMA_TimeStamp
FROM tblPeople
ORDER BY 1;

----------------- Combo Box Query -----------------

 

[MSAccessRookie]

Update:

I looked at the tables for the WorkOrders and the People SQL Server and checked the Indexes. There was the following (all of which is related to the issue):

tblPeople has an index on the Column Person_ID (Primary Key)
Institutions has an index on the Column institution_ID (Primary Key)
tblWorkOrders has an index on the Column Institution_ID (Foreign Key)
tblWorkOrders has an index on the Column InvoiceNumber (Primary Key)
tblWorkOrders has an index on the Column ResearcherID (Foreign Key)
tblWorkOrders has an index on the Column ShipToAddressID (Foreign Key)

All of the Indexes were rebuilt, and there was no difference when the Next or Last button was clicked.

 

[georgedwilkinson]

You don't put the index on the query but on the underlying table.

I see some potential problems but cannot be sure. As I recall, this is a SQL database.

Your first query has no constraints and is ordered by the invoice number, descending. Also, there is an index on invoice number, but it does you absolutely no good, in this case. If I'm not mistaken, SQL Server must get every WorkOrder record into its working memory to do the sort before it can return the first several rows. When your user put a filter on the form, it made SQL able to ignore any rows that didn't meet that criteria before it had to do the sort. So, you might want to:
1. rethink the WorkOrders query and re-write it so SQL doesn't have to load every record, or
2. put a descending index on InvoiceNumber, other than the PK index (I don't know if this will help but it's a fast test for you...if it does, you can potentially remove the index and just make the PK index descending), or
3. buy (a lot of) additional memory for your server (least desirable with probably the least bang per buck)

Ummm. How big is tblPeople? Since there is no where clause, an index will likely do you no good. Also, you're ordering by a function (IIf) so you cannot use an index for that (unless you have a version of SQL Server which I've never worked with, which is possible...Oracle does have limited indexing on functions). Is it your belief that the combo-box performance is a problem?

Another thing that might be giving you grief is database locking. This is a pretty complex concept, but, when you first load a form, the database goes off and works its little heart out trying to gather all the data (all as in ALL) that you requested. Since SQL Server promises you that it will never change data you have done a query on until it has completed your query, all other users can be locked out of certain rows, clusters, tables, or even the entire database until SQL has returned all the data (all forms, subforms, data on tabs, combo-box queries) back to Access. This can look like a performance problem to users. You can fix this by re-thinking your base queries and/or by using optimistic locking.

Try my suggestions out and let us know how it worked.

 

[MSAccessRookie]

Looks like I found the problem after all. I modified each of the affected fields so that they would apply a filter to the form after they were updated. See examples below for researchers and institutions:

DoCmd.ApplyFilter Me.Form.Filter, "((Lookup_ResearcherID.Expr1=""" + [FindPerson] + """) AND (qryWorkOrders.InvoiceNumber Is Not Null))"

DoCmd.ApplyFilter Me.Form.Filter, ((Lookup_Institution__ID.Institution=""" + [FindInstitution] + """)"

Thanks again for everyone's assistance.

georgedwilkinson,

Some of your suggestions sounded highly useful, and potentially effective, so I will also look into them as time passes -- Thanks again

 

[MSAccessRookie]

Looks like I found the problem after all. I modified each of the affected fields so that they would apply a filter to the form after they were updated. See examples below for researchers and institutions:

DoCmd.ApplyFilter Me.Form.Filter, "((Lookup_ResearcherID.Expr1=""" + [FindPerson] + """) AND (qryWorkOrders.InvoiceNumber Is Not Null))"

DoCmd.ApplyFilter Me.Form.Filter, ((Lookup_Institution__ID.Institution=""" + [FindInstitution] + """)"

Thanks again for everyone's assistance.

georgedwilkinson,

Some of your suggestions sounded highly useful, and potentially effective, so I will also look into them as time passes -- Thanks again

Unfortunately, this approach does not work correctly, and I had to back it out. Apparently it changes the range or WorkOrderIDs and when a new WorkOrder is added, and the next record pointer becomes incorrect in the process.

The Application begins with about 37,000 Workorders to sort through, and the filter brings that number down to about 800 or so (for one example case). It also appears to change the NextRecord value form 37000+ to 800+. I know this makes no sense, but that is what happened.

Since each WorkOrder record has a unique (AutoNumber) column for the WorkOrderID, The record cannot be inserted at the expected point (because there is already a record there) and the update fails.

I tried a Manual "Filter By Form", and the results were what I wanted them to be. The filter group had 800+ to search through, and the insert was at the end of the list where it belonged. Any suggestions as to how I can accomplish that action via program code would be greatly appreciated.