UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Build Form.recordset In Advance, For Speed, Access 2016    
 
   
mempie
post Mar 13 2018, 10:57 AM
Post#1



Posts: 246
Joined: 27-September 01



My company is supposedly upgrading our hardware and networks. Right now the network is very slow. My Access database is just about unusable. But I thought of testing a faster method of opening forms that might be helpful. I was wondering what people thought of it.

For any table, I usually have 3 forms. First, users have a sort/filter dialog box where they choose sorting and filtering options for the table. Then they get a "browse" form - a continuous form with basic identifying fields for the table. The form data is sorted and filtered using the options selected in the dialog box. Buttons on the rows open the "details" form, which shows all the fields for one record, and allows editing. This form is bound to the same table, and uses the same sort/filter options. I store the user's sorting and filtering options in an instance of a custom class. The OnOpen event of the Browse and Details forms has code that looks for the custom class object, reads its properties, and applies them to the form's own .OrderBy and .Filter properties.

I think that when the .Filter property is set, the form's data is requeried. If a user goes through the typical steps of selecting options, opening the browse form, and opening the details form, the same table is being queried four times. On a fast network, it only takes a second or two. But on a slower network, perhaps a WAN, it might be worth taking trouble to make it query the table just once.

I was thinking I would take the user's sorting and filtering options, put them in the WHERE and ORDER BY clauses of a SQL statement, create a DAO or ADODB recordset from that SQL statement, remove the recordsource from my browse and details forms, and then do as I've seen in some example code: open each form :achidden, Set form.Recordset = my recordset, then set the form.Visible = True.

I'll probably have more questions about this technique if I go ahead. For now, I'll just ask: is it worth doing? Will my forms load faster? What I've read about use of the form's .Recordset property doesn't talk about the speed of loading two forms and applying sort/filter options. It gives other reasons for doing this, which might also matter, but are not immediately important to me. I'd like to know if this is a tried and true method for making forms load faster.

My browse and details forms have a Sort/Filter button which brings up that sort/filter dialog box, so the user can change the options. Right now, after that dialog box closes, the new options are again applied to the form's .OrderBy and .Filter properties. But with this technique I wouldn't be using those properties. I'd be changing the recordset again, and applying the change to both forms, if both are open. So I'd need to figure out that code as well, and it would also need to be optimized for speed on the same slow network.

We have recently upgraded from Access 2010 to Access 2016. Some clients are on Access 2010 or maybe even Access 2007 at the moment.

Thank you.
Go to the top of the page
 
Jeff B.
post Mar 13 2018, 12:02 PM
Post#2


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


You mentioned a WAN -- please read more here about how ... "unsettling" ... that can be.

If I understood your description, you may be loading more records than are needed -- that takes time.

I'm not clear from your description if you are keeping a "persistent connection" open to the database -- if not, setting one might help.

And this may be a misinterpretation/overreaction ... did you describe users sifting through a table to find something? If so, consider creating a user interface that doesn't require users to learn/know MS Access. ... and does this mean your data/tables are organized more for user-understanding? If so, that sounds a lot like ... a SPREADSHEET! If that's the case, consider brushing up on relational data base design and normalization (plenty on both topics available here).

Good luck!
Go to the top of the page
 
missinglinq
post Mar 13 2018, 12:22 PM
Post#3



Posts: 4,537
Joined: 11-November 02



Albert Kallal has one of the best articles on the subject of WANs and Access:

http://www.kallal.ca/Wan/Wans.html

Linq ;0)>
Go to the top of the page
 
tina t
post Mar 13 2018, 12:26 PM
Post#4



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
You mentioned a WAN -- please read more here about how ... "unsettling" ... that can be.

hello, Jeff, will you add the link for the "read more here", pls?

tia
tina
Go to the top of the page
 
Jeff B.
post Mar 13 2018, 02:46 PM
Post#5


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


<Tina>

I would go with Albert K's article (see Linq's post)
Go to the top of the page
 
mempie
post Mar 13 2018, 03:19 PM
Post#6



Posts: 246
Joined: 27-September 01



I'd still like to know whether it may be worthwhile, for speed reasons, to build and assign recordsets in the way I describe.

