Help with corrupt table

[Dan]

Hi, just ran into my first instance of a backend Access97 database not
compacting. I'm getting the "MSACCESS.EXE has generated errors.." message on
compact.

I've narrowed it down to the largest table which cotains 600k of records.
I've tried copying the database and trying to compact that -doesn't work.
I've tried Repair and then compact which also doesn't work. I've tried to
create a new database and import the tables but it flakes out at about
5/8ths of the way through the import of the corrupt table. I can't export
the table either. I can though, view the table contents all the way down to
the last record it seems.

I'm contemplating copying and pasting big chunks at a time into a copy of
the table definition, but am not sure if I can keep the exisiting primary
key autonumber field, unless I'm missing a way of doing this. Is it Peter
Miller time?

Thanks

Dan

[Peter Miller]

Dan,

On Wed, 14 Jan 2004 22:00:13 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

I'm contemplating copying and pasting big chunks at a time into a copy of
the table definition, but am not sure if I can keep the exisiting primary
key autonumber field, unless I'm missing a way of doing this. Is it Peter
Miller time?

Well, yes and no. 'Yes' in the sense that, well, here I am, but 'No'
in the sense that this is not a serious issue requiring any
specialized skills.

Go ahead and duplicate the table structure. An append query then
makes more sense for bringing over 'big chunks' than copying and
pasting, but if you *do* wish to copy/paste, first change the table
structure of the empty table so that the pk autonumber field is set to
a simple long integer field. Copy and paste to your hearts content,
but you'll probably find one or more records with memo or ole field
values that are corrupt and will not copy. Copy all records but
these, and for these records, copy all fields but the problematic
ones. As I said above, append queries do this more quickly and
smoothly, and more to the point, do not require modifying the pk field
from autonumber to long int.

In any event, if you do take the copy and paste route, remember to
port the data back to a copy of the table structure that *does* have
an autonumber pk field definition before you call it quits.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Dan]

"Peter Miller" <pm*****@pksolutions.com>

Dan,

On Wed, 14 Jan 2004 22:00:13 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

I'm contemplating copying and pasting big chunks at a time into a copy of
the table definition, but am not sure if I can keep the exisiting primary
key autonumber field, unless I'm missing a way of doing this. Is it Peter
Miller time?

Well, yes and no. 'Yes' in the sense that, well, here I am, but 'No'
in the sense that this is not a serious issue requiring any
specialized skills.

Go ahead and duplicate the table structure. An append query then
makes more sense for bringing over 'big chunks' than copying and
pasting, but if you *do* wish to copy/paste, first change the table
structure of the empty table so that the pk autonumber field is set to
a simple long integer field. Copy and paste to your hearts content,
but you'll probably find one or more records with memo or ole field
values that are corrupt and will not copy. Copy all records but
these, and for these records, copy all fields but the problematic
ones. As I said above, append queries do this more quickly and
smoothly, and more to the point, do not require modifying the pk field
from autonumber to long int.

In any event, if you do take the copy and paste route, remember to
port the data back to a copy of the table structure that *does* have
an autonumber pk field definition before you call it quits.

Peter Miller

Thanks for taking the time to respond Peter.

Yes, I'll try an append query first as you suggest.

I'm a bit concerned as to why it's happened now. I've never been able to not
compact the database in the past 7 years we've had it. I normally do
compacts once every month/two months and never had a problem. In my mind
it's quite serious if the unable-to-compact contingency methods don't work
(import into new database etc)! I'm also wondering whether to decompile but
can't see the point as this is a backend with only a handful of tables, no
code to decompile.

Lastly, if I do sort this out, how on earth do you check for this type of
corruption routinely, or are you supposed to discover them only when you
compact? I might have to think about daily compacting if that's the case!

Thanks for your help,

Dan

[Peter Miller]

On Thu, 15 Jan 2004 00:09:12 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

