Databinding lost synchronizations (possible a Bug in Access)

[Andreas Hoffmann]

Our company developing an Access Solution, we work the most time with
Access. Our Customers use our product the most time of there daily work and
the Mount of Data is rising up very quickly. The important working table has
more than 128000 Rows (like Customers or Products).



For editing we use a Form and bind it to a Query. The Amount of the Rows is
more than 1300000 rows.

If the Count of Rows in a Form is greater than around 128000 rows,
forms.Recordset.findfirst find a Row (check with recordset.noMatch) but the
Form doesn't noticed that the Data in the Recordset is changed and showed
the content of another Row. The Cursor Position is at 13000, not at 128xxx.

In some cases the Data (row >128000) is display valid, but after changing
the Datarow, another (with a lower Position) will be override.



I hope I describe the Problem fully understandable.

If there any Questions, you can contact me.



Greets from Austria

Andreas Hoffmann

 

[David W. Fenton]

For editing we use a Form and bind it to a Query. The Amount of
the Rows is more than 1300000 rows.

If the Count of Rows in a Form is greater than around 128000 rows,
forms.Recordset.findfirst find a Row (check with
recordset.noMatch) but the Form doesn't noticed that the Data in
the Recordset is changed and showed the content of another Row.
The Cursor Position is at 13000, not at 128xxx.

In some cases the Data (row >128000) is display valid, but after
changing the Datarow, another (with a lower Position) will be
override.

Don't load the whole recordset and navigate via bookmark navigation.
Instead, assign the form's recordsource using the WHERE clause you
are using in the .FindFirst. This will be *much* faster than
navigating via .FindFirst. I have an app with over 350K records in
the "people" table that uses this approach and loading the
sought-for record(s) is instantaneous.

 

[Guest]

Thanks David,

I know this is a solution for the problem, the big problem the oldest part
of the solution is about 10 years old, and in many parts there is this old
legacy code, and we remove this.

but there are two things where I don't like to remove this. In our solution
there you can go to the contacts, you select your adress and say "back..."
than it write the new value back. The users like to step throw the contacts
and select one. The names are in moste cases the same, but in the second name
there was standing a little difference name (i.e. department ).

 

[David W. Fenton]

=?Utf-8?B?SG9mZm1hbm4gQW5kcmVhcw==?=

but there are two things where I don't like to remove this. In our
solution there you can go to the contacts, you select your adress
and say "back..." than it write the new value back. The users like
to step throw the contacts and select one. The names are in moste
cases the same, but in the second name there was standing a little
difference name (i.e. department ).

Then use a partial match for your filter. I do this in the large app
I mentioned. I have a FIND text box in the form's header, and the
user could put in:

Fenton

Fenton, D

Fenton, David

, David

The first would return all Fentons, second all Fentons whose first
name begins with D, the third all David Fentons, the last, all
Davids. And they can also use * and ? wild cards (because I'm
processing the input with Application.BuildCriteria).

They can switch the form back and forth between form and datasheet
view for easy browsing. When they are in datasheet view they can
doubleclick on a record selector and it switches back to form view.

This has been a very successful UI. It replaced a form with all
records loaded that was navigated with Ctrl-F.