128k record limit on forms????

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

Is there some kind of limit on the number of records a
lookup combo box can search through?

I created a form with a combo box that looks up records
in the form. Using the following (partially) MS
generated code (I modified it a little):

Private Sub ComboXXX_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Not IsNull(Me.ComboXXX) Then
rs.FindFirst "[MyNum] = " & Str(Me![ComboXXX])
If rs.NoMatch Then
MsgBox "The number you entered was not found. Please
try again", vbDefaultButton1, "No Record Found"
Else
Me.Bookmark = rs.Bookmark
End If
Else
End If

This form is based on query that pulls from three large
tables. Now, what I noticed is that once that underlying
query hit 128,000 records, this code no longer works
properly. I fear I have hit up against an Access
limitation that I didn't know about. Is there some
reason why the bookmark property doesn't work after 128k
records? I was sure I read somewhere that Access can
handle unlimited bookmarks when a form is opened.

Any help in getting around this is greatly appreciated!!
 
Hi Sandra.

If you have more than 128k records, it is time to rethink the design. There
are ways around some of these issues (dependent on the actual data source),
but a far better approach would be to just load the record you want.

Once way to do this is to use the AfterUpdate of the combo to change the
RecordSource of the form:

Private Sub ComboXXX_AfterUpdate()
' Load just the record that matches the control.
Dim strSQL As String

If Me.Dirty Then
Me.Dirty = False
End If
strSQL = "SELECT * FROM MyTable WHERE [MyNum] = " & Str(Me![ComboXXX]) &
";"
Me.RecordSource = strSQL
End If

If you want to continue with your exisitng approach, and your data is is
Access tables, you could try:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
...
However, we have experienced the Bookmark bug where this code takes you to a
record that looks like the one you want, but when you save your changes it
overwrites a different record. That experience was with a fully patched
Access 97 database, and a client who had several hundred thousand records in
the form. That means that this approach is not only inefficient, it is
potentially dangerous.
 
However, we have experienced the Bookmark bug where this code takes you
record that looks like the one you want, but when you save your changes
overwrites a different record. That experience was with a fully patched
Click to expand...

Allen, I see that the Bookmark is only supposed to be used in a
module where the setting is Option Compare Binary, but I never see
this discussed in the context of Bookmark bugs. Have you got an
opinion?

(david)


Allen Browne said:
Hi Sandra.

If you have more than 128k records, it is time to rethink the design. There
are ways around some of these issues (dependent on the actual data source),
but a far better approach would be to just load the record you want.

Once way to do this is to use the AfterUpdate of the combo to change the
RecordSource of the form:

Private Sub ComboXXX_AfterUpdate()
' Load just the record that matches the control.
Dim strSQL As String

If Me.Dirty Then
Me.Dirty = False
End If
strSQL = "SELECT * FROM MyTable WHERE [MyNum] = " & Str(Me![ComboXXX]) &
";"
Me.RecordSource = strSQL
End If

If you want to continue with your exisitng approach, and your data is is
Access tables, you could try:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
...
However, we have experienced the Bookmark bug where this code takes you to a
record that looks like the one you want, but when you save your changes it
overwrites a different record. That experience was with a fully patched
Access 97 database, and a client who had several hundred thousand records in
the form. That means that this approach is not only inefficient, it is
potentially dangerous.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sandra said:
Is there some kind of limit on the number of records a
lookup combo box can search through?

I created a form with a combo box that looks up records
in the form. Using the following (partially) MS
generated code (I modified it a little):

Private Sub ComboXXX_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Not IsNull(Me.ComboXXX) Then
rs.FindFirst "[MyNum] = " & Str(Me![ComboXXX])
If rs.NoMatch Then
MsgBox "The number you entered was not found. Please
try again", vbDefaultButton1, "No Record Found"
Else
Me.Bookmark = rs.Bookmark
End If
Else
End If

This form is based on query that pulls from three large
tables. Now, what I noticed is that once that underlying
query hit 128,000 records, this code no longer works
properly. I fear I have hit up against an Access
limitation that I didn't know about. Is there some
reason why the bookmark property doesn't work after 128k
records? I was sure I read somewhere that Access can
handle unlimited bookmarks when a form is opened.

Any help in getting around this is greatly appreciated!!
Click to expand...
Click to expand...
 
Hi David

Yes, strictly it should be a binary comparison, so if you were trying to
check whether you were at the same record as in the clone, the module could
be set for binary compare, or you could use StrComp() to perform a binary
compare.

