Problem Importing Tables

[AndyCabbages]

I am using Microsoft Access 2000 and when I try and import a table from an older version of the database I get the fllowing error:

'the decimal field's precision is too small to accept the numeric you attempted to add'

I have been working on a database for a friend over the last few weeks and have made changes to forms and quieries, but the tables and the data in them has remainded the same. During the time I have been working on the database my friend has been using it as normal and inserting data in to his original version while I work on the updated version.

Because of the amount of data he has added during the time I've been working on the database it is not practical to go through and type it all in again so we tried the following:

1. Delete all the tables in the updated version
2. Import all the tables from his original version he has been using
3. Result should be an updated database with no data loss?

All of the tables import fine except for one, which causes the above error and I have no idea why.

Also, is this the best method for handing over the updated database? Or is there an easier way it can be done with no loss of data he has input over the last few weeks?

 

[Galaxiom]

You would be better to keep your tables, delete the records and append the records from the original tables. This way you maintain total control of the datatypes in the later format.

The properties of the Decimal datatype are the most complex of any. I image Access is taking a guess at the data type properties to use and gettting it wrong.

 

[AndyCabbages]

How can I get all the records from the old table in to the new table without a whole load of manual entering?

 

[Bob Ross]

right click the table, export to excell. then import the excell file as a new table.

 

[AndyCabbages]

Thanks, this seems to work fine

There are about 12 tables in the database, 11 of them import fine from the original Access file, its just this one that was causing problems.

Would you reccomend I go through this process with just the one problematic table and go through the import from Access process with the other 11? Or go through this process with all 12 tables?

 

[Bob Ross]

it's up to you. shouldn't realy matter if there are no problems occuring, but if you want to be on the safe side then do all 12

 

[AndyCabbages]

Alright, I will probably just use the Excel method for the one table. I was just concerned there might be some sort of consistency issue with doing it half one way and half the other way.

Thanks for the help

 

[Galaxiom]

There is no need to go through Excel. Indeed there are good reasons not to do so.

Use an append query in Access.
Open the old database. Create a query with all the fields of the table included.
Convert the query to an Append query and follow the prompts selecting "a different database" and enter the path to the database and the table to append to.

The fields to be populated should be automatically selected with the matching names.