ODBC Data into Access Query/Subform

[NotAnExpert]

Good afternoon, many thanks for taking the time to read my post.

I'm sorry if this seems like an obvious thing to know, but because I don't quite know how to ask the question it makes things more difficult.

I have a database, which I have attached Access to via ODBC, the tables are linked, not imported but the source of the data is huge and accessed very regularly by several members of our team which has an impact on how quickly the results are shown in this Access database.

In the source data there are several fields which I will be searching from but to get the ball rolling I will give you an idea.

Table: SalesOrders
Fields: JobRef (Primary Key), CustomerName, CustomerPO,

Table: SalesOrderItems
Fields: ItemID (Primary Key), JobRef (Linked to SalesOrders: JobRef), Description, Price, Quantity

In the Access database I have the following:

Form: 'frmMainForm',
Textbox: 'txtJobRef'
'txtJobRef': AfterUpdate Event, requery subform

SubForm: 'sfrmResults' based on query 'qryResults'

'qryResults' gets information from ODBC Source 'SalesOrderItems'

I have noticed a couple of things on my learning path but I am hitting a couple of stumbling blocks.

The query is slow. It works fine, but it is slow.

I have been told that perhaps something else might be faster, I know a vlookup type function works ok but this means I can only get one set of information at a time, but I want to be able to display multiple items in my subform where the JobRef matches what is typed into 'txtJobRef' on my main form.

Is there a faster way of doing this which doesn't impact the source database when being queried by an outsider program like Access?

Any examples would be fantastic...

 

[Minty]

Don't requery the subform using the query. The sub form JobRef should be linked to the JobRef primary key in the frmMainForm. In the SubForm properties look at the Master Child properties.

The subform record source can be a query to restrict the data you pull in, but it doesn't need to be , Access uses the Join between the two datasets to provide instant(ish) updates.

 

[NotAnExpert]

Hi and thank you for responding so quickly.

Do you mean as master/child tables linked via the JobRef?

I have already tried this method but it still seems to take some time to pull the information from the source database (which isn't Access based, it's actually Sage Accounts 50 via ODBC)

Are there any other methods that might work quicker?

Kindest regards

 

[Minty]

It's normally the quickest route to tbh.

You may get some mileage out of restricting the sub form recordset to a limited date range or number of records, so if customer ABC1 has 2000 + invoices over the last 5 years that are being pulled in maybe you only need the last 12 months worth which would only be approximately 500 records?

Check (if you can) that the data you are linked to has Indexes on it.

 

[CJ_London]

your issue will be indexing (or lack of) and/or a slow network connection.

not much you can do about indexing since this is within Sage's domain except perhaps using their reindexing utility to refresh the indexes.

talk to IT about network speeds, they may be able to do something.

vlookup is an excel function, the access equivalent is dlookup so just a check, you say access, but quote excel - so are you using excel which is using access to connect to sage?

in any event, dlookups are very slow compared with a query.


Sage is read only so consider changing your recordset type in the query to snapshot. It can have a benefit.

Other things to consider:

ways to minimise network traffic, i.e. design forms to work of a small recordset rather than the whole table - so prefilter the data as much as possible. This includes not only the form recordset but also the rowsources for combo and listboxes.

e.g rather than the subform working off the entire table and relying on the linkchild/master properties to filter it. Have the recordsource be restated, applying the filter requirements based on the linkchild field.

If the data is not changing frequently, consider bringing the data through to populate a (properly indexed) local temporary table for the forms to work off - with an option for users to refresh at their convenience or on a timer.

Ensure your link to sage remains open and is not closed when the form closes

 

[NotAnExpert]

Sorry I do work with both Excel and Access so I referred to the wrong function, apologies. I did mean Access and Dlookup.

I do try and prefilter as you have mentioned, we are also running off a 1Gb network which I check frequently.

The IT stuff I am fine with, but getting into databases is something new and I like to learn.

The existing Access database has been split with all the forms on the client side and the back end on the server (same machine hosting the Sage program).

Could you explain what you meant by:

e.g rather than the subform working off the entire table and relying on the linkchild/master properties to filter it. Have the recordsource be restated, applying the filter requirements based on the linkchild field.

Hope this explains a bit more...

 

[CJ_London]

Could you explain what you meant by....

Should have said criteria rather than filter, but a typical setup might be

mainform recordsource:SalesOrders

subformrecordsource:SalesOrderItems with linkchild/master values based on JobRef

I note you have a query for the subform recordsouce, but no idea what that query does - it may be doing what I suggest

for the subform rowsource I would initially have it blank until a salesorder has been selected from a control on the main form and in the after update event of that control have code along the lines of

subform.form.recordsource="SELECT * FROM SalesOrderItems WHERE Jobref=" & cboJobref

or to make it a bit more universal

subform.form.recordsource="SELECT * FROM SalesOrderItems WHERE " & subform.linkchildfields & "=" & subform.linkMasterfields

no need to requery

 

[NotAnExpert]

OK, I think I see where this is going, so after doing a bit of reading I should point out a little more of how the database is currently set up.

The database is actually using information from the Sage database and storing copies of that information for an unrelated purpose except for traceability specific to our company with extra information that cannot be backwards saved into Sage itself.

The form is not linked to the source table, but the subform queries the products table in Sage, and it is this that is slow. Speeding this particular transaction up is quite important to all of the current projects where I am trying to add functionality. (Learning as I go...)

After looking at the build of the database, the subform is already set up as a parent/child linked table which only shows items linked to a JobRef number that matches the txtJobRef textbox on the main form. Now it is interesting to note that the dlookup performed on the 4 fields I have chosen are almost instantly populated but the subform takes several seconds to catch up.

It is this reason that I initially looked for a dlookup type alternative as the speed difference was plainly obvious.

I will save a copy of the database and try CJ's suggestion, i'll let you know how it goes.

Thank you again!

 

[CJ_London]

re dlookup, you might find this article of interest

https://www.experts-exchange.com/articles/1921/Access-Techniques-Fast-Table-Lookup-Functions.html

 

[NotAnExpert]

Thank you CJ for your suggestion:

I used the following but want to know if there is a further way to speed this up:

Me.SOP_ITEM_subform.Form.RecordSource = "SELECT * FROM SOP_ITEM WHERE ORDER_NUMBER=" & Me.txtJobRef

Is this the correct way to use this code in the afterupdate event of the search box?

It does seem to shave a second or two off the time it takes to display the results in the subform, is there a reason it does this?

 

[CJ_London]

yes - that is how to use it

other options, but no idea if they will be better or can be done with a sage backend

- have you tried changing the form recordsettype to snapshot as previously suggested
- index the source table
- investigate using ADO rather than ODBC via linked table
- ensure you maintain a persistent connection to sage so you do not spend 'refresh time' opening the link again.

Also
- have a routine to import products regularly to an access table (my guess is they don't change frequently) and provide a user option to update on request.