Most of the time, though we are assigning the bookmark rather than comparing
it. I would expect the assignment to have the correct results regardless of
the compare option for the module.

In the particular case I was referring to, our client was in another country
and the database was too large/sensitive to transfer, so we wrote some
logging routines that recorded the primary key of the record in the form's
Current, Delete, BeforeUpdate and AfterUpdate events. The logs clearly
showed that, after a deletion, the primary key value recorded in the Current
event was different from primary key in the BeforeUpdate event, indicating
that when Access loaded its buffer for the edit and then proceeded to write
the buffer back, it was overwriting the wrong record - classic Bookmark bug
symptoms. It was a fully patched A97 mdb, split, no duplicate dlls, patching
checked through version numbers, and the bug only showed up when there were
heaps of records (hundreds of thousands in this case).

Anyway, regarding your question, I think:
Me.Bookmark = rs.Bookmark
should be fine in any module. However:
If Me.Bookmark = rs.Bookmark Then
should be used only in a binary compare module.
So, for portability of code, we should use:
If StrComp(Me.Bookmark, rs.Bookmark, vbBinaryCompare) = 0 Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

david epsom dot com dot au said:
However, we have experienced the Bookmark bug where this code takes you
record that looks like the one you want, but when you save your changes
overwrites a different record. That experience was with a fully patched
Click to expand...

Allen, I see that the Bookmark is only supposed to be used in a
module where the setting is Option Compare Binary, but I never see
this discussed in the context of Bookmark bugs. Have you got an
opinion?

(david)


Allen Browne said:
Hi Sandra.

If you have more than 128k records, it is time to rethink the design. There
are ways around some of these issues (dependent on the actual data source),
but a far better approach would be to just load the record you want.

Once way to do this is to use the AfterUpdate of the combo to change the
RecordSource of the form:

Private Sub ComboXXX_AfterUpdate()
' Load just the record that matches the control.
Dim strSQL As String

If Me.Dirty Then
Me.Dirty = False
End If
strSQL = "SELECT * FROM MyTable WHERE [MyNum] = " &
Str(Me![ComboXXX]) &
";"
Me.RecordSource = strSQL
End If

If you want to continue with your exisitng approach, and your data is is
Access tables, you could try:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
...
However, we have experienced the Bookmark bug where this code takes you
to a
record that looks like the one you want, but when you save your changes
it
overwrites a different record. That experience was with a fully patched
Access 97 database, and a client who had several hundred thousand records in
the form. That means that this approach is not only inefficient, it is
potentially dangerous.


Sandra said:
Is there some kind of limit on the number of records a
lookup combo box can search through?

I created a form with a combo box that looks up records
in the form. Using the following (partially) MS
generated code (I modified it a little):

Private Sub ComboXXX_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Not IsNull(Me.ComboXXX) Then
rs.FindFirst "[MyNum] = " & Str(Me![ComboXXX])
If rs.NoMatch Then
MsgBox "The number you entered was not found. Please
try again", vbDefaultButton1, "No Record Found"
Else
Me.Bookmark = rs.Bookmark
End If
Else
End If

This form is based on query that pulls from three large
tables. Now, what I noticed is that once that underlying
query hit 128,000 records, this code no longer works
properly. I fear I have hit up against an Access
limitation that I didn't know about. Is there some
reason why the bookmark property doesn't work after 128k
records? I was sure I read somewhere that Access can
handle unlimited bookmarks when a form is opened.

Any help in getting around this is greatly appreciated!!
Click to expand...
Click to expand...
Click to expand...
 
Well, that is the question...
Me.Bookmark = rs.Bookmark
Click to expand...
does an implicit search of the recordset, and matches
on the bookmark. If it uses Database Compare for the
search, and uses a binary field for the bookmark, you
would expect occasional errors. Errors could be systematic
if the Bookmark creation algorithm was systematic, or
might only occur in large recordsets if the creation
algorithm was random.

So I still wonder about it.

But the problem you describe below seems to exclude
any kind of problem with Database Compare.

(david)



Allen Browne said:
Hi David

Yes, strictly it should be a binary comparison, so if you were trying to
check whether you were at the same record as in the clone, the module could
be set for binary compare, or you could use StrComp() to perform a binary
compare.

Most of the time, though we are assigning the bookmark rather than comparing
it. I would expect the assignment to have the correct results regardless of
the compare option for the module.

