A question from exprienced experts:Do my program works well after 3 months?

[Mota]

Hi;
I cant guess Number of Records limitation of a table in access and not sure
of the future of my progrm.So i need your comment if you are exprienced in
Access.
In my design,i have 2 related tables:the Parent would have near 50000 record
and the Child more than 150000 (maximum 200000),after 3 months.
I show these tables in a Form/Subform to the users and all operations i
expect to be done well with this huge number of records is:Entering data
thru these forms,deleting a record from parent table with a cascade delete
with child table (that means auto delete from child table),Finding a record
thru a combo box on the parent form and related records be shown in sub
form,and so on.
My progrm works on Win XP,Acc 2003,Pentium 4.
Can i be sure Access acomplishes these tasks without any problem for the
users,when having such a big tables?
Since changing this design and breaking these tables has many troubles for
me,i hope get the answer "Relax;this is ok" !
Thank you in advance for your consultation.

 

[Phlip]

I cant guess Number of Records limitation of a table in access and not
sure of the future of my progrm.So i need your comment if you are
exprienced in Access.
In my design,i have 2 related tables:the Parent would have near 50000
record and the Child more than 150000 (maximum 200000),after 3 months.
I show these tables in a Form/Subform to the users and all operations i
expect to be done well with this huge number of records is:Entering data
thru these forms,deleting a record from parent table with a cascade delete
with child table (that means auto delete from child table), ....
Can i be sure Access acomplishes these tasks without any problem for the
users,when having such a big tables?

Whip out your VBA and write a test case that pushes 1 million records into
those tables. Then either manually run the GUI and watch its performance, or
have the test case automatically run the GUI's controls and time them.

If they get slow, you will need to fiddle with the indexes.

Finding a record thru a combo box on the parent form and related records
be shown in sub form,and so on.
My progrm works on Win XP,Acc 2003,Pentium 4.

Per my recent spelunking in combo boxes, they are "virtual", which means the
GUI doesn't paint all their cells at the same time. The GUI links to a
Recordset which in turn links to a "cursor", which is the database-side
handle to the recordset's query. As you scroll through the combo box, for
each new line you see the GUI fetches its field value through the Recordset
through the cursor and into the raw table. The combo box does not copy every
value out each time the GUI populates its controls.

I don't know if that works with a million records. You must not speculate;
you must push in 1,000,000 records, with random data or something, to see
for yourself how the GUI will perform.

Since changing this design and breaking these tables has many troubles for
me,i hope get the answer "Relax;this is ok" !

Hm. If you write unit tests, designs get much easier to change. Backup your
database, make a tiny change, run all the tests, and repeat until the
design's better or the tests break. If the later, restore your backup and
try again.

 

[User]

Whip out your VBA and write a test case that pushes 1 million records into
those tables. Then either manually run the GUI and watch its performance, or
have the test case automatically run the GUI's controls and time them.

If they get slow, you will need to fiddle with the indexes.


Per my recent spelunking in combo boxes, they are "virtual", which means the
GUI doesn't paint all their cells at the same time. The GUI links to a
Recordset which in turn links to a "cursor", which is the database-side
handle to the recordset's query. As you scroll through the combo box, for
each new line you see the GUI fetches its field value through the Recordset
through the cursor and into the raw table. The combo box does not copy every
value out each time the GUI populates its controls.

Interesting spelunking. That is what I have found also.
I had read many books written by "experts" who claim the entire set of table
values was pulled down to populate the combo box and therefore combo box was
a bad idea. (It may be bad, but not for the reason they were describing).
Thanks for the clarification.

 

[User]

Hi;
I cant guess Number of Records limitation of a table in access and not sure
of the future of my progrm.So i need your comment if you are exprienced in
Access.
In my design,i have 2 related tables:the Parent would have near 50000 record
and the Child more than 150000 (maximum 200000),after 3 months.
I show these tables in a Form/Subform to the users and all operations i
expect to be done well with this huge number of records is:Entering data
thru these forms,deleting a record from parent table with a cascade delete
with child table (that means auto delete from child table),Finding a record
thru a combo box on the parent form and related records be shown in sub
form,and so on.
My progrm works on Win XP,Acc 2003,Pentium 4.
Can i be sure Access acomplishes these tasks without any problem for the
users,when having such a big tables?
Since changing this design and breaking these tables has many troubles for
me,i hope get the answer "Relax;this is ok" !
Thank you in advance for your consultation.

These are not such big tables for Access.
(I assume you mean 200000 child records total, not per parent record).
Access running on reasonable PC's will handle it.

But like all Access setups with shared backends, you must have a well
functioning network.
And the comments of Phlip are very applicable.

 

[Phlip]

Interesting spelunking. That is what I have found also.
I had read many books written by "experts" who claim the entire set of
table
values was pulled down to populate the combo box and therefore combo box
was
a bad idea. (It may be bad, but not for the reason they were describing).
Thanks for the clarification.

I hope you are still online because I must confess to making it all up,
based on general knowledge of "virtual" GUI controls, and on this signature:

Function FillCombo(fld As Control, id As Variant, _
row As Variant, col As Variant, code As Variant) _
As Variant

Select Case code
Case acLBInitialize
FillCombo = True
Case acLBOpen
FillCombo = Timer
Case acLBGetRowCount
FillCombo = -1
Case acLBGetColumnCount
FillCombo = 3
Case acLBGetColumnWidth
FillCombo = -1
Case acLBGetValue
FillCombo = rstsa!org1
FillCombo = rstsa!primary_pca
FillCombo = rstsa!division_name
End Select

