DB Self-destructed! <GRRRR>

**Chris O'C via AccessMonster.com** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

I normally recommend DIY because most people can build a db app with a little
(or a lot) of help from these groups or by taking some classes. It might
help preserve your sanity to consider it takes an experienced Access
developer about 30 to 45 minutes to normalize a spreadsheet into a db app
with three tables (that's two parent tables and a child table), import the
data, and build three simple data entry/edit forms with no code or macros.
Cleaning up 6 MB of data before the imports could take 15 minutes -- or hours
and hours, depending on how dirty your data is.

If you had a consultant interview you for an hour to find out what your needs
are and to check your system to find out what caused that "POOF!" episode,
spend another hour building the db design and import routines, and spend half
an hour explaining what he's doing and why (so you learn these valuable
skills yourself) and how to clean up the data, and let you clean up the data
for the imports yourself, you might find it well worth the cost for about 2
1/2 hours of a consultant's time.

The consultant will also suggest other improvements to automate your db app
beyond those simple objects he builds because Access is capable of so much
more than a simple db app with no code or queries.

Chris
Microsoft MVP

tekctrl wrote:

>(See earlier post "Record Is Too Large")
>
>This morning I just wiped out all earlier tables, kept the original tab
>delimited table and Form, then went back in and did a complete new DB split
>and accepted all defaults. Got the thing split, fields changed to date/time
>& memo as appropriate, field sizes trimmed to reasonable numbers, and saved
>the work. Rebuilt the data entry/edit Form and insured that it was pointing
>to the newly created tables. Closed MSAccess and went back in and
>everything worked perfectly! I then edited several records and
>saved...agai n it worked perfectly. No 'record is too large', no err's at
>all. Then I clicked on the Create New Record button, copied some text from
>another document with a Ctrl-C and pasted it into a Memo field in one of the
>records...POOF , the entire DB now consists of a single empty record (which
>AutoNumber refuses to create a record number for and which won't accept any
>data in any fields). All tables except for 3 were just gone. Closed
>MSAccess and went back in, the remaining 3 tables were now gone along with
>the Form.
>
>This was just a simple DB with no macro's, no code, just a few tables and
>one Form.
>
>After hunting all around the system for the tables & form, I ended up
>re-splitting (again) the original tab delimited table, again accepting all
>defaults and letting the system create the new tables. After the new tables
>were created, went into the new tables in Design mode to edit the field
>properties. Got the first few fields edited and then (surprise) got "Record
>is too Large". (!!!)
>
>Can anyone at all tell me just what the <expletive deletedis going on
>here?!? I really don't want to spend another Entire Day repeating this
>exercise if I'm missing something stupid and might end up having it
>self-destruct again.
>
>Jack

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

**lyle fairfield** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

Perhaps, you are frustrated because you think Access should behave in
a way which it does not.

I think you start with a text-file of lines/records with fields
delimited with something? and that you want to import this data into a
JET table and edit it in a form.

I suggest that you post a line or two from this text file, if there is
a text file, and indicate what you want to do with the data in general
not "database" terms. Someone may make a suggestion and the suggestion
may work.

On Jun 16, 7:36 pm, "tekctrl" <tekc...@earthl ink.netwrote:

This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB split
and accepted all defaults.

**Larry Linson** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

Whatever the cause, and I wouldn't care to do any remote debugging, it is an
Object Lesson in the need for regular backups, ESPECIALLY before making
"drastic" modifications as you did. If you were among the "backups are for
sissies" contingent before, I suspect you are now giving the matter
additional consideration. And, if you had to, as you say, hunt around the
system for forms and files, that's a pretty good indication you did not do a
backup immediately before the drastic mods.

When I am in development, I make a backup copy prior to every major
change -- I'm willing to risk a half-hour to an hour's work catching up, but
no more. That, of course, doesn't mean I have to back up every half-hour
(but I sometimes do so). Every project's development folder contains a
folder named Backups, filled with files identified by date and time.

Too, I got the impression that you were linking to a delimited file and
actually using it as a table without importing into Access tables... that
will leave you "somewhat exposed" because the Jet and ACCDB database engines
have built-in safeguards that text files just can't have. It is an approach
you might want to re-consider if that is what you are doing, or at least
discuss it, and your reasons for using it, here.

