Importing a table from Access 97 to 2000

[Andrew]

Hi,

There has been a recent upgrade from Access 97 to 2000 and
as a result I have had to convert a database set up in
Access 97 to Access 2000. As the database contained quite
a few objects and a fair amount of VBA code I could not
convert it using the "convert" option in 2000. I got
around the problem by creating a blank database in Access
2000 and importing all the tables, forms, queries etc and
editing any incompatable VBA code where necessary.

My problem is that I cannot successfully import the main
table from the original although if I create a dynamic
link to the "old" table everything works fine.

When I try to import it I get an error message saying
another user is trying to access the data. I know for a
fact that this is not the case and I cannot think why it
doesn't work as all the other tables have imported. I have
removed any relationships from the table but other tables
with relationships imported OK anyway.

I also tried to copy the data into a copy of the table
using copy and paste but there is a problem as the table
has an autonumber primary key. When the data is copied the
new table assigns a new number to the record's primary key
field. If I change the field to "number" I can paste in
the data and keep the original "ID" values for the records
but i cannot then turn the auto number back on the begin
numbering from the last id number e.g. 1500.

If anybody can suggest a way to deal with this problem I
would be very grateful.

Thanks in advance.

Andrew

 

[Wayne Morgan]

Do you still have Access 97 handy? If so, can you import this table into a new, blank
Access 97 file? Can you then import this into 2000?

 

[Geoff]

Andrew

You're almost there!

As you've already done, paste the data into a new
table with the ID field defined as just a number field
(not an autonumber primary key field).

Create a new table with no data in it with the ID
field defined as an autonumber primary key
field.

Create an append query to copy the data from one
table to the other.

This will preserve the IDs.

Regards
Geoff

 

[Russell]

Careful pasting in a new table and then apending to
another table with an autonmumber field. This will NOT
preserve your auto number field if any records have been
previously deleted from your main table, creating a "hole".

If no records have been deleted, then this will work fine,
just make sure the number field is indexed or ordered in
the append query to append the records in the same order.

 

[Geoff]

Russell,

That's very odd! I tested exactly the scenario
you describe (with A2K) before my post and
it worked fine WITH a missing number!

Have you tested it?

Regards.
Geoff