That was indeed a misinterpretation/overreaction. My interface doesn't require users to have any knowledge of Access, and does not resemble a spreadsheet, except for the browse form itself. Being a continuous form, it superficially resembles a spreadsheet. My design is based on the idea that users want to begin by selecting a subset of the table, and then be provided with a list of the records in the subset, before working on individual records. It may not be the best arrangement for every database and every table. But I've made many databases this way, and no user has objected. Indeed, I think that many users like the "browse" form because they like Excel and are used to seeing their data arranged that way. Many Access databases begin life as Excel spreadsheets, and then outgrow them. My browse form will usually display just the fields that identify a record. If it is, for example, a table of Documents, the Browse form might show the Document Number, Title, Creation Date, and maybe one or two other fields. I won't put all the fields into the Browse form, unless someone asks me to. Which one client did. Because she really liked Excel.

I make a different sort/filter dialog box for each table. The options depend on what the data looks like. If the table contains Projects, divided into ten "tasks", its sort/filter dialog box will have a drop-down list for tasks, and maybe some textboxes for putting in date values, to filter the Start Date or Completed Date fields. I add whatever options seem appropriate, or whatever the user requests. If the table is big, I may require the user to choose some options before enabling the OK button, so they don't get too many records returned.

I haven't created a Persistent Connection in my databases, though they are nearly all split and run on networks. I wasn't familiar with that technique. I may go ahead and do that, having looked at various website that talk about it. It still seems likely that I can improve the performance of the set of forms I describe, by building and assigning recordsets. But maybe a persistent connection reduces the advantage. Any thoughts on that?

I looked at Mr. Kallal's old article about WANs. I'm no network admin, but not all WANs are as bad as he describes. In my last job, I had a WAN that ran over a T1 line to our satellite office. An Access database ran much slower than it did over a LAN, but it was still useable. I learned a few things about designing faster interfaces and queries. But I never got around to trying the technique I described in my post.

Our old network was fast. Our new network connects to our server through the old network, through a whole bunch of nodes, and is therefore slow. I heard that the server will eventually be moved to the new network, and then my database will be usable again. Our IT department isn't communicating much, and hasn't really explained it all. I figure I'll use this time as an opportunity to test any changes that might make my databases go faster. I may have clients whose networks are slower than ours used to be, but not so slow as to make Access useless. I don't know what clients we may have in the future, or what our own IT department may do in the future in order to save money.

Many of us work at home and connect to the network using VPN. We don't run Access databases over VPN.

There are any number of other things I might do to speed up forms or the database as a whole. I didn't want to make this post more broad than it already is. I may ask about these other methods in other posts.

Go to the top of the page
 
tina t
post Mar 13 2018, 05:15 PM
Post#7



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
I was thinking I would take the user's sorting and filtering options, put them in the WHERE and ORDER BY clauses of a SQL statement, create a DAO or ADODB recordset from that SQL statement, remove the recordsource from my browse and details forms, and then do as I've seen in some example code: open each form :achidden, Set form.Recordset = my recordset, then set the form.Visible = True.

i guess the part i'm not getting is why you want to create a DAO or ADODB recordset and assign it to the form. why not just leave the RecordSource property of the two forms blank; then, in the browse form's Load event procedure, build the SQL statement you want (don't include any fields in the SELECT statement that are not supported in the form, unless they're needed for something else - calculations, for instance) with the WHERE and ORDER BY clauses you want, as a string variable in VBA, and set the form's RecordSource to the string, as

Me.RecordSource = MyStringVariableName

use the same technique when opening the detail form, using the WHERE clause to limit the returned recordset to the one targeted record.

and suggest you also employ a persistent connection, as was already suggested.

hth
tina
This post has been edited by tina t: Mar 13 2018, 05:17 PM
Go to the top of the page
 
mempie
post Mar 14 2018, 09:39 AM
Post#8



Posts: 246
Joined: 27-September 01



The browse and the detail forms use the same records. If I do as you suggest in both forms, that's likely better than what I'm doing now. Instead of querying the table four times, I'd be doing it twice. But I imagine I'd be querying the table just once, even after both forms are open, if I use Form.Recordsset as I described. If I can get it to work.

Which I can't, so far. When I Set frmMyBrowseForm.Recordset = rstRecordsetForForm, the line produces Error 2448: "You can't assign a value to this object". I have tried it with a DAO recordset and an ADODB recordset, with the same result. At the moment, the code looks like this:

In another module I have
Public rstProjectForms As ADODB.Recordset