Best of luck with your recovery efforts.

Larry Linson
Microsoft Office Access MVP

**Keith Wilby** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:E-mdnR5RMbvHZcvVn Z2dnUVZ_rXinZ2d @earthlink.com. ..

(See earlier post "Record Is Too Large")
>
kept the original tab
delimited table

You were trying to edit this?

**Mike P** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

On 17 Jun, 00:36, "tekctrl" <tekc...@earthl ink.netwrote:

(See earlier post "Record Is Too Large")
>
This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB split
and accepted all defaults. Got the thing split, fields changed to date/time
& memo as appropriate, field sizes trimmed to reasonable numbers, and saved
the work. Rebuilt the data entry/edit Form and insured that it was pointing
to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err's at
all. Then I clicked on the Create New Record button, copied some text from
another document with a Ctrl-C and pasted it into a Memo field in one of the
records...POOF, the entire DB now consists of a single empty record (which
AutoNumber refuses to create a record number for and which won't accept any
data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along with
the Form.
>
This was just a simple DB with no macro's, no code, just a few tables and
one Form.
>
After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting all
defaults and letting the system create the new tables. After the new tables
were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got "Record
is too Large". (!!!)
>
Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.
>
Jack

Could it be that your delimited text file contains spurious data? Have
you tried examining its contents with a file viewer; download one if
you don't have one. It seems to me that your source data is the
culprit. Check for 'hidden characters' (tabs, newlines, etc) by
viewing the file in HEX mode.

Don't forget the 'compact and repair' option.

Mike P

**tekctrl** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

Yes, the original file did contain some spurious data and that was cleaned
before it was imported. The old file was successfully imported into the
JET/Access file format and works sort-of ok for the past few months except
for the individual records being pretty large and not being normalized.
That tells me that the source data was ok.

After a while the (functional) 'monolithic' file started displaying "record
is too large". I split it into several related tables (using
Tools/Analyze/Table), eliminating repeating fields. After the split, it
worked very well with no record size err's. Records were
opened/closed/edited without problem. Then I clicked on the Add Record
button to add a fresh record; a fresh record appeared but with no
Autonumber. Since Access increments the AutoNumber on record creation and
that didn't happen, that tells me that Access itself may be corrupted(?).
Further, the new (empty) record would not allow any data to be
entered...eithe r manually or pasted. I closed the Form and saw that 3 of
the 5 tables had disappeared. I restarted Access and found that the Form
and the remaining 2 tables were now gone....clean slate.

Last night I was able to re-split the file, saved the new tables, and
rebuilt the Form. It's still behaving strangely ("Enter parameter value;
table 3;IVdate" on Form open), but it's working.

The file containing the imported (tab delimited) data still works, including
the "record is too large" err, so I can't see it being a data issue. I
don't know why this happened, and this makes me very nervous about
preventing it from happening again.

"Mike P" <mikewpayne@tis cali.co.ukwrote in message
news:eb5a1c50-dc6d-4de7-a386-d2182ccb3b4a@b1 g2000hsg.google groups.com...

On 17 Jun, 00:36, "tekctrl" <tekc...@earthl ink.netwrote:

(See earlier post "Record Is Too Large")

This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB

split

and accepted all defaults. Got the thing split, fields changed to

date/time

& memo as appropriate, field sizes trimmed to reasonable numbers, and

saved

the work. Rebuilt the data entry/edit Form and insured that it was

pointing

to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err's

at

all. Then I clicked on the Create New Record button, copied some text

from

another document with a Ctrl-C and pasted it into a Memo field in one of

the

records...POOF, the entire DB now consists of a single empty record

