"Record is too large"?

[Tony Toews [MVP]]

Re: "Record is too large"?

"tekctrl" <tekctrl@earthl ink.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/

[tekctrl]

Re: &quot;Record is too large&quot;?

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" <johnvonc@earth link.netwrote in message
news:Dnz4k.1526 $Y84.1335@newsf e10.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]

Re: &quot;Record is too large&quot;?

Tony Toews [MVP] wrote:

"tekctrl" <tekctrl@earthl ink.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?

[JvC]

Re: &quot;Record is too large&quot;?

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" <tekctrl@earthl ink.netwrote in message
news:FZKdnYc1qO MjrsnVnZ2dnUVZ_ uydnZ2d@earthli nk.com...

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" <johnvonc@earth link.netwrote in message
news:Dnz4k.1526 $Y84.1335@newsf e10.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
>>

>
>

[tekctrl]

Re: &quot;Record is too large&quot;?

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 "tabdelimit ed" 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>
"tabdelimit ed".

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" <johnvonc@earth link.netwrote in message
news:AfY4k.499$ sM4.453@newsfe1 7.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" <tekctrl@earthl ink.netwrote in message
news:FZKdnYc1qO MjrsnVnZ2dnUVZ_ uydnZ2d@earthli nk.com...

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" <johnvonc@earth link.netwrote in message
news:Dnz4k.1526 $Y84.1335@newsf e10.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]]

Re: &quot;Record is too large&quot;?

Salad <oil@vinegar.co mwrote:

>>>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/

[tekctrl]

Re: &quot;Record is too large&quot;?

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" <oil@vinegar.co mwrote in message
news:jM6dnUvcQ5 0PpsnVnZ2dnUVZ_ tLinZ2d@earthli nk.com...

Tony Toews [MVP] wrote:
>

"tekctrl" <tekctrl@earthl ink.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?

[Tony Toews [MVP]]

Re: &quot;Record is too large&quot;?

"tekctrl" <tekctrl@earthl ink.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/

[Chris Martin]

Re: &quot;Record is too large&quot;?


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 "tabdelimit ed" 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>
"tabdelimit ed".
>
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|DatabaseU tils|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 "tabdelimit ed" 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>
"tabdelimit ed".
>
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" <johnvonc@earth link.netwrote in message
news:AfY4k.499$ sM4.453@newsfe1 7.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" <tekctrl@earthl ink.netwrote in message
>news:FZKdnYc1q OMjrsnVnZ2dnUVZ _uydnZ2d@earthl ink.com...

>>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" <johnvonc@earth link.netwrote in message
>>news:Dnz4k.15 26$Y84.1335@new sfe10.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
>>>>
>>>
>>>

>>
>>

>
>
>

[tekctrl]

Re: &quot;Record is too large&quot;?

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" <cfm@vfemail.ne twrote in message
news:Pine.WNT.4 .64.08061816100 20.6640@CMARTIN TP60.med.unc.ed u...

>
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 "tabdelimit ed" 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>
"tabdelimit ed".

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|DatabaseU tils|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 "tabdelimit ed" 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>
"tabdelimit ed".

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" <johnvonc@earth link.netwrote in message
news:AfY4k.499$ sM4.453@newsfe1 7.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" <tekctrl@earthl ink.netwrote in message
news:FZKdnYc1qO MjrsnVnZ2dnUVZ_ uydnZ2d@earthli nk.com...
>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" <johnvonc@earth link.netwrote in message
>news:Dnz4k.152 6$Y84.1335@news fe10.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
>>>
>>
>>
>
>

[Chuck]

Re: &quot;Record is too large&quot;?

On Thu, 19 Jun 2008 10:03:19 -0500, "tekctrl" <tekctrl@earthl ink.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
--