Thanks for taking the time to respond Peter.
You're welcome.
Yes, I'll try an append query first as you suggest.
Good.
I'm a bit concerned as to why it's happened now. I've never been able to not
compact the database in the past 7 years we've had it. I normally do
compacts once every month/two months and never had a problem.
I think once a week to once every two months is an ok schedule. I'd
recommend once a week myself, but as long as its done periodically,
its no big deal. Of course, I'm talking about production here.
In my mind
it's quite serious if the unable-to-compact contingency methods don't work
(import into new database etc)!
Well, they won't. The reason the compact fails is that there is
damage to system tables (serious) or bad data in a data table (not
serious). The latter is what you experienced, and doing anything with
the table as a whole (importing, exporting, converting, compacting,
etc) will all fail for the very same reason - Jet can not read the
corrupted field value so it can not complete the action.
I'm also wondering whether to decompile but
can't see the point as this is a backend with only a handful of tables, no
code to decompile.
There is no point in such a case.
Lastly, if I do sort this out, how on earth do you check for this type of
corruption routinely, or are you supposed to discover them only when you
compact? I might have to think about daily compacting if that's the case!
Well, let's keep perspective here. Attempting to read everything in
the file will tell you whether everything in the file can be read.
Compacting does this (and other things). Importing everything to e
new file does this too. I don't see any reason why nightly compacts
are needed. If a record gains a corrupt field value mid week, its
true you may not know it until the end of the week, but even if you
found out mid-week, it does not prevent it from occurring.

Basically, you want to do what you can to ensure your network is
stable and your app well designed. Even so, corruption can not be
prevented in Jet, but it can be minimized. If you find corruption
like that you discovered recently, fix it and move forward. If the
corruption occurs more frequently than you are comfortable with, and
you have done everything you can to improve stability, then its time
to consider using a true client/server rdbms like sql server, oracle,
postgresql, db2, etc.
Thanks for your help,

You're more than welcome.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Tom van Stiphout]

On Wed, 14 Jan 2004 22:00:13 -0000, "Dan" <no****@nospam.com> wrote:

In addition to everything Peter said: regularly check your backups and
your ability to restore from backup. There likely WILL come a time
you'll need it.

-Tom.

Hi, just ran into my first instance of a backend Access97 database not
compacting. I'm getting the "MSACCESS.EXE has generated errors.." message on
compact.

I've narrowed it down to the largest table which cotains 600k of records.
I've tried copying the database and trying to compact that -doesn't work.
I've tried Repair and then compact which also doesn't work. I've tried to
create a new database and import the tables but it flakes out at about
5/8ths of the way through the import of the corrupt table. I can't export
the table either. I can though, view the table contents all the way down to
the last record it seems.

I'm contemplating copying and pasting big chunks at a time into a copy of
the table definition, but am not sure if I can keep the exisiting primary
key autonumber field, unless I'm missing a way of doing this. Is it Peter
Miller time?

Thanks

Dan

[Peter Miller]

On Wed, 14 Jan 2004 22:09:41 -0700, Tom van Stiphout
<to*****@no.spam.cox.net> wrote in comp.databases.ms-access:

In addition to everything Peter said: regularly check your backups and
your ability to restore from backup. There likely WILL come a time
you'll need it.

Absolutely.

Periodically, inform the necessary people that a restore of the db is
necessary, and see whether the work put into backups, off site storage
etc, actually is reliable. Make sure that the company's backups are
just that, and not simply the nightly process that everyone assumes is
going to be useful.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Dan]

Thanks Peter and Tom,

After trying the append query, by process of elimination (i.e. appending
till it crashed) I managed to narrow it down to a memo field in one record.
Deleting the record now allows compacting (at least on my test copy).

Am wondering whether to:

a) just delete the record from my live table and reinput the lost info
(apart from the memo field that crashes Access when I try to view it!) and
then compact
b) recreate the table and append everything but the corrupt record then
deleet the old table
c) create a new database and import all the tables and data minus the
corrupt record