(which

AutoNumber refuses to create a record number for and which won't accept

any

data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along

with

the Form.

This was just a simple DB with no macro's, no code, just a few tables

and

one Form.

After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting

all

defaults and letting the system create the new tables. After the new

tables

were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got

"Record

is too Large". (!!!)

Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.

Jack

>
Could it be that your delimited text file contains spurious data? Have
you tried examining its contents with a file viewer; download one if
you don't have one. It seems to me that your source data is the
culprit. Check for 'hidden characters' (tabs, newlines, etc) by
viewing the file in HEX mode.
>
Don't forget the 'compact and repair' option.
>
Mike P

**tekctrl** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

Allow me to correct a mis-impression. No, I wasn't linking to anything.
Quite some while back I'd imported an old DOS based database in ASCII tab
delimited format into a JET/Access file. I still have that and it's
working, but it's 'monolithic' and not in individual tables and hence is
running into record size restrictions.

Yesterday I split that file into 5 separate related tables (using
Tools/Analyze/Table) and built a new Form to view/edit the records. Presto,
the record size err msgs went away and it worked perfectly. I
edited/added/deleted records with no problem. I had a backed up copy of the
pre-split file but not yet of the post-split. I was expecting to do a
backup of the 'post-split' file at the end of the day since it was behaving
itself so nicely.

When the file went 'poof' I was doing nothing more than adding a record. No
linking, no format/propeties changes, no mods of any sort. Since Access
automatically increments the Autonumber field on CreateNewRecord , and the
new record had an empty Autonumber field, I have to think that Access itself
'hiccup'd'. The pre-split file ('tab delimited') was still present and
working 'post-poof', only the opened/in-use JET/Access tables and form
disappeared.

Since I have no idea why this happened, I have no idea how to prevent it
from happening again.

Jack

"Larry Linson" <bouncer@localh ost.notwrote in message
news:yfH5k.6696 0$bs3.8318@trnd dc07...

Whatever the cause, and I wouldn't care to do any remote debugging, it is

an

Object Lesson in the need for regular backups, ESPECIALLY before making
"drastic" modifications as you did. If you were among the "backups are for
sissies" contingent before, I suspect you are now giving the matter
additional consideration. And, if you had to, as you say, hunt around the
system for forms and files, that's a pretty good indication you did not do

a

backup immediately before the drastic mods.
>
When I am in development, I make a backup copy prior to every major
change -- I'm willing to risk a half-hour to an hour's work catching up,

but

no more. That, of course, doesn't mean I have to back up every half-hour
(but I sometimes do so). Every project's development folder contains a
folder named Backups, filled with files identified by date and time.
>
Too, I got the impression that you were linking to a delimited file and
actually using it as a table without importing into Access tables... that
will leave you "somewhat exposed" because the Jet and ACCDB database

engines

have built-in safeguards that text files just can't have. It is an

approach

you might want to re-consider if that is what you are doing, or at least
discuss it, and your reasons for using it, here.
>
Best of luck with your recovery efforts.
>
Larry Linson
Microsoft Office Access MVP
>
>

**tekctrl** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

FYI....after resplitting and restoring the files, I did a full backup before
even testing. Everything works perfectly until I click on the "Add Record"
button on the toolbar. Again, I got an empty record which Access refuses to
allow any data to be entered into. I can tab to the different fields, and I
can PageUp/Down to other records now, but the new record won't accept any
data, has no record #, and won't delete. It won't delete in Form view, it
won't delete in Table view. Another curiosity; Access now reports 509
records, but I can also view record #511. At least it hasn't (yet) erased
all open tables and forms as it did yesterday.

I think I'm just going to reload Access and see if it'll recover just the
tables from the backup without doing anything else.

Jack

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:E-mdnR5RMbvHZcvVn Z2dnUVZ_rXinZ2d @earthlink.com. ..

(See earlier post "Record Is Too Large")
>
This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB

split

and accepted all defaults. Got the thing split, fields changed to

date/time

& memo as appropriate, field sizes trimmed to reasonable numbers, and

saved

the work. Rebuilt the data entry/edit Form and insured that it was

pointing

to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err's at
all. Then I clicked on the Create New Record button, copied some text

from

another document with a Ctrl-C and pasted it into a Memo field in one of

the

records...POOF, the entire DB now consists of a single empty record (which
AutoNumber refuses to create a record number for and which won't accept

any

data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along with
the Form.
>
This was just a simple DB with no macro's, no code, just a few tables and
one Form.
>
After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting all
defaults and letting the system create the new tables. After the new

tables

were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got

"Record

is too Large". (!!!)
>
Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.
>
>
Jack
>
>

**Roger** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

On Jun 17, 9:48 am, "tekctrl" <tekc...@earthl ink.netwrote:

FYI....after resplitting and restoring the files, I did a full backup before
even testing. Everything works perfectly until I click on the "Add Record"
button on the toolbar. Again, I got an empty record which Access refuses to
allow any data to be entered into. I can tab to the different fields, and I
can PageUp/Down to other records now, but the new record won't accept any
data, has no record #, and won't delete. It won't delete in Form view, it
won't delete in Table view. Another curiosity; Access now reports 509
records, but I can also view record #511. At least it hasn't (yet) erased
all open tables and forms as it did yesterday.
>
I think I'm just going to reload Access and see if it'll recover just the
tables from the backup without doing anything else.
>
Jack
>
"tekctrl" <tekc...@earthl ink.netwrote in message
>
news:E-mdnR5RMbvHZcvVn Z2dnUVZ_rXinZ2d @earthlink.com. ..
>
>
>

(See earlier post "Record Is Too Large")

>

This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB

split

and accepted all defaults. Got the thing split, fields changed to

date/time

& memo as appropriate, field sizes trimmed to reasonable numbers, and

saved

the work. Rebuilt the data entry/edit Form and insured that it was

pointing

to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err'sat
all. Then I clicked on the Create New Record button, copied some text

from

another document with a Ctrl-C and pasted it into a Memo field in one of

the

records...POOF, the entire DB now consists of a single empty record (which
AutoNumber refuses to create a record number for and which won't accept

any

data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along with
the Form.

>

This was just a simple DB with no macro's, no code, just a few tables and
one Form.

>

After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting all
defaults and letting the system create the new tables. After the new

tables

were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got

"Record

is too Large". (!!!)

>

Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.

>

Jack- Hide quoted text -

>
- Show quoted text -

what does the 'add record' button do ?
if it fails on the first 'add record', what happens if you instead try
to enter the data directly into the table(s) bypassing the form

the autonumber usually isn't assigned until the contents of the form
is saved, I surprised to see that you expected an autonumber so early
in the process

**tekctrl** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

You're correct, my error...the Autonumber doesn't assign a number 'til the
record is saved.

I did a Detect/Repair & restarted the system, still get same thing; I can
view & edit existing records but can't add new records using either the
Insert/NewRecord or the AddRecord button in the toolbar. I get a blank
record, and can tab from field to field, but can't enter anything in any
fields. The original 'base' file still works ok. I'm going to blow it all
away and split it yet again. (sigh)

Jack

"Roger" <lesperancer@na tpro.comwrote in message
news:26feb36a-09c2-4ff6-bfd3-d09fd7084fab@d1 g2000hsg.google groups.com...
On Jun 17, 9:48 am, "tekctrl" <tekc...@earthl ink.netwrote:

FYI....after resplitting and restoring the files, I did a full backup

before

even testing. Everything works perfectly until I click on the "Add Record"
button on the toolbar. Again, I got an empty record which Access refuses

to

allow any data to be entered into. I can tab to the different fields, and

I

can PageUp/Down to other records now, but the new record won't accept any
data, has no record #, and won't delete. It won't delete in Form view, it
won't delete in Table view. Another curiosity; Access now reports 509
records, but I can also view record #511. At least it hasn't (yet) erased
all open tables and forms as it did yesterday.
>
I think I'm just going to reload Access and see if it'll recover just the
tables from the backup without doing anything else.
>
Jack
>
"tekctrl" <tekc...@earthl ink.netwrote in message
>
news:E-mdnR5RMbvHZcvVn Z2dnUVZ_rXinZ2d @earthlink.com. ..
>
>
>

(See earlier post "Record Is Too Large")

>

This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB

split

and accepted all defaults. Got the thing split, fields changed to

date/time

& memo as appropriate, field sizes trimmed to reasonable numbers, and

saved

the work. Rebuilt the data entry/edit Form and insured that it was

pointing

to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err's at
all. Then I clicked on the Create New Record button, copied some text

from

another document with a Ctrl-C and pasted it into a Memo field in one of

the

records...POOF, the entire DB now consists of a single empty record

(which

AutoNumber refuses to create a record number for and which won't accept

any

data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along

with

the Form.

>

This was just a simple DB with no macro's, no code, just a few tables

and

one Form.

>

After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting

all

defaults and letting the system create the new tables. After the new

tables

were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got

"Record

is too Large". (!!!)

>

Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.

>

Jack- Hide quoted text -

>
- Show quoted text -

what does the 'add record' button do ?
if it fails on the first 'add record', what happens if you instead try
to enter the data directly into the table(s) bypassing the form

the autonumber usually isn't assigned until the contents of the form
is saved, I surprised to see that you expected an autonumber so early
in the process

**Roger** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

On Jun 17, 11:50 am, "tekctrl" <tekc...@earthl ink.netwrote:

You're correct, my error...the Autonumber doesn't assign a number 'til the
record is saved.
>
I did a Detect/Repair & restarted the system, still get same thing; I can
view & edit existing records but can't add new records using either the
Insert/NewRecord or the AddRecord button in the toolbar. I get a blank
record, and can tab from field to field, but can't enter anything in any
fields. The original 'base' file still works ok. I'm going to blow it all
away and split it yet again. (sigh)
>
Jack
>
"Roger" <lesperan...@na tpro.comwrote in message
>
news:26feb36a-09c2-4ff6-bfd3-d09fd7084fab@d1 g2000hsg.google groups.com...
On Jun 17, 9:48 am, "tekctrl" <tekc...@earthl ink.netwrote:
>
>
>
>
>

FYI....after resplitting and restoring the files, I did a full backup

before

even testing. Everything works perfectly until I click on the "Add Record"
button on the toolbar. Again, I got an empty record which Access refuses

to

allow any data to be entered into. I can tab to the different fields, and

I

can PageUp/Down to other records now, but the new record won't accept any
data, has no record #, and won't delete. It won't delete in Form view, it
won't delete in Table view. Another curiosity; Access now reports 509
records, but I can also view record #511. At least it hasn't (yet) erased
all open tables and forms as it did yesterday.

>

I think I'm just going to reload Access and see if it'll recover just the
tables from the backup without doing anything else.

>

Jack

>

"tekctrl" <tekc...@earthl ink.netwrote in message

>

news:E-mdnR5RMbvHZcvVn Z2dnUVZ_rXinZ2d @earthlink.com. ..

>

(See earlier post "Record Is Too Large")

>

This morning I just wiped out all earlier tables, kept the original tab
delimited table and Form, then went back in and did a complete new DB

split

and accepted all defaults. Got the thing split, fields changed to

date/time

& memo as appropriate, field sizes trimmed to reasonable numbers, and

saved

the work. Rebuilt the data entry/edit Form and insured that it was

pointing

to the newly created tables. Closed MSAccess and went back in and
everything worked perfectly! I then edited several records and
saved...again it worked perfectly. No 'record is too large', no err's at
all. Then I clicked on the Create New Record button, copied some text

from

another document with a Ctrl-C and pasted it into a Memo field in one of

the

records...POOF, the entire DB now consists of a single empty record

(which

AutoNumber refuses to create a record number for and which won't accept

any

data in any fields). All tables except for 3 were just gone. Closed
MSAccess and went back in, the remaining 3 tables were now gone along

with

the Form.

>

This was just a simple DB with no macro's, no code, just a few tables

and

one Form.

>

After hunting all around the system for the tables & form, I ended up
re-splitting (again) the original tab delimited table, again accepting

all

defaults and letting the system create the new tables. After the new

tables

were created, went into the new tables in Design mode to edit the field
properties. Got the first few fields edited and then (surprise) got

"Record

is too Large". (!!!)

>

Can anyone at all tell me just what the <expletive deletedis going on
here?!? I really don't want to spend another Entire Day repeating this
exercise if I'm missing something stupid and might end up having it
self-destruct again.

>

Jack- Hide quoted text -

>

- Show quoted text -

>
what does the 'add record' button do ?
if it fails on the first 'add record', what happens if you instead try
to enter the data directly into the table(s) bypassing the form
>
the autonumber usually isn't assigned until the contents of the form
is saved, I surprised to see that you expected an autonumber so early
in the process- Hide quoted text -
>
- Show quoted text -

what is the recordsource of the form ? is it editable ?
if you open the table/query, can you add a new record ?

**Larry Linson** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

Open each table in design view, and make certain you don't see anything "out
of the ordinary". Open each other object in design view and do the same.
Create a new empty database and import from Access all the objects.

If I were you, and it were not too much trouble, instead of importing the
form where you are having the difficulties, I would re-create it.

A more drastic approach would be to Google on the (officially undocumented
and officially unsupported) Save as Text and Load from Text functions and
use them to transfer the objects. That will be about as "clean" as you are
going to be able to get your database.

Autonumber is not guaranteed to be monotonically increasing... there are a
number of situations in which a number can be lost (e.g., when a record is
deleted, its AutoNumber is not reused, when you begin to add a record, and
that addition is cancelled, after a certain point an AutoNumber may have
been assigned and can be lost). Thus, seeing a(n autonumber) RecordID field
of 511, when the table has 509 records is neither unusual, nor startling.

Larry Linson
Microsoft Office Access MVP

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:sZedneCYy_ hMS8rVnZ2dnUVZ_ ofinZ2d@earthli nk.com...

Allow me to correct a mis-impression. No, I wasn't linking to anything.
Quite some while back I'd imported an old DOS based database in ASCII tab
delimited format into a JET/Access file. I still have that and it's
working, but it's 'monolithic' and not in individual tables and hence is
running into record size restrictions.
>
Yesterday I split that file into 5 separate related tables (using
Tools/Analyze/Table) and built a new Form to view/edit the records.
Presto,
the record size err msgs went away and it worked perfectly. I
edited/added/deleted records with no problem. I had a backed up copy of
the
pre-split file but not yet of the post-split. I was expecting to do a
backup of the 'post-split' file at the end of the day since it was
behaving
itself so nicely.
>
When the file went 'poof' I was doing nothing more than adding a record.
No
linking, no format/propeties changes, no mods of any sort. Since Access
automatically increments the Autonumber field on CreateNewRecord , and the
new record had an empty Autonumber field, I have to think that Access
itself
'hiccup'd'. The pre-split file ('tab delimited') was still present and
working 'post-poof', only the opened/in-use JET/Access tables and form
disappeared.
>
Since I have no idea why this happened, I have no idea how to prevent it
from happening again.
>
>
Jack
>
"Larry Linson" <bouncer@localh ost.notwrote in message
news:yfH5k.6696 0$bs3.8318@trnd dc07...

>Whatever the cause, and I wouldn't care to do any remote debugging, it is

an

>Object Lesson in the need for regular backups, ESPECIALLY before making
>"drastic" modifications as you did. If you were among the "backups are
>for
>sissies" contingent before, I suspect you are now giving the matter
>additional consideration. And, if you had to, as you say, hunt around
>the
>system for forms and files, that's a pretty good indication you did not
>do

a

>backup immediately before the drastic mods.
>>
>When I am in development, I make a backup copy prior to every major
>change -- I'm willing to risk a half-hour to an hour's work catching up,

but

>no more. That, of course, doesn't mean I have to back up every half-hour
>(but I sometimes do so). Every project's development folder contains a
>folder named Backups, filled with files identified by date and time.
>>
>Too, I got the impression that you were linking to a delimited file and
>actually using it as a table without importing into Access tables... that
>will leave you "somewhat exposed" because the Jet and ACCDB database

engines

>have built-in safeguards that text files just can't have. It is an

approach

>you might want to re-consider if that is what you are doing, or at least
>discuss it, and your reasons for using it, here.
>>
>Best of luck with your recovery efforts.
>>
> Larry Linson
> Microsoft Office Access MVP
>>
>>

>
>

**lyle fairfield** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

An autonumber is assigned and used (up) if you think about adding a
new record, scratch your leg, or the telephone rings.

On Jun 17, 12:04 pm, Roger <lesperan...@na tpro.comwrote:

On Jun 17, 9:48 am, "tekctrl" <tekc...@earthl ink.netwrote:

the autonumber usually isn't assigned until the contents of the form
is saved, I surprised to see that you expected an autonumber so early
in the process

**Larry Linson** · Jun 27 '08, 07:26 PM

Re: DB Self-destructed! <GRRRR&gt ;

"lyle fairfield" <lyle.fairfield @gmail.comwrote

An autonumber is assigned and used (up) if you think
about adding a new record, scratch your leg, or the
telephone rings.

Or, if the AutoNumber Fairy decides to leave a sequence of your autonumbers
under the pillow of some other deserving boy or girl.

DB Self-destructed! <GRRRR>

DB Self-destructed! <GRRRR>

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment