If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
import data from excel 2003 to a table in an existing database
i made a copy of Products table as ProductsChange table in an existing
database. no relationship created yet. i will use ProductsChange table to update records to Products table regularly. i exported Products table and saved as an excel spreadsheet called ProductschangeTemplate. i created an Macro to import data from ProductsChangeTemplate to ProductsChange table. tried several times and i always get the "key violation error". if i change primary key field of ProductsChange table to No Index(primary key got to taken off first), it works but left duplicated records in ProductsChange table every time i import the same productID. to void duplication, i delete records in the ProductsChange table before importing. there must be some settings i did not do properly. please help how i can avoid deleting records first but go to my Macro to import directly and don't have to worry duplicated records. |
#2
|
|||
|
|||
import data from excel 2003 to a table in an existing database
"Johnli" wrote in message ... i made a copy of Products table as ProductsChange table in an existing database. no relationship created yet. i will use ProductsChange table to update records to Products table regularly. i exported Products table and saved as an excel spreadsheet called ProductschangeTemplate. i created an Macro to import data from ProductsChangeTemplate to ProductsChange table. tried several times and i always get the "key violation error". if i change primary key field of ProductsChange table to No Index(primary key got to taken off first), it works but left duplicated records in ProductsChange table every time i import the same productID. to void duplication, i delete records in the ProductsChange table before importing. there must be some settings i did not do properly. please help how i can avoid deleting records first but go to my Macro to import directly and don't have to worry duplicated records. |
#3
|
|||
|
|||
import data from excel 2003 to a table in an existing database
The best approach is to use an interim table to receive the contents of your
spreadsheet's data. Then the append query to copy the data to the permanent table should include a left join to the permanent table on the primary key field and only select records from the interim table where the permanent table's primary key field value is NULL. For example: INSERT INTO PermanentTable SELECT * FROM InterimTable LEFT JOIN PermanentTable ON InterimTable.PrimaryKey = PermanentTable.PrimaryKey WHERE PermanentTable.PrimaryKey IS NULL; -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Johnli" wrote in message ... i made a copy of Products table as ProductsChange table in an existing database. no relationship created yet. i will use ProductsChange table to update records to Products table regularly. i exported Products table and saved as an excel spreadsheet called ProductschangeTemplate. i created an Macro to import data from ProductsChangeTemplate to ProductsChange table. tried several times and i always get the "key violation error". if i change primary key field of ProductsChange table to No Index(primary key got to taken off first), it works but left duplicated records in ProductsChange table every time i import the same productID. to void duplication, i delete records in the ProductsChange table before importing. there must be some settings i did not do properly. please help how i can avoid deleting records first but go to my Macro to import directly and don't have to worry duplicated records. |
Thread Tools | |
Display Modes | |
|
|