Is a) sufficient or do I really need to look at b) or c)?

Thanks again for all your help, I was getting a bit panicky yesterday!

Dan

[Mike MacSween]

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:5r********************************@4ax.com...

In any event, if you do take the copy and paste route, remember to
port the data back to a copy of the table structure that *does* have
an autonumber pk field definition before you call it quits.
I've sometimes wondered about this. If you've got data somewhere else,
whether it's an Access table or not. And you want to get it into an Access
table with an autonumber how do you do it, if you want to preserve the
actual autonumber values?

You can import the AN values into a long integer field, but can't then
convert that back to an AN. I know the AN values shouldn't have 'meaning'
but if there are FK fields in other tables then you need to preserve the
values.

I assume there's a standard technique for this. I imagined that you
import/append or whatever the data, including the child tables. Reset the
relationship to the new long int field, create a new AN field, then, with
cascading updates ON, set the value of the PK (long int) field to that of
the AN field, change the PK to the AN field, and the relationship, and
delete the old PK long int field.

Is that the way to go about it?

Yours, Mike MacSween



Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Tom van Stiphout]

On Thu, 15 Jan 2004 10:56:05 -0000, "Dan" <no****@nospam.com> wrote:

a is fine, if you can't just delete the corrupt memo value.
-Tom.

Thanks Peter and Tom,

After trying the append query, by process of elimination (i.e. appending
till it crashed) I managed to narrow it down to a memo field in one record.
Deleting the record now allows compacting (at least on my test copy).

Am wondering whether to:

a) just delete the record from my live table and reinput the lost info
(apart from the memo field that crashes Access when I try to view it!) and
then compact
b) recreate the table and append everything but the corrupt record then
deleet the old table
c) create a new database and import all the tables and data minus the
corrupt record

Is a) sufficient or do I really need to look at b) or c)?

Thanks again for all your help, I was getting a bit panicky yesterday!

Dan

[Peter Miller]

On Thu, 15 Jan 2004 11:12:43 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote in
comp.databases.ms-access:

I've sometimes wondered about this. If you've got data somewhere else,
whether it's an Access table or not. And you want to get it into an Access
table with an autonumber how do you do it, if you want to preserve the
actual autonumber values?
Append queries.
I assume there's a standard technique for this. I imagined that you
import/append or whatever the data, including the child tables. Reset the
relationship to the new long int field, create a new AN field, then, with
cascading updates ON, set the value of the PK (long int) field to that of
the AN field, change the PK to the AN field, and the relationship, and
delete the old PK long int field.

Is that the way to go about it?

No, because you can't write values directly to an AN field in the
current table. Remember that as soon as you create your new AN field,
AN values are assigned to existing records. So the way to do it is to
create a second table, change the long int field back to AN (when no
data is present) then append data from the table with the long into
field to the empty table with the AN field. This does the trick.
Obviously, referential integrity issues must be dealt with too if
present.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Peter Miller]

Dan,

On Thu, 15 Jan 2004 10:56:05 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

a) just delete the record from my live table and reinput the lost info
(apart from the memo field that crashes Access when I try to view it!) and
then compact
b) recreate the table and append everything but the corrupt record then
deleet the old table
c) create a new database and import all the tables and data minus the
corrupt record

Is a) sufficient or do I really need to look at b) or c)?

Thanks again for all your help, I was getting a bit panicky yesterday!

Dan

Actually, (a) is probably fine, but you should follow (a) up with (c).
Remember that after (a), there's no longer any need to deal with the
problem record as part of (c), so simply creating a new db then
importing everything solves (c). Why the new db? Because it is the
best way to ensure that it is a clean file. Simply repairing the
damage you've found may not correct other corruption issues at a lower
threshold, and it is not uncommon for corruption to both (a) spread
and (b) involve multiple instances.

So freely skip (b), but don't skip (c).

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Mike MacSween]

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:uu********************************@4ax.com...

I assume there's a standard technique for this. I imagined that you
import/append or whatever the data, including the child tables. Reset the
relationship to the new long int field, create a new AN field, then, with
cascading updates ON, set the value of the PK (long int) field to that of
the AN field, change the PK to the AN field, and the relationship, and
delete the old PK long int field.

Is that the way to go about it?
No, because you can't write values directly to an AN field in the
current table.

Yes, I know. I wasn't proposing writing values into an AN field.
Obviously, referential integrity issues must be dealt with too if
present.

Yes. That was my point.

Yours, Mike MacSween

[Mike MacSween]

Dan

I can't see you original post for some reason.

Do you have an tables which have a foreign key reference to the autonumber
field in the table you're having trouble with?

What happens when you import (by whatever method) your records into the new
table, with a 'new' autonumber field? Unless you're very lucky the
autonumber _values_ in the new table won't be the same as the autonumber
values in the old table. So your foreign key references will be incorrect.

Mike

[Peter Miller]

Mike,

On Thu, 15 Jan 2004 15:59:20 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote in
comp.databases.ms-access:

Obviously, referential integrity issues must be dealt with too if
present.

Yes. That was my point.

I know you do, but since others reading the thread may not be aware of
that, I didn't want to finish the post without making clear that
append queries don't solve referential integrity issues, even though
they do allow writing specific values to AN fields.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Dan]

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:j4********************************@4ax.com...

Dan,

On Thu, 15 Jan 2004 10:56:05 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

a) just delete the record from my live table and reinput the lost info
(apart from the memo field that crashes Access when I try to view it!) andthen compact
b) recreate the table and append everything but the corrupt record then
deleet the old table
c) create a new database and import all the tables and data minus the
corrupt record

Is a) sufficient or do I really need to look at b) or c)?

Thanks again for all your help, I was getting a bit panicky yesterday!

Dan

Actually, (a) is probably fine, but you should follow (a) up with (c).
Remember that after (a), there's no longer any need to deal with the
problem record as part of (c), so simply creating a new db then
importing everything solves (c). Why the new db? Because it is the
best way to ensure that it is a clean file. Simply repairing the
damage you've found may not correct other corruption issues at a lower
threshold, and it is not uncommon for corruption to both (a) spread
and (b) involve multiple instances.

So freely skip (b), but don't skip (c).

Peter Miller

Thanks once again Peter, I thought as much. I'll do (a) and (c) -better to
be safe than sorry!

Thanks

Dan

[Dan]

Hi Mike,

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:40**********************@news.aaisp.net.uk...

Dan

I can't see you original post for some reason.

Do you have an tables which have a foreign key reference to the autonumber
field in the table you're having trouble with?

What happens when you import (by whatever method) your records into the new table, with a 'new' autonumber field? Unless you're very lucky the
autonumber _values_ in the new table won't be the same as the autonumber
values in the old table. So your foreign key references will be incorrect.

Mike

Yes I do have tables that reference the autonumber field in the corrupt
table. Peter's append method works though, in that it retains the existing
autonumber value. I've done this about 6 times today to make sure! Just to
clarify, I'm creating a new table which is based on a copy of the corrupt
table's definition. I run an append query to append corrupt table records to
new table. The appended autonumbers in the table remain the same so foreign
key references are correct.

Dan

[Peter Miller]

On Thu, 15 Jan 2004 17:20:36 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

Yes I do have tables that reference the autonumber field in the corrupt
table. Peter's append method works though, in that it retains the existing
autonumber value. I've done this about 6 times today to make sure! Just to
clarify, I'm creating a new table which is based on a copy of the corrupt
table's definition. I run an append query to append corrupt table records to
new table. The appended autonumbers in the table remain the same so foreign
key references are correct.

Yes, but remember that although the foreign key references will be
fine, because you're creating a new table, existing relationships and
referential integrity are not established for the new table just
because they existed for the old one.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Mike MacSween]

Humble pie time.