Then I call a method of a custom class where I have been storing my sort and filter options. It has this:

' Make the recordset that Project forms will use.
Dim strBuildFormSQL As String
Dim boolBuildIt As Boolean
boolBuildIt = False
strBuildFormSQL = "SELECT * FROM tblProject"
If Len(Me.strFilter) > 3 Then
strBuildFormSQL = strBuildFormSQL & " WHERE " & Me.strFilter
End If
If Len(Me.strSort) > 1 Then
strBuildFormSQL = strBuildFormSQL & " ORDER BY " & Me.strSort
End If
strBuildFormSQL = strBuildFormSQL & ";"
If Me.strFormRecordsetSQL = strBuildFormSQL Then
If rstProjectForms Is Nothing Then
boolBuildIt = True
End If
Else
boolBuildIt = True
End If
If boolBuildIt Then
If Not rstProjectForms Is Nothing Then
rstProjectForms.Close
Set rstProjectForms = Nothing
End If
Me.strFormRecordsetSQL = strBuildFormSQL
Set rstProjectForms = New ADODB.Recordset
rstProjectForms.CursorLocation = adUseClient
rstProjectForms.Open strBuildFormSQL, _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

' Set rstProjectForms = CurrentDb.OpenRecordset(strBuildFormSQL)
End If

Then, in the button code that launches the browse form:

DoCmd.OpenForm "frmProjectBrowse", acHidden, , , , , "View"
With Forms("frmProjectBrowse")
Set .Recordset = rstProjectForms ''''''''''''''''''''this line makes Error 2448.
.Visible = True
End With

There may be other problems with this approach, but I can't figure out why I can't assign my recordset to the form.Recordset. My code looks much like example code I've seen in various places.

Even if I don't get a speed improvement, I may want to do this with recordsets for other reasons. I understand that I can have transaction processing with forms and subforms, after reading that part of the old Access 2002 Developer's Handbooks.

Go to the top of the page
 
LPurvis
post Mar 14 2018, 02:06 PM
Post#9


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Sorry to come along late to the party...
Is this a form for displaying matching rows to the requested criteria the user has provided?
Does the user then edit the data of those rows, or select the record they want to work upon and edit that in another form?

If this isn't for editing, but you're looking to avoid subsequent trips to the server as the user changes the sort order, then you could use a local table (probably with greater efficiency, ADO recordsets in particular won't necessarily accept complex sorting conditions and can be slow to re-sort and then re-apply.)
The reason you're unable to assign a recordset is unclear. Can you assign a local table? (I don't necessarily mean as a workaround, but out of curiosity.)

Cheers
Go to the top of the page
 
mempie
post Mar 14 2018, 03:47 PM
Post#10



Posts: 246
Joined: 27-September 01



I usually don't allow editing in my browse form. I make them go to the details form to edit a record. One client specifically asked me to allow editing in the browse form. I allowed it for certain fields that didn't require validation.

The browse and details forms both use the same set of records, matching the criteria selected in the sort/filter dialog box. The details form is Default View = "Single Form", and has typical First-Previous-Next-Last buttons for navigating the selected set of records.

I'm not sure what you mean by "local table". Do you mean a table that is in the front end? I made a copy of my projects table, put it in the front end, and named it tblProjectsTest, and then copied all the project data into it. I changed my SQL statement so it would read tblProjectTest instead of tblProject. Same error. I changed the code so that my Recordset would be a DAO.Recordset instead of an ADODB.Recordset. I replaced the lines of ADODB code that create the recordset object with this:

Set rstProjectForms = CurrentDb.OpenRecordset(strBuildFormSQL)

I still get the same Error 2448.

I'm not sure what you have in mind about using local tables. Can you tell me more about ADO recordsets not accepting complex sorting conditions? Do you mean filtering conditions? What is the limitation?

For making things go faster, I'm happy to make a DAO or ADO recordset. But if I want to use this code later, in another database with subforms that would benefit from transactions, I would need ADO.
Go to the top of the page
 
LPurvis
post Mar 15 2018, 06:35 AM
Post#11


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

So in this instance, you're editing in the browse list. That's a consideration.

>> I'm not sure what you mean by "local table".
Exactly as you did. A local copy of the server table to temporarily hold data while it's re-sorted and re-displayed.
Only a change of criteriawould require that local table being emptied and re-populated from the server.

