RecordSet update taking too long for listbox to requery new information

[RonL]

RonL,

There's no difference - as far as the workings of Access' forms - between tables and queries. Actually opening a table is simply a very all-encompassing query. The bound result set - Access uses the term 'Recordset' - is that list of rows (records) that is the source for the data displayed on the form. The only way of obtaining a second reference to this bound recordset is to assign another object reference variable to the original object variable.

So how do we access that original variable? What is it? Strictly internal? I've not discovered any way in my reading that it can be exposed.

Then, of course, any pointer movement or data change made using the second variable will be reflected on the form. (Not true actually! Try changing a field value of a bound control in the recordset directly via VBA and the change is not reflected on the form and the change gets overridden when the form is updated. The safe way is to change the value of the bound control.)

A recordset created by any other means (i.e. OpenRecordset) is independent of the bound recordset. It may have exactly the same SQL specification but it is different and independent.

Now it depends upon the type of recordset that is created whether Access/Jet attempts to synchronise the data across the implementation for all affected recordsets.

By "type" you mean "dynaset," "forward" etc? What I'm seeking is a good tutorial on these fundamentals. When there will be the synchronization to which you refer and when there won't.

Here's a link that clearly shows if a recordset object is instantiated on a simple table - no form, no query, just a simple table, and no clone or indirect reference to the recordset object - then you can unequivocally manipulate that table in code:

http://ms-access-tips.blogspot.com/2...-vba-code.html

See why I'm confused? We can edit that table by directly manipulating a recordset instantiated from it, yet it appears if we had created a form for which that table is the recordsource, we can't see those changes reflected on the form (without manipulating the bound controls, as you say). There must be some fundamental principles relating to recordset object instantiation that's not sinking in with me.

Anyway, thanks for the post. -Ron

[Rod]

So how do we access that original variable? What is it? Strictly internal? I've not discovered any way in my reading that it can be exposed.

By ‘variable’ I assume you mean the table attribute/column/field. By ‘original,’ do you mean the binary encoding that resides on your magnetic medium (disk)? I can’t see why you would want to manipulate that directly, let the JET RDMS do that for you.

By "type" you mean "dynaset," "forward" etc?