I've just tried it. It seems I was wrong.

My apologies to both of you.

Though it's actually a blessing for me.

In sackcloth and ashes, Mike MacSween

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:mk********************************@4ax.com...

On Thu, 15 Jan 2004 17:20:36 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

Yes I do have tables that reference the autonumber field in the corrupt
table. Peter's append method works though, in that it retains the existingautonumber value. I've done this about 6 times today to make sure! Just toclarify, I'm creating a new table which is based on a copy of the corrupt
table's definition. I run an append query to append corrupt table records tonew table. The appended autonumbers in the table remain the same so foreignkey references are correct.

Yes, but remember that although the foreign key references will be
fine, because you're creating a new table, existing relationships and
referential integrity are not established for the new table just
because they existed for the old one.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[Dan]

Hi Peter,

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:mk********************************@4ax.com...

On Thu, 15 Jan 2004 17:20:36 -0000, "Dan" <no****@nospam.com> wrote in
comp.databases.ms-access:

Yes I do have tables that reference the autonumber field in the corrupt
table. Peter's append method works though, in that it retains the existingautonumber value. I've done this about 6 times today to make sure! Just toclarify, I'm creating a new table which is based on a copy of the corrupt
table's definition. I run an append query to append corrupt table records tonew table. The appended autonumbers in the table remain the same so foreignkey references are correct.

Yes, but remember that although the foreign key references will be
fine, because you're creating a new table, existing relationships and
referential integrity are not established for the new table just
because they existed for the old one.

Hmmm, I'm a bit confused. How do I establish RE and rebuild the existing
relationships? The relationship structure seems to be there as it was
before. Do I need to delete and re-do the relationships in the Tools |
Relationships window? Also, whilst only a couple of the tables have
relationships on the backend, the front/client ends have far more -do these
need re-establishing again as well? I apologise if these are daft questions!

Thanks

Dan

[David W. Fenton]

pm*****@pksolutions.com (Peter Miller) wrote in
<uu********************************@4ax.com>:

On Thu, 15 Jan 2004 11:12:43 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote in
comp.databases.ms-access:

I assume there's a standard technique for this. I imagined that
you import/append or whatever the data, including the child
tables. Reset the relationship to the new long int field, create
a new AN field, then, with cascading updates ON, set the value of
the PK (long int) field to that of the AN field, change the PK to
the AN field, and the relationship, and delete the old PK long
int field.

Is that the way to go about it?

No, because you can't write values directly to an AN field in the
current table. . . .

What?

An Autonumber that is used in relationships (i.e., is a PK) has
these characteristics:

1. a long integer field.

2. unique, non-Null index.

3. default value of Max([last value used])+1 or Rand().

That's it.

Because of that, values that match the data type and do not violate
the index can be appended.

So, if you're appending data from an existing schema into a new one
and want to retain the Autonumber values, you just append them to
the Autonumber field. If they are valid PK values in the source
table, they'll be valid in the destination table.

You don't have to muck around with changing data types at all.
. . . Remember that as soon as you create your new AN
field, AN values are assigned to existing records. . . .
This is only true if you're appending to a table that already has
values in it that collide with the values of the source data.
. . . So the way to
do it is to create a second table, change the long int field back
to AN (when no data is present) then append data from the table
with the long into field to the empty table with the AN field.
This does the trick. Obviously, referential integrity issues must
be dealt with too if present.

It's pretty simple and doesn't require changing data types. You
change your destination table to have an OldAutonumberID field (a
long integer field with a unique index allowing Nulls), and append
the old AutoNumber from the source table into the OldAutonumberID
field of the destination table.

To append data from child tables and maintain the relationship, you
just do this:

INSERT INTO tblChildDestination
(ForeignKeyID, DataField1, DataField2)
SELECT tblParentDestination.PrimaryKeyID,
tblChildSource.DataField1, tblChildSource.DataField2
FROM tblParentDestination INNER JOIN tblChildSource
ON tblChildSource.ForeignKeyID =
tblParentDestination.OldAutonumberID

