W
Wim
Are there any limits to the amount of VBA code that can be put into an Access
2003 database?
2003 database?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
Linq Adams via AccessMonster.com
None that I know. You're limited by the 2 GB total size (1 GB in v97, I think)
and there are a lot of limitations for the various objects, but not how much
code. per se. Here's a list of the specs from bytes.com:
http://bytes.com/forum/thread603897.html
and there are a lot of limitations for the various objects, but not how much
code. per se. Here's a list of the specs from bytes.com:
http://bytes.com/forum/thread603897.html
C
Chris O'C via AccessMonster.com
The limits are:
Max 1,024 characters per line.
Max 6,601 lines per procedure.
Max 65,545 lines per module.
Max 1,000 modules per database.
Max 2 GB file size.
But don't go anywhere near those limits. If Access doesn't reach over and
smack you (by giving baffling error messages for no reason), one of your
fellow developers will.
Chris
Microsoft MVP
Max 1,024 characters per line.
Max 6,601 lines per procedure.
Max 65,545 lines per module.
Max 1,000 modules per database.
Max 2 GB file size.
But don't go anywhere near those limits. If Access doesn't reach over and
smack you (by giving baffling error messages for no reason), one of your
fellow developers will.
Chris
Microsoft MVP
W
Wim
Thank you, Chris, for those numbers. They are very reassuring!
And, yes, I will stay well below the limits - I have already experienced
that Access can get out of control long before the limits are reached (in my
case it happened because I had too many fields in a table - but nowhere near
the official limit of 255).
Thanks.
And, yes, I will stay well below the limits - I have already experienced
that Access can get out of control long before the limits are reached (in my
case it happened because I had too many fields in a table - but nowhere near
the official limit of 255).
Thanks.
C
Chris O'C via AccessMonster.com
You ran into another limit. Each data page in Jet 4.0 is 4 KB, so no record
can exceed the space left over in that data page after subtracting the space
necessary for the page definition and row definitions. You can hit the 4 KB
limitation with as few as 16 max length (255) text fields.
Chris
Microsoft MVP
can exceed the space left over in that data page after subtracting the space
necessary for the page definition and row definitions. You can hit the 4 KB
limitation with as few as 16 max length (255) text fields.
Chris
Microsoft MVP
Thank you, Chris, for those numbers. They are very reassuring!
And, yes, I will stay well below the limits - I have already experienced
that Access can get out of control long before the limits are reached (in my
case it happened because I had too many fields in a table - but nowhere near
the official limit of 255).
Thanks.
[quoted text clipped - 13 lines]The limits are:
W
Wim
That sounds scary.
If you can reach the limit with 16 text fields (16 X 250 = 4000), doesn't
this mean you might as well reach the limit with just 1 memo field
(supposedly up to 65 000 or so characters) ?????
If you can reach the limit with 16 text fields (16 X 250 = 4000), doesn't
this mean you might as well reach the limit with just 1 memo field
(supposedly up to 65 000 or so characters) ?????
Chris O'C via AccessMonster.com said:You ran into another limit. Each data page in Jet 4.0 is 4 KB, so no record
can exceed the space left over in that data page after subtracting the space
necessary for the page definition and row definitions. You can hit the 4 KB
limitation with as few as 16 max length (255) text fields.
Chris
Microsoft MVP
Thank you, Chris, for those numbers. They are very reassuring!
And, yes, I will stay well below the limits - I have already experienced
that Access can get out of control long before the limits are reached (in my
case it happened because I had too many fields in a table - but nowhere near
the official limit of 255).
Thanks.
[quoted text clipped - 13 lines]The limits are:Are there any limits to the amount of VBA code that can be put into an Access
2003 database?
M
Marshall Barton
Wim said:That sounds scary.
If you can reach the limit with 16 text fields (16 X 250 = 4000), doesn't
this mean you might as well reach the limit with just 1 memo field
(supposedly up to 65 000 or so characters) ?????
Not so scary. The text part of a memo field is saved in
another part of the file. Only the header part of a memo
field in in the record.
Personally, I have never seen a table with 16 text fields.
If you have such a situation, then I suggest that it's time
to create a parallel 1-1 table.
M
Marshall Barton
Chris said:You ran into another limit. Each data page in Jet 4.0 is 4 KB, so no record
can exceed the space left over in that data page after subtracting the space
necessary for the page definition and row definitions. You can hit the 4 KB
limitation with as few as 16 max length (255) text fields.
Chris
Microsoft MVP
Nice summary of the various limits Chris.
I have not run into you before, how about logging into the
MVP Access newsgroup and intorducing yourself?
W
Wim
OK, that's clear.
I have one table with 24 memo fields in it. That should in principle be
possible?
I have one table with 24 memo fields in it. That should in principle be
possible?
M
Marshall Barton
Wim said:OK, that's clear.
I have one table with 24 memo fields in it. That should in principle be
possible?
Wow! That's a heck of a table. Are you sure it wouldn't be
better having a separate table with a foreign key and one
memo field?
Actually, because of the complex mechanism used to store
memo fields, a fair number of heavy duty developers use a
separate table for each memo field. This may(?) be overkill
since A2003 seems to have fixed a corruption problem when
multiple users edited the same record containing one or more
memo fields.
W
Wim
The 24 fields contain quite different information. I don't care much where
the information is saved, but I need 24 different controls on a form to guide
the user in his/her answer. Is there another way to solve this, except for
creating 24 different fields, one for each control?
the information is saved, but I need 24 different controls on a form to guide
the user in his/her answer. Is there another way to solve this, except for
creating 24 different fields, one for each control?
C
Chris O'C via AccessMonster.com
As Marshall explained, the text part of memo fields are saved in other data
pages. Only the pointer to the first of those data pages is saved within the
data page of the record consisting of regular data types.
And it's 16 X 255 = 4,080 that bumps you over the max record size limit for
the data page (4,096 bytes minus bytes needed for data page definition and
row definitions).
Chris
Microsoft MVP
pages. Only the pointer to the first of those data pages is saved within the
data page of the record consisting of regular data types.
And it's 16 X 255 = 4,080 that bumps you over the max record size limit for
the data page (4,096 bytes minus bytes needed for data page definition and
row definitions).
Chris
Microsoft MVP
C
Chris O'C via AccessMonster.com
It's possible, but are you sure you need 24 memo fields? I can see a
nightmare trying to query that data.
At a minimum, don't use bound text boxes in your forms on those memo fields
unless you place those memo fields in separate tables with a 1-to-1
relationship. Corrupted records commonly result with bound memo fields. If
you keep the memo fields in the same table as your regular data types, you
can have the code behind the bound form update the record's unbound memo
fields whenever the record updates.
Chris
Microsoft MVP
nightmare trying to query that data.
At a minimum, don't use bound text boxes in your forms on those memo fields
unless you place those memo fields in separate tables with a 1-to-1
relationship. Corrupted records commonly result with bound memo fields. If
you keep the memo fields in the same table as your regular data types, you
can have the code behind the bound form update the record's unbound memo
fields whenever the record updates.
Chris
Microsoft MVP
C
Chris O'C via AccessMonster.com
Thanks, I'll do that when I get time.
Chris
Microsoft MVP
Chris
Microsoft MVP
W
Wim
Chris, I am not sure if I understood you.
24 different tables with one memo field each (+ a primary key)?
You suggest I use unbound text boxes. What code would I need to have the
content of these unbound text boxes transferred to the table(s)? Can you give
an example?
Forgive my ignorance!
24 different tables with one memo field each (+ a primary key)?
You suggest I use unbound text boxes. What code would I need to have the
content of these unbound text boxes transferred to the table(s)? Can you give
an example?
Forgive my ignorance!
M
Marshall Barton
IMO, the most robust way to deal with a monster like that is
what Chris described in his response. Rather than confusing
things with two outlines of the same process, I'll bow out.
what Chris described in his response. Rather than confusing
things with two outlines of the same process, I'll bow out.
C
Chris O'C via AccessMonster.com
It would be only two tables, not 24. One table would contain the primary key
columns and your regular data type columns (text, long, date/time, etc.), and
the other table would have the same primary key and each of the 24 memo
fields. A 1-to-1 relationship between the two tables is required so both
contain the same values for the primary key. This is probably the most
common method of safely dealing with bound memo fields on bound forms, but it
requires an extra join in your queries to include records from both tables.
For the alternative (keeping memo fields in the same table as the regular
data types), I'll post some example code for you later today for the unbound
memo fields on a bound form.
And it's not ignorance. You're just not trained yet. Easy fix.
Chris
Microsoft MVP
columns and your regular data type columns (text, long, date/time, etc.), and
the other table would have the same primary key and each of the 24 memo
fields. A 1-to-1 relationship between the two tables is required so both
contain the same values for the primary key. This is probably the most
common method of safely dealing with bound memo fields on bound forms, but it
requires an extra join in your queries to include records from both tables.
For the alternative (keeping memo fields in the same table as the regular
data types), I'll post some example code for you later today for the unbound
memo fields on a bound form.
And it's not ignorance. You're just not trained yet. Easy fix.
Chris
Microsoft MVP
W
Wim
OK, that's clear. Will start experimenting with this. Thank you very much.
W
Wim
Hi Chris,
Hope you haven't forgotten about the code you would post (for the
alternative method)...
Hope you haven't forgotten about the code you would post (for the
alternative method)...
C
Chris O''C
David Fenton has instructions and sample code for unbound memo fields.
They're located near the end of his post.
http://groups.google.com/group/comp.databases.ms-access/msg/25e3524610bcbb79
Chris
Microsoft MVP
They're located near the end of his post.
http://groups.google.com/group/comp.databases.ms-access/msg/25e3524610bcbb79
Chris
Microsoft MVP
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.