>> I still get the same Error 2448.
Well, once there's a local table, there's no need for assigning a recordset. The local table is your semi=permanent recordset. Just use it as the source for the form.
The problem comes in that you are allowing updating in this form. So then you need a flag in the local records to mark them as edited to then write back those rows to the server when you deem it appropriate. (The recordsets on server data handle that for you, but at that slight overhead cost too.)

>> What is the limitation?
That's the problem, you can't necessarily predict it in advance (not with a high degree of accuracy anyway).
I've made filters against an open ADO recordset and winced as I expected it to fail but it proceeded. Then been aghast when it failed o something relatively simply.

There's an example I have that uses a recordset on a combo box. The idea being that once the user types a few characters, the list then displays but filtered. Subsequent key presses further limit the list as characters are typed.
That functionality is standard, Allen Browne's had it up for years. My example differs in using a recordset to avoid re-querying the database with each subsequent keystroke.
It works there as we're dealing with subsequent filters - not a new set of criteria altogether, and of course, the data isn't updateable.

>> in another database with subforms that would benefit from transactions, I would need ADO
Both DAO and ADO support transactions. But assigning a recordset to a form and wrapping it in a transaction is both possible and unpretty.
You'll need to error handle a lot, and even then you don't have a lot of control. (I have an example of doing that too, both in the link in my sig below.)
I think Ben wrote an article on it too at some point.
Local tables might be the way forward with that too. (One thing I love about Access as a development platform for server data, is that it can host local data and manipulate it trivially.)

Cheers
Go to the top of the page
 
mempie
post Mar 15 2018, 11:02 AM
Post#12



Posts: 246
Joined: 27-September 01



What you are describing - making local tables and bringing over the data - sounds like replication. That sounds like a serious compromise of the database's functionality, introducing latency in data. It seems justified only if the network is really bad and will remain so. The copying of records will be slow. Users will need to decide at the beginning what records they want to see, and wait for them to be brought forward.

Right now, my network is really bad. If they won't fix it, or won't tell us when they might, I may end up doing something much like what you describe. One of my colleagues actually proposed something like that, just this morning. I have previously built some pieces of that kind of solution, for other reasons. If I go down that road, and have questions about it, I'll start a new thread.

What you say about ADO choking on complex WHERE conditions is disturbing. I mostly use DAO, and have never had that kind of problem. I have never tried binding a DAO.Recordset to a form before.

My ideas about creating recordsets, binding them to forms, and reusing them, seem like they should improve performance in the interface I described, without compromising functionality at all. it could also get me started on using transactions. That is something specifically requested by one of my users, on a project that is now on hold. It has a form showing an order, with a subform showing order items. I have just begun reading about subforms and transactions in the old Access 2002 Developer's Handbooks. They also said that it wasn't simple, and wasn't easy to generalize.

To avoid my Error 2448, there's something else I might try, later today.
Go to the top of the page
 
LPurvis
post Mar 15 2018, 11:36 AM
Post#13


UtterAccess Editor
Posts: 16,280
Joined: 27-June 06
From: England (North East / South Yorks)



>> What you are describing - making local tables and bringing over the data - sounds like replication.
That would be the case if you were attempting to synchronise all data. If I'd been talking about emulating replication - I definitely would have said so.
I'll not address all the subsequent points you made about that ("That sounds like a serious compromise ..." etc.) as I suspect they're a result of a misconception. But just to touch on:

>> Users will need to decide at the beginning what records they want to see, and wait for them to be brought forward.
Isn't that exactly what you're doing at present when loading a recordset?

The process I'm talking about a local table which holds exactly the same data as that which you would hold in your recordset.
You've been talking about opening a recordset based on some initial criteria provided by a user. It's then held in memory (and in the case of DAO, you then hope it doesn't perform subsequent fetches to the database to satisfy the request - though with ADO you can work client side, disconnect and remove that possibility.) A local table holding the data that you would have fetched into a recordset is analogous, the difference being it held in RAM verses the local disk. We're talking about no more data than that you'd be fetching into a recordset anyway. The data required over the network is no different at all. However you can load, re-sort and subsequently filter that small table of data very efficiently once it's local.

If I understand your initial problem correctly, it was operations performed on the set of data that was initially requested which was slowing your process down, as subsequent requests were being made for the data.
A recordset could help with that only to an extent (as mentioned, once bound to a form, you could see it making subsequent requests to the database anyway).
Your limitation of network performance is key. There's only so much you can achieve. You've assured us there is nothing but reliable connections to the database so the usual WAN risk is not an issue. But it's still not well performing. You're having to operate on the back foot, and if recordsets only get you so far, holding that same data as a recordset locally means it will be only requested when you say so.
Add to that your interest in transactions in forms, and you're going to struggle with bound recordsets.

Regardless, the problem of why your form won't bind to a recordset is a key one. You can bind an empty form to a recordset. So something in your scenario is preventing it. (There are certain requirements for an ADO recordset to successfully bind, but a DAO one should be quite simple, and yet it's not.) Have you tested the same recordset on different forms?