You use the OldAutonumberID field in your destination parent table
as your concordance/lookup to the foreign keys of the old data
schema in order to retain RI.

If the old schema was isomorphic to the new one and maintained
valid RI, there should be no problems with this kind of append.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

mi******************@btinternet.com (Mike MacSween) wrote in
<40**********************@news.aaisp.net.uk>:

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:uu********************************@4ax.com.. .

>I assume there's a standard technique for this. I imagined that
>you import/append or whatever the data, including the child
>tables. Reset the relationship to the new long int field,
>create a new AN field, then, with cascading updates ON, set the
>value of the PK (long int) field to that of the AN field,
>change the PK to the AN field, and the relationship, and delete
>the old PK long int field.
>
>Is that the way to go about it?

No, because you can't write values directly to an AN field in
the current table.

Yes, I know. I wasn't proposing writing values into an AN field.

But you *can* do that if the values you are writing do not collide
with existing values.

Obviously, referential integrity issues must be dealt with too
if present.

Yes. That was my point.

This is not really very difficult. I've done it many dozens of
times, with practically every data import I've done when writing
Access apps to replace older applications.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Peter Miller]

On Fri, 16 Jan 2004 01:14:43 GMT, dX********@bway.net.invalid (David
W. Fenton) wrote in comp.databases.ms-access:

An Autonumber that is used in relationships (i.e., is a PK) has
these characteristics:

1. a long integer field.

2. unique, non-Null index.

3. default value of Max([last value used])+1 or Rand().

That's it.

Because of that, values that match the data type and do not violate
the index can be appended.
Of course they can be appended. I made that same point. What I said
was that you can append records with existing values, but you can not
write AN values directly. In other words you can not set the field's
value explicitly (with, for example, a sql update statement, or via
ado or dao using 'field = 3' type assignments).
So, if you're appending data from an existing schema into a new one
and want to retain the Autonumber values, you just append them to
the Autonumber field. If they are valid PK values in the source
table, they'll be valid in the destination table.
Of course this is true. Append queries were mentioned numerous times
higher up in this thread. This is not news.
You don't have to muck around with changing data types at all.

Well, again, reading the thread in context is helpful. 'messing with
datatypes' is indeed required if the user wishes to copy/paste records
rather than using an append query. That was the context within which
data types issues were raised. As I pointed out, copy/paste is
inferior to append queries.

. . . Remember that as soon as you create your new AN
field, AN values are assigned to existing records. . . .

This is only true if you're appending to a table that already has
values in it that collide with the values of the source data.

Huh? Its true regardless. If you have a table that has existing
data, and you add an autonumber field, there is no way to stop jet
from populating that autonumber field. This has nothing to do with
other tables, or external source data...

. . . So the way to
do it is to create a second table, change the long int field back
to AN (when no data is present) then append data from the table
with the long into field to the empty table with the AN field.
This does the trick. Obviously, referential integrity issues must
be dealt with too if present.

It's pretty simple and doesn't require changing data types. You
change your destination table to have an OldAutonumberID field (a
long integer field with a unique index allowing Nulls), and append
the old AutoNumber from the source table into the OldAutonumberID
field of the destination table.

To append data from child tables and maintain the relationship, you
just do this:

INSERT INTO tblChildDestination
(ForeignKeyID, DataField1, DataField2)
SELECT tblParentDestination.PrimaryKeyID,
tblChildSource.DataField1, tblChildSource.DataField2
FROM tblParentDestination INNER JOIN tblChildSource
ON tblChildSource.ForeignKeyID =
tblParentDestination.OldAutonumberID

You use the OldAutonumberID field in your destination parent table
as your concordance/lookup to the foreign keys of the old data
schema in order to retain RI.

If the old schema was isomorphic to the new one and maintained
valid RI, there should be no problems with this kind of append.

Of course.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051