Import Data into MS Access table
Hi folks, I have a table in MS Access that look like this.
Tablename = User
Columns: UserID, UserName, Category, Admin, Custodian
and I have a similar data in XL. I would like to import the XL data into this table. Some of the data is duplicated and I would like to retain the data in Access.
Its easy enough to import the data into a new table in Access, but the option to import into an existing table is disabled for some reason.
Do I need to import into another table and then use an SQL query? If so, can you please provide it?
Tablename = User
Columns: UserID, UserName, Category, Admin, Custodian
and I have a similar data in XL. I would like to import the XL data into this table. Some of the data is duplicated and I would like to retain the data in Access.
Its easy enough to import the data into a new table in Access, but the option to import into an existing table is disabled for some reason.
Do I need to import into another table and then use an SQL query? If so, can you please provide it?
It is almost always best to import external data into a 'holding' table, in Access, then you can check to be sure that the imported data is correct, before then importing the data from this 'holding' table, into your actual 'live' tables.
If you import directly into the live tables, and something goes wrong, it would be EXTREMELY difficult to cleanup the resulting mess.
AW
If you import directly into the live tables, and something goes wrong, it would be EXTREMELY difficult to cleanup the resulting mess.
AW
Open database(don 't select any tables just go to)->file->Get external data->Import->file of types XLS->Select your file
add to my suggestion
Open database(don 't select any tables just go to)->file->Get external data->Import->file of types XLS->Select your file
When you create new table by importing table,just make simple append query
INSERT INTO tbl1
SELECT tbl2.*
FROM tbl2
Open database(don 't select any tables just go to)->file->Get external data->Import->file of types XLS->Select your file
When you create new table by importing table,just make simple append query
INSERT INTO tbl1
SELECT tbl2.*
FROM tbl2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and of course,
instead of importing the XLS file, you can just link to it:
Tables->New->Link Table->Select your file
instead of importing the XLS file, you can just link to it:
Tables->New->Link Table->Select your file
Have you looked into DTS? It should be eady enought to map fields and scrub data (if necessary).
Another Quick Solution to the problem is to import into a new table. just copy and paste the data from the new table in the open view. this is a work around and just a quick solution.
tables' columns and column types are same?