Yes, I do. The synchronisation occurs for Dynaset; it may occur on other types but I’m not sure; certainly it does not occur on Snapshot. Dynaset is a JET/DAO concept. The JET/DAO does not (except in small result sets) expose the complete list of rows. Instead it exposes only a limited cache, enough I presume to fill a continuous form and a few more (either side?) to speed user navigation. When the calling routine moves beyond the current cache, the back-end data is requeried. Thus any changes that have happened in the meantime are reflected in the newly retrieved data. It’s not a ‘push’ synchronisation where JET broadcasts change to the user interface but rather a ‘pull’ synchronisation. This behaviour also explains why it is necessary to issue the MoveFirst/MoveLast command sequence to get an accurate RecordCount. (It also explains why Dynaset is a network traffic ‘hogger' in split database applications.)

Here's a link that clearly shows if a recordset object is instantiated on a simple table - no form, no query, just a simple table, and no clone or indirect reference to the recordset object - then you can unequivocally manipulate that table in code:

Yes, of course you can. JET does not care about the source of data changes (only that the changes comply with the syntax and are consistent). Changes may originate in forms, SQL and VBA modules. Please try to forget this ‘simple table’ concept. As I explained earlier opening a table is an all-encompassing form of a query. The OpenRecordset function also accepts a SQL statement that may be as complex as SQL allows (linked tables, unions, etc.) and you can still manipulate the values in the result set (and thus, eventually, the back-end database via the RDMS) directly in the VBA module without presenting those values on a form, provided that the SQL is an updateable query.

So what happens if you have more than one recordset for the same table and two or more simultaneous changes to the same field occur? Well, it’s first come, first served; any remaining changes will be rejected with a ‘record changed by other user’ message. ‘User’ in this sense, as viewed by JET, means routine or procedure, not end user.

Think in terms of layers (or chains/networks of objects). A control on a form is an object instance of the control’s class and this may be bound to a field (also an object instance) in an underlying recordset. Both the control instance and field instance have Value properties, but they are distinct. Access sets up mechanisms whereby a change to the control’s value is immediately (automatically) reflected in the field’s value – but not vice versa. Access, and for that matter VBA, default to the control, so if you are sloppy and code EventNum = 123 then the value of the control is changed, if the control exists. Access, in my opinion, is very bad in its automatic naming of objects: it names controls with the same name as the bound field; it names subform controls with the same name as the contained form. This may ‘protect’ the beginner from complexity but soon leads to confusion. Experienced Access users follow a naming convention (such as Reddick) for control names and internally declared VBA variables but will not use the convention for table attributes/columns/fields. Thus it is unambiguous when coding txtEventNum that the control is meant rather than EventNum which is the field.

Unlike the immediate updating of a field in the underlying recordset with a change to the control’s value, the back-end data is not immediately updated with changes to a recordset. In VBA this update has to be forced with a Recordset.Update command whereas for forms this is usually triggered by moving from one row/record to another. (There is a save record command if you want to stay on the same record.)

There must be some fundamental principles relating to recordset object instantiation that's not sinking in with me.

No, I don’t think so; I believe you are looking for something far too clever. Nothing except the RDMS works with the ‘original variable value;’ everything else works on a copy of that value, maybe even a copy of the copy.

Enough for now; I would advise you to get hold of one of the more ‘heavyweight’ texts on Access. One of the: ‘Look how easy it is; just use the wizard and type your value here’ type of commentaries is not for you.

[RonL]

By ‘variable’ I assume you mean the table attribute/column/field. By ‘original,’ do you mean the binary encoding that resides on your magnetic medium (disk)? I can’t see why you would want to manipulate that directly, let the JET RDMS do that for you.

I was referring to the "original variable" as you used the phrase, but I see now I misconstrued your meaning. I'd assumed that a simple table has a recordset property. I see now that's not the way to think of it. I understand about a table being the result of a grand query.

......... A control on a form is an object instance of the control’s class and this may be bound to a field (also an object instance) in an underlying recordset. Both the control instance and field instance have Value properties, but they are distinct.

Aha!

..............Unlike the immediate updating of a field in the underlying recordset with a change to the control’s value, the back-end data is not immediately updated with changes to a recordset. In VBA this update has to be forced with a Recordset.Update command whereas for forms this is usually triggered by moving from one row/record to another.

So I'm discovering.

................ I would advise you to get hold of one of the more ‘heavyweight’ texts on Access. .....

I have the 2010 "Bible" but so far, it's not been as informative as this thread. Thank you Rod and everyone. Will keep this thread handy. Again, apologies to OP for the tangent. -Ron

[orange]

Ron,

There is a good free reference at http://www.accessmvp.com/strive4peace/

and free video tutorials at http://www.datapigtechnologies.com/AccessMain.htm

and vba (all versions) http://www.functionx.com/vbaccess/index.htm

Good luck.

[baulrich]

So I've pinpointed, or at least narrowed down the cause of the problem. It seems to be related to the "persistent connection" code that I added to this project. I've tried different versions of the code in the original post and the problem persists. All I have to do to solve the problem is remove the persistent connection described next.

There is a query in this project that ran fine with only one user connected, but VERY slowly if more than one was connected. So I followed advice in a forum and added the persistent connection using a startup form with this code:

Option Compare Database

Dim dbsAlwaysOpen As DAO.Database

Private Sub Detail_Click()

End Sub

Private Sub Form_Close()
      Set dbsAlwaysOpen = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set dbsAlwaysOpen = OpenDatabase("M:\Data\My Data.mdb", False)
    'MsgBox ("Debug: Opened connection to database")
End Sub

This persistent connection fixed the problem of the slow query when more than one user is connected. Can someone tell me why it would cause a delay when running the small routine in my first post?

As you can see, the backend BD is in a shared network location, accessed by shared network drive. Until recently the front-end database was also in a shared network location, with all users connecting to the same .mdb. But I've tried having users copy the FE file to their local PCs and running it from there... same problem.

I have a feeling I'm missing some simple, common step that should be taken when deploying an Access app with FE and BE files.

thanks in advance,

baulrich