In the particular case I was referring to, our client was in another country
and the database was too large/sensitive to transfer, so we wrote some
logging routines that recorded the primary key of the record in the form's
Current, Delete, BeforeUpdate and AfterUpdate events. The logs clearly
showed that, after a deletion, the primary key value recorded in the Current
event was different from primary key in the BeforeUpdate event, indicating
that when Access loaded its buffer for the edit and then proceeded to write
the buffer back, it was overwriting the wrong record - classic Bookmark bug
symptoms. It was a fully patched A97 mdb, split, no duplicate dlls, patching
checked through version numbers, and the bug only showed up when there were
heaps of records (hundreds of thousands in this case).

Anyway, regarding your question, I think:
Me.Bookmark = rs.Bookmark
should be fine in any module. However:
If Me.Bookmark = rs.Bookmark Then
should be used only in a binary compare module.
So, for portability of code, we should use:
If StrComp(Me.Bookmark, rs.Bookmark, vbBinaryCompare) = 0 Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

david epsom dot com dot au said:
However, we have experienced the Bookmark bug where this code takes you
record that looks like the one you want, but when you save your changes
overwrites a different record. That experience was with a fully patched
Click to expand...

Allen, I see that the Bookmark is only supposed to be used in a
module where the setting is Option Compare Binary, but I never see
this discussed in the context of Bookmark bugs. Have you got an
opinion?

(david)


Allen Browne said:
Hi Sandra.

If you have more than 128k records, it is time to rethink the design. There
are ways around some of these issues (dependent on the actual data source),
but a far better approach would be to just load the record you want.

Once way to do this is to use the AfterUpdate of the combo to change the
RecordSource of the form:

Private Sub ComboXXX_AfterUpdate()
' Load just the record that matches the control.
Dim strSQL As String

If Me.Dirty Then
Me.Dirty = False
End If
strSQL = "SELECT * FROM MyTable WHERE [MyNum] = " &
Str(Me![ComboXXX]) &
";"
Me.RecordSource = strSQL
End If

If you want to continue with your exisitng approach, and your data is is
Access tables, you could try:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
...
However, we have experienced the Bookmark bug where this code takes you
to a
record that looks like the one you want, but when you save your changes
it
overwrites a different record. That experience was with a fully patched
Access 97 database, and a client who had several hundred thousand
Click to expand...
records
in
the form. That means that this approach is not only inefficient, it is
potentially dangerous.


Is there some kind of limit on the number of records a
lookup combo box can search through?

I created a form with a combo box that looks up records
in the form. Using the following (partially) MS
generated code (I modified it a little):

Private Sub ComboXXX_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
If Not IsNull(Me.ComboXXX) Then
rs.FindFirst "[MyNum] = " & Str(Me![ComboXXX])
If rs.NoMatch Then
MsgBox "The number you entered was not found. Please
try again", vbDefaultButton1, "No Record Found"
Else
Me.Bookmark = rs.Bookmark
End If
Else
End If

This form is based on query that pulls from three large
tables. Now, what I noticed is that once that underlying
query hit 128,000 records, this code no longer works
properly. I fear I have hit up against an Access
limitation that I didn't know about. Is there some
reason why the bookmark property doesn't work after 128k
records? I was sure I read somewhere that Access can
handle unlimited bookmarks when a form is opened.

Any help in getting around this is greatly appreciated!!
Click to expand...
Click to expand...
Click to expand...
 
I assume that Access uses the combobox common control provided by Windows,
except with enhancements. It does have a limit, and that probably is the
limit that is relevant here.

However I will explain something that confuses me here. It is confusing for
me to say "search through" a combobox; as far as I know, a combobox does not
search the database; a combobox gets filled with values from the database.
Perhaps what is meant is "contain", as in "is there a limt on the number of
items a combobox can contain". If that is not what is meant, then perhaps I
misunderstand the question.

Another thing: combobox is a comb-ination (combo) of an edit control and a
listbox. So I assume that the amount of data that can be contained by a
combobox is essentially the amount of data that can be contained by a
combobox is the same as for a listbox. If I remember correctly, that limit
is defined in terms of the total buffer size required to contain all the
relevant data, but I am not sure. I am nearly certain that the number of
records in the Access query or table or whatever is essentially not
relevant.

Since this is an old question, I won't try to find the place in the
Platfornm SDK that provides the specific documentation.
 
Back
Top