If rstsa.EOF Then
Exit Function
Else
rstsa.MoveNext
End If

End Function
I found that on USENET, where someone used as the UDF for RowSourceType's
user defined function option. It looks generally like a callback that
virtualizes list fields, but I could be wrong. But if a UDF can do it, then
internal code can do it too. In theory.

 

[Mota]

Dear Philip;
In fact,I did this test before posting my question.I filled 2 tables with
50,000 and 200,000 records with random data.There was no problem.
But,both you and this test,are supposing that the behavior of a mdb does not
depend on the other tables and its file size.And this is where my doubt come
from.My file has about 80 tables.I cant guess which one would have how many
records after a couple of months.
Now,if you,as a proffessional,say that tables are handled separately by
Microsoft Jet DBEngine and there is no other threading in Access,i go on my
work and thank you for the peace of mind you give to me.Otherwise,for i cant
wait and see what happens,i need more comments from people like you,having
experience of working with growing tables up to 200,000 record.Have you ever
seen the behaviour of a MDB containing such a big tables?Can i count on
Access 2003 to handle my tables without any error?
By the way,changing that design is difficult because there is many
dependencies for these 2 important tables e.g. forms,reports,queries,and
even tables that must be reviewed and maybe changed.

Thank you so much for your help.

 

[Mota]

Yes,i mean 200,000 as a child total (and 50,000 for its parent)
Thank you for the good news.

 

[Marshall Barton]

I cant guess Number of Records limitation of a table in access and not sure
of the future of my progrm.So i need your comment if you are exprienced in
Access.
In my design,i have 2 related tables:the Parent would have near 50000 record
and the Child more than 150000 (maximum 200000),after 3 months.
I show these tables in a Form/Subform to the users and all operations i
expect to be done well with this huge number of records is:Entering data
thru these forms,deleting a record from parent table with a cascade delete
with child table (that means auto delete from child table),Finding a record
thru a combo box on the parent form and related records be shown in sub
form,and so on.
My progrm works on Win XP,Acc 2003,Pentium 4.
Can i be sure Access acomplishes these tasks without any problem for the
users,when having such a big tables?
Since changing this design and breaking these tables has many troubles for
me,i hope get the answer "Relax;this is ok" !

Let me add some additional thoughts here. A table is
limited to the size of its mdb file. Individual records are
limited to 2K and an mdb file is limited to 2GB. So if
there is only the one table and every record is maxed out
(very unlikely), you could still have 1 million records.

Note that it is easy to use multiple back end mdb files, so
you could go to the extreme of placing each table in a
separate mdb file, essentially making you total data store
virtually unlimited. This is not really advisable because
you can not enforce referential integrity between table in
different file.

The key to performance is to index the tables properly and
minimize the number of records retrieved by each operation.

Bottom line is that you should not have any space problems
and you can often improve any performance deficiencies by
making judicious design improvements.

 

[Mota]

What kind of design improvements are more effective on the big tables?
thank you.

 

[Phlip]

Now,if you,as a proffessional,say that tables are handled separately by
Microsoft Jet DBEngine and there is no other threading in Access,i go on
my work and thank you for the peace of mind you give to me.

Nope. Hold out for an answer from someone who has actually filled up an
Access database like that. (I suspect they migrate to SQL Server as the
spectre of Enterprise Integration rears its ugly head.)

By the way,changing that design is difficult because there is many
dependencies for these 2 important tables e.g. forms,reports,queries,and
even tables that must be reviewed and maybe changed.

The trick is changing in tiny steps. To rename and retype a field, add a
second redundant field. Get everything working. Add the field to one form.
Get everything working. Add it to the next form. And so on. Then remove the
old field from each form, then from the database. Backup between each step.

 

[Mota]

Ok,
Thank you.

Now,if you,as a proffessional,say that tables are handled separately by
Microsoft Jet DBEngine and there is no other threading in Access,i go on
my work and thank you for the peace of mind you give to me.

Nope. Hold out for an answer from someone who has actually filled up an
Access database like that. (I suspect they migrate to SQL Server as the
spectre of Enterprise Integration rears its ugly head.)

By the way,changing that design is difficult because there is many
dependencies for these 2 important tables e.g. forms,reports,queries,and
even tables that must be reviewed and maybe changed.

The trick is changing in tiny steps. To rename and retype a field, add a
second redundant field. Get everything working. Add the field to one form.
Get everything working. Add it to the next form. And so on. Then remove
the old field from each form, then from the database. Backup between each
step.

 

[Marshall Barton]

As I, and others, said earlier:

"The key to performance is to index the tables
properly and minimize the number of records
retrieved by each operation."

The database you are describing with a few hundred thousand
records is not really all that large and I don't think you
have to worry very much, at least not until you can actually
demonstrate a problem. It will be well worth your time to
follow Phlip's excellent advice.

 

[Mota]

Ok.Thanks a lot.

As I, and others, said earlier:

"The key to performance is to index the tables
properly and minimize the number of records
retrieved by each operation."

The database you are describing with a few hundred thousand
records is not really all that large and I don't think you
have to worry very much, at least not until you can actually
demonstrate a problem. It will be well worth your time to
follow Phlip's excellent advice.
--
Marsh
MVP [MS Access]

What kind of design improvements are more effective on the big tables?
thank you.