Bound/unbound forms ? (1 Viewer)

[mattP]

Hi all,

My current database (built with a lot of help and sample code form this site..thankyou to all for their advice) tracks the escalations we receive into the business but as we have used it more and more I have started experiencing a massive performance issue.

I basically have a main table that captures the basic details, reference, product, escalation source, escalation reason etc, and these are captured from combo box selections (so only capturing the id numebr of the row in the combo box)

My main problem seems to be a table I have that we have linked, via the unique escalationID, that we cna add notes for each escalation.

So for instance a particular escalations can 1 or 50 notes (or more) depending on the work involved to resolve.

This table now has thousands of entries and is about 10Mb on it;s own.
It seems that when I open my bound forms the query is having to scan not only the main table, but also all of the notes in the journal table.

My question is,

Would I be better to -
1 - Use unbound forms and pull the data via a recordset call or
2 - could I use a query that captures everything for only the open escalations and then have my main form query that query or
3 - have the form load but do not link the journal table and have the form pull the data in seperately ?

Hope that makes sense and appreciate any help on this one.

MattP

 

[macca the hacke]

One thing that will make a massive performance issue is indexing - have you indexed the tables properly? This takes up more space, but speeds queries up no end

 

[mattP]

Hi,

I'm not sure what you mean, is this the option to select Indexed (yes/No) for the fields in the tables ?

Regards

MattP

 

[macca the hacke]

Yes. If you use certain field(s) from a table a lot in queries (and therefore forms), it pays to index them. You do not have to make them a primary index. It is to do with the way Access stores information in the background.

 

[mattP]

I'll have a look at that, however my main performance issue seems to be around my large journal table, these are all unique entries as they are memo fields of update notes. If I take this table out completely thent he whole DB runs very well, however I need the journal notes to show when I open up an individual escalation.

Woudl it be easier to have the open escalation query ignore the journal table until I select an individual journal and maybe have it pull in the relevant data when the next form opens ?

Regards

MattP

 

[macca the hacke]

If you are currently showing all journals for all queries and don't need to view these all the time, then yes definetely only show them if you double click on a specific query. Access will have all those memo fields open at once, which will be a huge drain on resources.

 

[Pat Hartman]

Remove the query name from the subform's RecordSource essentially making it unbound. Put the query back in the after update event of the combo that selects the main record.