Anyone:
I have a simple MSAccess DB which was created from an old ASCII flatfile.
It works fine except for something that just started happening. I'll enter
info in a record, save the record, and try to move to another record and get
an Access error "Record is too large". The record is only half filled, with
many empty fields. If I remove the added data or delete some older data,
then it saves ok and works fine again. Whenever I'm getting that error, I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
Jack
aka te*****@earthlink.net 25  20267 
FYI...it doesn't matter what field I've added data to or which table is
affected, I get the same err msg whenever I appear to pass some threshold. I
can't find that error msg in any of the MSAccess documentation that I have
here, or anything that addresses restrictions to a records size. I'm in FORM
view when this happens.
"tekctrl" <te*****@earthlink.netwrote in message
news:Gv******************************@earthlink.co m...
Anyone:
I have a simple MSAccess DB which was created from an old ASCII flatfile.
It works fine except for something that just started happening. I'll
enter
info in a record, save the record, and try to move to another record and
get
an Access error "Record is too large". The record is only half filled,
with
many empty fields. If I remove the added data or delete some older data,
then it saves ok and works fine again. Whenever I'm getting that error, I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
Jack
aka te*****@earthlink.net
tekctrl wrote:
Anyone:
I have a simple MSAccess DB which was created from an old ASCII flatfile.
It works fine except for something that just started happening. I'll enter
info in a record, save the record, and try to move to another record and get
an Access error "Record is too large". The record is only half filled, with
many empty fields. If I remove the added data or delete some older data,
then it saves ok and works fine again. Whenever I'm getting that error, I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
Jack
aka te*****@earthlink.net
It might help those that help you if you provide an error number as well
as the error message. Anyway...this is what it says in help.
"You defined or imported a table with records larger than 2K. This error
occurs when you enter data into the record, not when you define the
table structure. Redefine the table by making some fields shorter,
removing unneeded fields, or moving some fields to other tables."
You might have a text field like State that's set to 50 chars. Set it
to 2. See what else you can trim down in size by modifying your table
structure.
Jamaica Days http://www.youtube.com/watch?v=q6CqtOJraBA
MSAccess 2002, sp3
6.6Mb
all running on a Win2K PC with 1Gb of RAM
"Linq Adams via AccessMonster.com" <u28780@uwewrote in message
news:8593f08eb0070@uwe...
What version of Access are you running and how big is the MDB file?
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200806/1
You need to take a look at this article: http://support.microsoft.com/kb/111304
Part of what it says is that record size is limited to approximately 2000
characters, not counting memo fields (which are not actually held in the
record.)
Sometimes, when importing data, the field size, for all fields imported,
defaults to 255 characters, which can quickly add up. Unlike Access, many
flat files use a fixed length field format. You might want to go into the
Table Design View and check out the field sizes.
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200806/1
Jamaica ... there was no err #, just the message.
Linq ... I believe you're right about the field sizes defaulting to 255 on
creation and then adding up to a LOT of wasted 'reserved' space. I went
back into the Properties and found and corrected a number that were like
that, including several single-character fields with a reserved field size
of 255. Also changed one Text field to a Memo type.
We'll see if my changes corrected the issue. Good thing I backed up the DB
first! <G>
Thank you, both! This is a very important DB and it's proper function is
critical here.
Jack
"Salad" <oi*@vinegar.comwrote in message
news:M_******************************@earthlink.co m...
tekctrl wrote:
Anyone:
I have a simple MSAccess DB which was created from an old ASCII
flatfile.
It works fine except for something that just started happening. I'll
enter
info in a record, save the record, and try to move to another record and
get
an Access error "Record is too large". The record is only half filled,
with
many empty fields. If I remove the added data or delete some older
data,
then it saves ok and works fine again. Whenever I'm getting that error,
I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
Jack
aka te*****@earthlink.net
It might help those that help you if you provide an error number as well
as the error message. Anyway...this is what it says in help.
"You defined or imported a table with records larger than 2K. This error
occurs when you enter data into the record, not when you define the
table structure. Redefine the table by making some fields shorter,
removing unneeded fields, or moving some fields to other tables."
You might have a text field like State that's set to 50 chars. Set it
to 2. See what else you can trim down in size by modifying your table
structure.
Jamaica Days
http://www.youtube.com/watch?v=q6CqtOJraBA
Linq:
Shuckey Darn! I reviewed the article that you suggested and went back into
the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data and
save without problem, but when I try to move to the next record I still get
the "record is too large" msg. No err #. So far it's only affecting a
single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain the
most data have all been changed to Memo type, and are all in separate tables
anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...anyone?
Jack
"Linq Adams via AccessMonster.com" <u28780@uwewrote in message
news:85940cb2d95ee@uwe...
You need to take a look at this article:
http://support.microsoft.com/kb/111304
Part of what it says is that record size is limited to approximately 2000
characters, not counting memo fields (which are not actually held in the
record.)
Sometimes, when importing data, the field size, for all fields imported,
defaults to 255 characters, which can quickly add up. Unlike Access, many
flat files use a fixed length field format. You might want to go into the
Table Design View and check out the field sizes.
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200806/1
tekctrl wrote:
Linq:
Shuckey Darn! I reviewed the article that you suggested and went back into
the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data and
save without problem, but when I try to move to the next record I still get
the "record is too large" msg. No err #.
You might have an error routine in the sub to save the record. It might be
Msgbox Err.Description.
Guess what? There's an Err.Number. You can even do this
Msgbox Err.Number & "-" & Err.Description
But I told you what the err number was so you don't need to bother with
it. I even provided the info regarding that error.
single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain the
most data have all been changed to Memo type, and are all in separate tables
anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...anyone?
In help look for "Data Type Summary". This will list the storage
requirements for each data type.
Also, look for "Specifications" in help for your education.
Now, open up your table in design mode. Add up the storage space for
each field based on the data type and for Text the number of characters
you set for it. Don't add Memo or OLE Object fields. You will have
more than 2000 bytes. Fix it or continue to be stumped.
Salad wrote:
tekctrl wrote:
>Linq:
the "record is too large" msg. No err #.
I didn't supply the error number. The error number is 3047.
Salad:
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text to
Memo or date/time to conserve space.
Yes, I did review the error msg article that you cited and it does appear to
be relevant. However, the DB here doesn't have the size that should trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
Jack
"Salad" <oi*@vinegar.comwrote in message
news:G4******************************@earthlink.co m...
tekctrl wrote:
Linq:
Shuckey Darn! I reviewed the article that you suggested and went back
into
the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data
and
save without problem, but when I try to move to the next record I still
get
the "record is too large" msg. No err #.
You might have an error routine in the sub to save the record. It might
be
Msgbox Err.Description.
Guess what? There's an Err.Number. You can even do this
Msgbox Err.Number & "-" & Err.Description
But I told you what the err number was so you don't need to bother with
it. I even provided the info regarding that error.
single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain
the
most data have all been changed to Memo type, and are all in separate
tables
anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...anyone?
In help look for "Data Type Summary". This will list the storage
requirements for each data type.
Also, look for "Specifications" in help for your education.
Now, open up your table in design mode. Add up the storage space for
each field based on the data type and for Text the number of characters
you set for it. Don't add Memo or OLE Object fields. You will have
more than 2000 bytes. Fix it or continue to be stumped.
tekctrl wrote:
Salad:
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text to
Memo or date/time to conserve space.
Yes, I did review the error msg article that you cited and it does appear to
be relevant. However, the DB here doesn't have the size that should trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
Jack
At this point I'd make a copy of your database (Fe/Be) and use it for
testing.
Regarding the recordsource of the form...is it something like
Select * From Table1
Or do you use specific field names?
Select Id, CustName, Address From Table1
If the first, try the second method.
If that doesn't work, perhaps try splitting the table. Copy Table1 to
Table2. Open up Table1 in design. Delete 1/2 of the fields from it.
Open Table2. Delete the rows (besides the key) you didn't delete in
Table1. If key is autonumber in Table2, change in to Number/Long.
Now create a query that links Table1 to Table2. Set that query as the
recordsource to the form. Does that help?
If that doesn't work...hmmm...delete all code from the form (in the MDB
fe copy). Then start deleting fields, open, run, and by trial and error
see if there's a specific field that causes a problem. If you have code
behind the form, prior to deleting fields but after deleting the code,
see if you can use the form correctly. Maybe there's some code that's
the culprit.
Try creating a new table with the memo fields you need.
Then use an Append query to populate it with the data from the old table.
Don't type in really long field names or descriptions.
The page size is now actually 4k in size, so you might even get around 4k
characters in a record before it's "too wide." MS says this depends on
Unicode Compression and language settings.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"tekctrl" <te*****@earthlink.netwrote in message
news:d9******************************@earthlink.co m...
Salad:
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text
to
Memo or date/time to conserve space.
Yes, I did review the error msg article that you cited and it does appear
to
be relevant. However, the DB here doesn't have the size that should
trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data
restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several
are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records
are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do
all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
I note that in one of your early messages, you mention that you are editing
using a form. Does this happen if you go in to the table and directly edit
the table, rather than going in through the form? Just a thought...
John
"tekctrl" <te*****@earthlink.netwrote in message
news:d9******************************@earthlink.co m...
Salad:
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text
to
Memo or date/time to conserve space.
Yes, I did review the error msg article that you cited and it does appear
to
be relevant. However, the DB here doesn't have the size that should
trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data
restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several
are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records
are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do
all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
Jack
"Salad" <oi*@vinegar.comwrote in message
news:G4******************************@earthlink.co m...
>tekctrl wrote:
Linq:
Shuckey Darn! I reviewed the article that you suggested and went back
into
the Design view for each of the tables. Edited the tables to shrink
the
reserved field sizes, changed some to Memo type, saved and closed.
Went
back into MSAccess and still having the same issue. :( I can add data
and
save without problem, but when I try to move to the next record I still
get
the "record is too large" msg. No err #.
You might have an error routine in the sub to save the record. It might
be
>Msgbox Err.Description.
Guess what? There's an Err.Number. You can even do this
Msgbox Err.Number & "-" & Err.Description
But I told you what the err number was so you don't need to bother with
it. I even provided the info regarding that error.
single record, but it's the single largest record in the DB even if
only
half of the fields are filled. The Text (comments) fields which
contain
the
most data have all been changed to Memo type, and are all in separate
tables
anyway, so they shouldn't be counting against the 2Kb record size
limit.
I'm open to suggestions...anyone?
In help look for "Data Type Summary". This will list the storage
requirements for each data type.
Also, look for "Specifications" in help for your education.
Now, open up your table in design mode. Add up the storage space for
each field based on the data type and for Text the number of characters
you set for it. Don't add Memo or OLE Object fields. You will have
more than 2000 bytes. Fix it or continue to be stumped.
Hope it works out for you! And backing up is the absolute, single most
intelligent thing you can ever do!
Let us know how you make out!
Linq
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200806/1
"tekctrl" <te*****@earthlink.netwrote:
>In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559.
You can't exclude Date/Time fields from your size count. You can exclude Memo fields
though.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Good question! I went into the Table view and took a look around and
noticed that I'd never deleted the old ASCII Tabdelimited file that was
imported from the original application. After the import was done, the
original (very large & full of repeating fields) was split into multiple
tables to do some normalization. After that, they were all saved and used
to create the data entry/edit Form. Imagine my surprise to find that the
core/base table, to which all others are related, had no records newer than
the table creation date. All of the edits appear to have been going back
into the massive & non-normalized tab delimited table. When I attempted to
edit that table to reduce redundancy and limit the field sizes, I got The
Same Error as I'm seeing in the data entry/edit Form. It won't allow any
changes at all to the field sizes or types without "Record is too large!".
So...the question becomes;
A) how does one migrate the data out of the old tab delimited table and into
the new normalized tables?
B) how does one unlink the data entry/edit Form from the old tab delimited
table and link it to the (normalized) core/base table? Will it be necessary
to just kill the Form and recreate it from the ground up? :(
At this point, my guess is that the err msg is a result of the Form still
pointing to the non-normalized tab delimited table and not to the slimmer
normalized tables.
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Dn*****************@newsfe10.phx...
I note that in one of your early messages, you mention that you are
editing
using a form. Does this happen if you go in to the table and directly edit
the table, rather than going in through the form? Just a thought...
John
Tony Toews [MVP] wrote:
"tekctrl" <te*****@earthlink.netwrote:
>>In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559.
You can't exclude Date/Time fields from your size count. You can exclude Memo fields
though.
Tony
Subtracting 2000-1559 we get 441 and at 8bytes for date/time he'd have
over 55 datetime fields. Along with the rest of the additional
fields...can we say Ouch?
Jack,
Glad we got that part figured out!
If I understand correctly:
-The form is pointing at the old, tab delimited data, which is the probable
source of your problem. To fix this problem, you need to develop a new set
of forms with form->subform relationships that match your new normalized
tables. There is really no way to attach the old form to the new data.
-Any data that has been edited since you did the original import hasn't been
migrated to the new tables. This may be OK, as you've said the edits are not
working anyway.
Good luck!
John
"tekctrl" <te*****@earthlink.netwrote in message
news:FZ******************************@earthlink.co m...
Good question! I went into the Table view and took a look around and
noticed that I'd never deleted the old ASCII Tabdelimited file that was
imported from the original application. After the import was done, the
original (very large & full of repeating fields) was split into multiple
tables to do some normalization. After that, they were all saved and used
to create the data entry/edit Form. Imagine my surprise to find that the
core/base table, to which all others are related, had no records newer
than
the table creation date. All of the edits appear to have been going back
into the massive & non-normalized tab delimited table. When I attempted
to
edit that table to reduce redundancy and limit the field sizes, I got The
Same Error as I'm seeing in the data entry/edit Form. It won't allow any
changes at all to the field sizes or types without "Record is too large!".
So...the question becomes;
A) how does one migrate the data out of the old tab delimited table and
into
the new normalized tables?
B) how does one unlink the data entry/edit Form from the old tab delimited
table and link it to the (normalized) core/base table? Will it be
necessary
to just kill the Form and recreate it from the ground up? :(
At this point, my guess is that the err msg is a result of the Form still
pointing to the non-normalized tab delimited table and not to the slimmer
normalized tables.
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Dn*****************@newsfe10.phx...
>I note that in one of your early messages, you mention that you are
editing
>using a form. Does this happen if you go in to the table and directly
edit
the table, rather than going in through the form? Just a thought...
John
Well, I'd thought that I'd done just that very thing when I'd split the
original table and normalized it. I do have the individual tables and their
relationships to the core/base table are all properly defined. It appears,
though, that the Form isn't using the core/base table or any of the smaller
normalized tables. At least that's my guess. When I open the data
entry/edit Form it displays "tabdelimited" after the table name. I'm unsure
now if the Form is even using the normalized tables at all.
I did look at the newer core/base table and there are records in there that
are newer than the table creation date, so at least Some of the new records
have made it there, so something changed after the original table was split
and normalized. However, the older non-normalized table has about 40 more
records in it than the normalized table, including the most current records
which aren't in the normalized table at all. Compounding my confusion, they
both have the same filename except that the Form displays as <FILENAME>
"tabdelimited".
I opened the normalized table in Table view and tried to Import from the tab
delimited table, but Access can't seem to find the tab delimited table. I
did an OS command-line 'search' for all MDB files on the system and found
neither the old table nor any of the new (post-split & normalization)
tables, but when I open Access there they are.
I'm very nervous about doing a copy from the tab delimited table and paste
to the normalized table as a means of Importing since the normalized table
is just a subset of the original table. The (limited) documentation
available here, as well as MSAccess F1/Help, assumes a lot of DB background
and training that I don't have and am not likely to get very soon.
Can anyone suggest how I might get the newer records migrated out of the old
monolithic tab delimited table and into the individual post-split &
normalized tables? Is killing/deleting the edit/data entry Form and
recreating it the only means by which I can move it's pointer away from the
old tab delimited table and make it use the new post-split/normalized
table(s)?
(As you may well have guessed by now, database admin is not my normal work.
I usually do Telecomm systems and desktop support work)
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Af***************@newsfe17.phx...
Jack,
Glad we got that part figured out!
If I understand correctly:
-The form is pointing at the old, tab delimited data, which is the
probable
source of your problem. To fix this problem, you need to develop a new set
of forms with form->subform relationships that match your new normalized
tables. There is really no way to attach the old form to the new data.
-Any data that has been edited since you did the original import hasn't
been
migrated to the new tables. This may be OK, as you've said the edits are
not
working anyway.
Good luck!
John
"tekctrl" <te*****@earthlink.netwrote in message
news:FZ******************************@earthlink.co m...
Good question! I went into the Table view and took a look around and
noticed that I'd never deleted the old ASCII Tabdelimited file that was
imported from the original application. After the import was done, the
original (very large & full of repeating fields) was split into multiple
tables to do some normalization. After that, they were all saved and
used
to create the data entry/edit Form. Imagine my surprise to find that
the
core/base table, to which all others are related, had no records newer
than
the table creation date. All of the edits appear to have been going
back
into the massive & non-normalized tab delimited table. When I attempted
to
edit that table to reduce redundancy and limit the field sizes, I got
The
Same Error as I'm seeing in the data entry/edit Form. It won't allow
any
changes at all to the field sizes or types without "Record is too
large!".
So...the question becomes;
A) how does one migrate the data out of the old tab delimited table and
into
the new normalized tables?
B) how does one unlink the data entry/edit Form from the old tab
delimited
table and link it to the (normalized) core/base table? Will it be
necessary
to just kill the Form and recreate it from the ground up? :(
At this point, my guess is that the err msg is a result of the Form
still
pointing to the non-normalized tab delimited table and not to the
slimmer
normalized tables.
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Dn*****************@newsfe10.phx...
I note that in one of your early messages, you mention that you are
editing
using a form. Does this happen if you go in to the table and directly
edit
the table, rather than going in through the form? Just a thought...
John
Salad <oi*@vinegar.comwrote:
>>>In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559.
You can't exclude Date/Time fields from your size count. You can exclude Memo fields
though.
Subtracting 2000-1559 we get 441 and at 8bytes for date/time he'd have
over 55 datetime fields. Along with the rest of the additional
fields...can we say Ouch?
Yes, that wasn't making sense but now it appears to be that his form was still based
on the unnormalized table.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
There are nowhere near that many Date/Time fields in the record. I count 11
date/time fields in the entire record. That gives me 88 bytes reserved.
"Salad" <oi*@vinegar.comwrote in message
news:jM******************************@earthlink.co m...
Tony Toews [MVP] wrote:
"tekctrl" <te*****@earthlink.netwrote:
>In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559.
You can't exclude Date/Time fields from your size count. You can
exclude Memo fields
though.
Tony
Subtracting 2000-1559 we get 441 and at 8bytes for date/time he'd have
over 55 datetime fields. Along with the rest of the additional
fields...can we say Ouch?
"tekctrl" <te*****@earthlink.netwrote:
>There are nowhere near that many Date/Time fields in the record. I count 11
date/time fields in the entire record. That gives me 88 bytes reserved.
But chances are there is one byte per field as overhead as a separator of some sort.
That's just a guess though.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
On Sat, 14 Jun 2008, tekctrl wrote:
Well, I'd thought that I'd done just that very thing when I'd split the
original table and normalized it. I do have the individual tables and their
relationships to the core/base table are all properly defined. It appears,
though, that the Form isn't using the core/base table or any of the smaller
normalized tables. At least that's my guess. When I open the data
entry/edit Form it displays "tabdelimited" after the table name. I'm unsure
now if the Form is even using the normalized tables at all.
I did look at the newer core/base table and there are records in there that
are newer than the table creation date, so at least Some of the new records
have made it there, so something changed after the original table was split
and normalized. However, the older non-normalized table has about 40 more
records in it than the normalized table, including the most current records
which aren't in the normalized table at all. Compounding my confusion, they
both have the same filename except that the Form displays as <FILENAME>
"tabdelimited".
I opened the normalized table in Table view and tried to Import from the tab
delimited table, but Access can't seem to find the tab delimited table. I
did an OS command-line 'search' for all MDB files on the system and found
neither the old table nor any of the new (post-split & normalization)
tables, but when I open Access there they are.
I'm very nervous about doing a copy from the tab delimited table and paste
to the normalized table as a means of Importing since the normalized table
is just a subset of the original table. The (limited) documentation
available here, as well as MSAccess F1/Help, assumes a lot of DB background
and training that I don't have and am not likely to get very soon.
Can anyone suggest how I might get the newer records migrated out of the old
monolithic tab delimited table and into the individual post-split &
normalized tables? Is killing/deleting the edit/data entry Form and
recreating it the only means by which I can move it's pointer away from the
old tab delimited table and make it use the new post-split/normalized
table(s)?
(As you may well have guessed by now, database admin is not my normal work.
I usually do Telecomm systems and desktop support work)
You may want to start over. A couple of important points you may be
missing though.
If you began with a tab-delimited ascii file (external to Access), Access
allows you to EITHER (1) import it into one or more Access tables, or (2)
LINK to the external ascii file. Linking doesn't import the contents, it
only saves the address of the file so that you can view it (and possibly
edit it).
It sounds like you have linked to it, not imported it. You can tell this
by looking at Tables tab of the Database Window, under 'Type'. If the Type
says 'Table', it is an Access table. If it is just a link to the external
file, it will say something like 'Table: Linked Text'.
If it is Linked, AND you can open the table and see data (from the Access
Tables Tab, not from the form you created), then the file definitely
exists (otherwise you couldn't open it). If you use the
Tools|DatabaseUtils|Linked Table Manager menu, you can see the filename by
finding that table on the list. That tells you exactly where it is on your
system so you don't need to search for it.
Your disk search for 'MDB' files only finds MS Access database files, it
won't find your tab-delimited ascii file, unless it was named
'something.mdb' (extremely unlikely).
At any rate, if you were starting over, you might begin by linking (rather
than importing) to this tab-delim table, and you might give it a name like
'txtMyBigTable'. The 'txt' prefix can serve are a reminder to you that it
is a linked text table, not an Access table.
Next, define the (empty) Access tables you want to ultimately hold the
data. You can reuse the ones you already created if you want. Then use
queries to select specific fields from the linked table (ascii file) and
append the data to your newly created Access tables. This will take
several queries depending on the the data.
Once you have extracted all the data from the tab-delim linked table into
Access tables, DELETE the 'txtMyBigTable' from the Tables Tab of the
Access database window. Note that this does not delete the file from your
hard disk, it only deletes the link to that file from the Access database.
Since you will have copied all the data into Access tables, you shouldn't
need to keep the link to the original data file any longer.
Then build your form using the Access tables. Since you will have deleted
the link to the text file, you cannot accidentally use that table in your
forms (as you apparently have done).
All this assumes you only need data from the big ascii file once. If that
file is constantly getting new data added to it from somewhere, and you
need to continually bring this new data into Access, then the solutions
are more complicated, although the basic approach is similar.
If you think you have a good grasp of how it works, you might be able to
fix things without starting over, but the essential thing you need to
understand is the difference between a native Access table (the data are
actually stored in the Access mdb file), versus an external tab-delimited
ascii file (the data are NOT stored in the Access MDB, only the filename
and a definition of the fieldnames). That said, you definitely don't want
to use a form based on the linked external file, especially one as large
and unnormalized as you describe.
Chris
On Sat, 14 Jun 2008, tekctrl wrote:
Well, I'd thought that I'd done just that very thing when I'd split the
original table and normalized it. I do have the individual tables and their
relationships to the core/base table are all properly defined. It appears,
though, that the Form isn't using the core/base table or any of the smaller
normalized tables. At least that's my guess. When I open the data
entry/edit Form it displays "tabdelimited" after the table name. I'm unsure
now if the Form is even using the normalized tables at all.
I did look at the newer core/base table and there are records in there that
are newer than the table creation date, so at least Some of the new records
have made it there, so something changed after the original table was split
and normalized. However, the older non-normalized table has about 40 more
records in it than the normalized table, including the most current records
which aren't in the normalized table at all. Compounding my confusion, they
both have the same filename except that the Form displays as <FILENAME>
"tabdelimited".
I opened the normalized table in Table view and tried to Import from the tab
delimited table, but Access can't seem to find the tab delimited table. I
did an OS command-line 'search' for all MDB files on the system and found
neither the old table nor any of the new (post-split & normalization)
tables, but when I open Access there they are.
I'm very nervous about doing a copy from the tab delimited table and paste
to the normalized table as a means of Importing since the normalized table
is just a subset of the original table. The (limited) documentation
available here, as well as MSAccess F1/Help, assumes a lot of DB background
and training that I don't have and am not likely to get very soon.
Can anyone suggest how I might get the newer records migrated out of the old
monolithic tab delimited table and into the individual post-split &
normalized tables? Is killing/deleting the edit/data entry Form and
recreating it the only means by which I can move it's pointer away from the
old tab delimited table and make it use the new post-split/normalized
table(s)?
(As you may well have guessed by now, database admin is not my normal work.
I usually do Telecomm systems and desktop support work)
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Af***************@newsfe17.phx...
>Jack,
Glad we got that part figured out!
If I understand correctly:
-The form is pointing at the old, tab delimited data, which is the
probable
>source of your problem. To fix this problem, you need to develop a new set
of forms with form->subform relationships that match your new normalized
tables. There is really no way to attach the old form to the new data.
-Any data that has been edited since you did the original import hasn't
been
>migrated to the new tables. This may be OK, as you've said the edits are
not
>working anyway.
Good luck!
John
"tekctrl" <te*****@earthlink.netwrote in message
news:FZ******************************@earthlink.c om...
>>Good question! I went into the Table view and took a look around and
noticed that I'd never deleted the old ASCII Tabdelimited file that was
imported from the original application. After the import was done, the
original (very large & full of repeating fields) was split into multiple
tables to do some normalization. After that, they were all saved and
used
>>to create the data entry/edit Form. Imagine my surprise to find that
the
>>core/base table, to which all others are related, had no records newer
than
the table creation date. All of the edits appear to have been going
back
>>into the massive & non-normalized tab delimited table. When I attempted
to
edit that table to reduce redundancy and limit the field sizes, I got
The
>>Same Error as I'm seeing in the data entry/edit Form. It won't allow
any
>>changes at all to the field sizes or types without "Record is too
large!".
>>>
So...the question becomes;
A) how does one migrate the data out of the old tab delimited table and
into
the new normalized tables?
B) how does one unlink the data entry/edit Form from the old tab
delimited
>>table and link it to the (normalized) core/base table? Will it be
necessary
to just kill the Form and recreate it from the ground up? :(
At this point, my guess is that the err msg is a result of the Form
still
>>pointing to the non-normalized tab delimited table and not to the
slimmer
>>normalized tables.
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Dn*****************@newsfe10.phx...
I note that in one of your early messages, you mention that you are
editing
using a form. Does this happen if you go in to the table and directly
edit
the table, rather than going in through the form? Just a thought...
John
Thanks for your intelligent and cogent response.
I checked and the tab delimited file is definately a table.
Yesterday I blew away all of the tables except the tab delimited table. I
then made a backup copy of the tab delimited file and renamed it. After
that, I did a manual Split of the original tab delimited table into several
smaller tables (Tools/Analyze/Table). I rebuilt the file and partially
normalized it, also editing the field sizes and types. I was still getting
"Record is too large" even after that, so I did a Repair/Compact on one of
the new tables and re-edited the field sizes/types, and now I'm no longer
seeing that msg.
However, now I can't enter new records. I can tab across the blank data
entry fields ok, but when I attempt to enter data in any fields nothing
happens. I can edit/copy/paste/save existing records ok. It's almost as
though the New Record function was giving me a blank form as 'read only'.
The older form, pointing to the tab delimited file, adds new records without
problem.
Jack
"Chris Martin" <cf*@vfemail.netwrote in message
news:Pi******************************@CMARTINTP60. med.unc.edu...
>
On Sat, 14 Jun 2008, tekctrl wrote:
Well, I'd thought that I'd done just that very thing when I'd split the
original table and normalized it. I do have the individual tables and
their
relationships to the core/base table are all properly defined. It
appears,
though, that the Form isn't using the core/base table or any of the
smaller
normalized tables. At least that's my guess. When I open the data
entry/edit Form it displays "tabdelimited" after the table name. I'm
unsure
now if the Form is even using the normalized tables at all.
I did look at the newer core/base table and there are records in there
that
are newer than the table creation date, so at least Some of the new
records
have made it there, so something changed after the original table was
split
and normalized. However, the older non-normalized table has about 40
more
records in it than the normalized table, including the most current
records
which aren't in the normalized table at all. Compounding my confusion,
they
both have the same filename except that the Form displays as <FILENAME>
"tabdelimited".
I opened the normalized table in Table view and tried to Import from the
tab
delimited table, but Access can't seem to find the tab delimited table.
I
did an OS command-line 'search' for all MDB files on the system and
found
neither the old table nor any of the new (post-split & normalization)
tables, but when I open Access there they are.
I'm very nervous about doing a copy from the tab delimited table and
paste
to the normalized table as a means of Importing since the normalized
table
is just a subset of the original table. The (limited) documentation
available here, as well as MSAccess F1/Help, assumes a lot of DB
background
and training that I don't have and am not likely to get very soon.
Can anyone suggest how I might get the newer records migrated out of the
old
monolithic tab delimited table and into the individual post-split &
normalized tables? Is killing/deleting the edit/data entry Form and
recreating it the only means by which I can move it's pointer away from
the
old tab delimited table and make it use the new post-split/normalized
table(s)?
(As you may well have guessed by now, database admin is not my normal
work.
I usually do Telecomm systems and desktop support work)
You may want to start over. A couple of important points you may be
missing though.
If you began with a tab-delimited ascii file (external to Access), Access
allows you to EITHER (1) import it into one or more Access tables, or (2)
LINK to the external ascii file. Linking doesn't import the contents, it
only saves the address of the file so that you can view it (and possibly
edit it).
It sounds like you have linked to it, not imported it. You can tell this
by looking at Tables tab of the Database Window, under 'Type'. If the Type
says 'Table', it is an Access table. If it is just a link to the external
file, it will say something like 'Table: Linked Text'.
If it is Linked, AND you can open the table and see data (from the Access
Tables Tab, not from the form you created), then the file definitely
exists (otherwise you couldn't open it). If you use the
Tools|DatabaseUtils|Linked Table Manager menu, you can see the filename by
finding that table on the list. That tells you exactly where it is on your
system so you don't need to search for it.
Your disk search for 'MDB' files only finds MS Access database files, it
won't find your tab-delimited ascii file, unless it was named
'something.mdb' (extremely unlikely).
At any rate, if you were starting over, you might begin by linking (rather
than importing) to this tab-delim table, and you might give it a name like
'txtMyBigTable'. The 'txt' prefix can serve are a reminder to you that it
is a linked text table, not an Access table.
Next, define the (empty) Access tables you want to ultimately hold the
data. You can reuse the ones you already created if you want. Then use
queries to select specific fields from the linked table (ascii file) and
append the data to your newly created Access tables. This will take
several queries depending on the the data.
Once you have extracted all the data from the tab-delim linked table into
Access tables, DELETE the 'txtMyBigTable' from the Tables Tab of the
Access database window. Note that this does not delete the file from your
hard disk, it only deletes the link to that file from the Access database.
Since you will have copied all the data into Access tables, you shouldn't
need to keep the link to the original data file any longer.
Then build your form using the Access tables. Since you will have deleted
the link to the text file, you cannot accidentally use that table in your
forms (as you apparently have done).
All this assumes you only need data from the big ascii file once. If that
file is constantly getting new data added to it from somewhere, and you
need to continually bring this new data into Access, then the solutions
are more complicated, although the basic approach is similar.
If you think you have a good grasp of how it works, you might be able to
fix things without starting over, but the essential thing you need to
understand is the difference between a native Access table (the data are
actually stored in the Access mdb file), versus an external tab-delimited
ascii file (the data are NOT stored in the Access MDB, only the filename
and a definition of the fieldnames). That said, you definitely don't want
to use a form based on the linked external file, especially one as large
and unnormalized as you describe.
Chris
On Sat, 14 Jun 2008, tekctrl wrote:
Well, I'd thought that I'd done just that very thing when I'd split the
original table and normalized it. I do have the individual tables and
their
relationships to the core/base table are all properly defined. It
appears,
though, that the Form isn't using the core/base table or any of the
smaller
normalized tables. At least that's my guess. When I open the data
entry/edit Form it displays "tabdelimited" after the table name. I'm
unsure
now if the Form is even using the normalized tables at all.
I did look at the newer core/base table and there are records in there
that
are newer than the table creation date, so at least Some of the new
records
have made it there, so something changed after the original table was
split
and normalized. However, the older non-normalized table has about 40
more
records in it than the normalized table, including the most current
records
which aren't in the normalized table at all. Compounding my confusion,
they
both have the same filename except that the Form displays as <FILENAME>
"tabdelimited".
I opened the normalized table in Table view and tried to Import from the
tab
delimited table, but Access can't seem to find the tab delimited table.
I
did an OS command-line 'search' for all MDB files on the system and
found
neither the old table nor any of the new (post-split & normalization)
tables, but when I open Access there they are.
I'm very nervous about doing a copy from the tab delimited table and
paste
to the normalized table as a means of Importing since the normalized
table
is just a subset of the original table. The (limited) documentation
available here, as well as MSAccess F1/Help, assumes a lot of DB
background
and training that I don't have and am not likely to get very soon.
Can anyone suggest how I might get the newer records migrated out of the
old
monolithic tab delimited table and into the individual post-split &
normalized tables? Is killing/deleting the edit/data entry Form and
recreating it the only means by which I can move it's pointer away from
the
old tab delimited table and make it use the new post-split/normalized
table(s)?
(As you may well have guessed by now, database admin is not my normal
work.
I usually do Telecomm systems and desktop support work)
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Af***************@newsfe17.phx...
Jack,
Glad we got that part figured out!
If I understand correctly:
-The form is pointing at the old, tab delimited data, which is the
probable
source of your problem. To fix this problem, you need to develop a new
set
of forms with form->subform relationships that match your new
normalized
tables. There is really no way to attach the old form to the new data.
-Any data that has been edited since you did the original import hasn't
been
migrated to the new tables. This may be OK, as you've said the edits
are
not
working anyway.
Good luck!
John
"tekctrl" <te*****@earthlink.netwrote in message
news:FZ******************************@earthlink.co m...
Good question! I went into the Table view and took a look around and
noticed that I'd never deleted the old ASCII Tabdelimited file that
was
>imported from the original application. After the import was done,
the
>original (very large & full of repeating fields) was split into
multiple
>tables to do some normalization. After that, they were all saved and
used
>to create the data entry/edit Form. Imagine my surprise to find that
the
>core/base table, to which all others are related, had no records newer
than
the table creation date. All of the edits appear to have been going
back
>into the massive & non-normalized tab delimited table. When I
attempted
>to
edit that table to reduce redundancy and limit the field sizes, I got
The
>Same Error as I'm seeing in the data entry/edit Form. It won't allow
any
>changes at all to the field sizes or types without "Record is too
large!".
>>
So...the question becomes;
A) how does one migrate the data out of the old tab delimited table
and
>into
the new normalized tables?
B) how does one unlink the data entry/edit Form from the old tab
delimited
>table and link it to the (normalized) core/base table? Will it be
necessary
to just kill the Form and recreate it from the ground up? :(
At this point, my guess is that the err msg is a result of the Form
still
>pointing to the non-normalized tab delimited table and not to the
slimmer
>normalized tables.
Jack
"JvC" <jo******@earthlink.netwrote in message
news:Dn*****************@newsfe10.phx...
I note that in one of your early messages, you mention that you are
editing
using a form. Does this happen if you go in to the table and directly
edit
the table, rather than going in through the form? Just a thought...
John
On Thu, 19 Jun 2008 10:03:19 -0500, "tekctrl" <te*****@earthlink.netwrote:
>Thanks for your intelligent and cogent response.
I checked and the tab delimited file is definately a table.
Zip up your tab delimitated table and send to me as an attachment to an E-mail.
Access is only a hobby for me and my time is free. If I can help, I will. If
I can't help, you've lost nothing. Your data is proprietary.
Chuck
-- This discussion thread is closed Replies have been disabled for this discussion. Similar topics
10 posts
views
Thread by KENNY L. CHEN |
last post: by
|
24 posts
views
Thread by hjbortol |
last post: by
|
reply
views
Thread by Dirk Försterling |
last post: by
|
3 posts
views
Thread by Jeff |
last post: by
|
3 posts
views
Thread by Vic |
last post: by
|
1 post
views
Thread by Just Me |
last post: by
|
reply
views
Thread by Carlo Razzeto |
last post: by
|
4 posts
views
Thread by bugbear |
last post: by
|
3 posts
views
Thread by =?Utf-8?B?Sm9obiBXYWxrZXI=?= |
last post: by
| | | | | | | | | | | | | | | | | | | |