>> What you say about ADO choking on complex WHERE conditions is disturbing.
It's important to note that I'm not saying that. But that on subsequent Filters and Sorts it's happened. In the WHERE condition of a SQL statement, that's passed to the database and is universally returned, ADO plays little role in that. It's the vessel.


>> My ideas about creating recordsets, binding them to forms, and reusing them, seem like they should improve performance in the interface I described, without compromising functionality at all.
I may have not gathered the ultimate performance improvement aim, but was it number of fetches from the database based on user manipulation of the returned data?
Looking back, when you mentioned in the original question:
>> I was thinking I would take the user's sorting and filtering options, put them in the WHERE and ORDER BY clauses of a SQL statement,
I'd say that's the key part of all of this. i.e. don't present a form with data until you've retrieved criteria and sort order, and then make the request with the form only asking for that. The form opens only requesting that SQL statement's worth of data. Prior to that it's unbound.
Recordset or not, the sequence of requests is key.

Perhaps it's still not clear what the process in play presently.

Cheers
Go to the top of the page
 
mempie
post Mar 15 2018, 04:02 PM
Post#14



Posts: 246
Joined: 27-September 01



I think you do understand what my interface does. However, if a user retrieves a subset of records, goes off-line, edits some, and maybe deletes one or two, and these records have relationships, then there needs to be a process for reconciling the result with the data on the server. It may not be full-service replication, with conflict resolution and all that. But it still sounds as if it requires handcrafting a process for getting the changes into the back end. All the same, I'm interested, and I might pursue that, if I don't hear any good news about our current network. How do you handle multiple users wanting to edit data? I thought of adding fields to the back end data - [CheckedOutBy], which would have the UserID of the user who checked out the record, and [CheckedOutDate], which would store Date() when it was checked out. Other users could view the checked-out records but not check them out for editing. Writing those [CheckedOutBy] and [CheckedOutDate] values would make the record transfer even slower on a slow network.

I'm still interested in the question of how to change my existing interface to work better on networks I haven't seen yet, that are slower than they should be, but not real slow. My original idea seemed like a simple change. I still think it would be simple, if not for this Error 2448. I still need to try it with other forms, and try one or two other things, and then get back to you on that specific problem. Meanwhile...

I clicked on your name, because I used to know a Lea Purvis. Lea was short for Leanne. it wasn't you. She was female, and American. But that led me to this site: http://www.databasedevelopment.co.UK/examples.htm which seems to be old. Some of the examples there include "Transactions In Forms", "Unbound Form Example", and "Bind Any Data to an Access Form by Generating Recordsets", which all seem to pertain to this discussion. I think I'll be needing some example code. Would you recommend these, or any other examples? Would anything in those examples be different in Access 2016? Is there an example more specific to the process you are recommending?

Go to the top of the page
 
mempie
post Mar 22 2018, 10:09 AM
Post#15



Posts: 246
Joined: 27-September 01



Now I have followed one of your suggestions and tested my code with a different form, to see what happens. Instead of opening the browse form, I changed my code to open the details form first. It gave me the same error. I should mention that the debugger doesn't show any apparent problem. In the Watch window, I put the name of my recordset. it seems to be created correctly, with various properties I would expect.

I also had a look at some example code from the ol' Access 2002 Desktop Developer's Handbook, which specifically does what I was trying to do - create a DAO or ADO recordset and attach it to a form. There are a few examples and they all seem to work.

I may indeed take the other approach, and use local tables. I may also start a new thread for that problem. I still think I'll need to solve this form-and-recordset problem when I resume work on my other database, and try to make some transactions happen.

Thank you.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 02:41 AM