Help! Having problems importing data

[Amit]

Hi,

I've to import data from a table in Access 97 to one in
Access 2000. The field names are not the same.

I tried converting the Access 97 database to 2K, but that
didn't work (it lost data). So, I ran a query to get data
from the table in Access 97, and copied and pasted the
data to an Excel worksheet, and then tried to import data
from Excel worksheet to the table in Access 2K.

Here are the steps I followed:
1. Imported the data from Excel to a new table (T1) in
Access 2K.
2. Changed the names of the fields and the field types of
T1 to match those in the table (T2) where data is going.
When I did this, I got the warning message that some data
may be lost, and I went ahead. [some fields in T2 are
smaller than the ones in T1. eg. the default for phone
number in T1 is 255 chars text, but in T2, it is 15 chars.
So, I'm sure I didn't lose any data when I changed the
field size in T1 to 15.]
3. Removed all input masks from T2. T2 does not have any
required fields except for the Autonumber ID field.
4. Ran an update query to get the data from T1 and update
it to T2.
5. Got a "validation rules violation" error for 750+
records out of 777.
table using the Excel worksheet, and then ran an update
query to transfer data to the table. Before running the
query, I made sure that the field names and the field
types are the same, and also removed any input masks from
the table to which data will be exported.

The format of the some fields in T1 is "text", and for the
rest, is "Yes/No".

Out of 777 records, it imported only 20 or so, and gave me
a validation rule violations error for the rest of them.
I'm not sure why this is happening, and if someone can
help me figure this out (if there is an easier way to do
this), I'll appreciate it.

Thanks!

-Amit

 

[Amit]

Hi John,

Thanks for your excellent suggestion, which I will use
next time, as it will save me some headache and is better
than what I did.

I did solve the problem though. There were some empty
fields in the Excel spreadsheet, and the table property
was set to not allow zero-length. Once I changed that to
yes, it worked like a charm.

Thanks again.

-Amit

-----Original Message-----
Hi Amit,

I'd suggest that you export from '97 and import to 2k via
a CSV file. This is a text file with fields separated by
commas.

1. File -> export
Enter a file name AND Save as type text file. Press
Save and
the export wizard will ask you for field delimiter and
text field delimiter - I'd suggest comma and " - the
default setting.

2. Import into 2k by creating a macro with action Transfer
text (means the file is a text file not just text data).
Give it the file name (full path). Table name (best this
doesn't exist already so access can decide its own
datatypes based on the data in the file). Tranfertype is
import delimited and has field names is no - leave the
rest blank.

3. Save and run the macro and all should be there.

The beauty of this is that if there are problems you can
read the data in the intermediate text file and know
exactly what is going on.

Good luck

John P.

-----Original Message-----
Hi,

I've to import data from a table in Access 97 to one in
Access 2000. The field names are not the same.

I tried converting the Access 97 database to 2K, but that
didn't work (it lost data). So, I ran a query to get data
from the table in Access 97, and copied and pasted the
data to an Excel worksheet, and then tried to import data
from Excel worksheet to the table in Access 2K.

Here are the steps I followed:
1. Imported the data from Excel to a new table (T1) in
Access 2K.
2. Changed the names of the fields and the field types of
T1 to match those in the table (T2) where data is going.
When I did this, I got the warning message that some data
may be lost, and I went ahead. [some fields in T2 are
smaller than the ones in T1. eg. the default for phone
number in T1 is 255 chars text, but in T2, it is 15 chars.
So, I'm sure I didn't lose any data when I changed the
field size in T1 to 15.]
3. Removed all input masks from T2. T2 does not have any
required fields except for the Autonumber ID field.
4. Ran an update query to get the data from T1 and update
it to T2.
5. Got a "validation rules violation" error for 750+
records out of 777.
table using the Excel worksheet, and then ran an update
query to transfer data to the table. Before running the
query, I made sure that the field names and the field
types are the same, and also removed any input masks from
the table to which data will be exported.

The format of the some fields in T1 is "text", and for the
rest, is "Yes/No".

Out of 777 records, it imported only 20 or so, and gave me
a validation rule violations error for the rest of them.
I'm not sure why this is happening, and if someone can
help me figure this out (if there is an easier way to do
this), I'll appreciate it.

Thanks!

-Amit
.

.