Uptate Column in Access from Excel

[mobilpc]

Hi

i need to update/sync existing fields (dont create new row/entry in database) maybe of 2-3 columns (eg. add/update EAN, WEIGHT, COLOUR) in access database from excel sheet using same ID.

The final file structure of the database must remain intact, cause will be imported back to ERP software.

Thanx

 

[Joe4]

So, are you saying that you want to update existing records in Access from data in Excel?

If the Excel file contains all the data that you need for that table, you can just link that Excel file to an Access table, and use that as your table in Access (for whatever you are using this for)?

If you are trying to update a "master" Access table, I would recommend linking this Excel file into another Access table, and then create a query between the two tables, and then change it to an Update Query so that you can update the Access records from the Excel ones.

 

[mobilpc]

yes, but i am newbie to this

i can export both access and excel sheet with only rows i need to update from excel to access. (i just need to rename rows in excel to match access rows)

so i need to create another database file and create some query in this file to update (not create any new entry) my original exported file from ERP?

 

[Joe4]

You do not need to export anything. You can do this all inside of Access, by linking the Excel file into Access.

I think it would be most helpful to see samples of your Excel file and Access table. And then let us know which key fields we are joining on, and which fields we are trying to update.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mobilpc]

i mean that i can export only rows i wants from both systems where those non synced data are

Excel

EANY_TEST.xlsx
	A	B
1	IDS	EAN
2	IPH7256BL_EU
3	IPH7256GL	190198070784
4	IPH7128GL_EU
5	IPH7256BL_SKV
6	IPH7128R
7	IPH7256SL
8	IPH7128GL_SKV
9	IPH764BL_SKV
10	IPH7128BL_SKV
11	IPH7128RG_SKV
12	IPH732GL
13	IPH732BLOB_SKV
14	IPH7128B_V	190198068262
15	IPH7128BL_SK
16	IPH732BL_SKV
17	IPH732JB
18	IPH732BL_V
19	IPH7128RG
20	IPH7128SL_SK
21	IPH7128RG_SK
22	IPH7128BLC_SK
23	IPH732RG	190198067906
24	IPH732BL
25	IPH732BL_EU	190198066824
26	IPH732GL_EU	190198067548
Hárok1

Access

EANY_TEST.xlsx
	A	B	C
1	ID	IDS	EAN
2	17541	IPH7128B_V
3	16273	IPH7128BL_SK
4	15655	IPH7128BL_SKV
5	16274	IPH7128BLC_SK
6	15811	IPH7128GL_EU
7	15674	IPH7128GL_SKV
8	17153	IPH7128R
9	16552	IPH7128RG_SK
10	16152	IPH7128RG_SKV
11	16484	IPH7128SL_SK
12	16229	IPH7256BL_EU
13	15762	IPH7256BL_SKV
14	16454	IPH7256GL
15	16553	IPH7256SL
16	16910	IPH732BL
17	16387	IPH732BL_EU
18	15761	IPH732BL_SKV
19	17103	IPH732BL_V
20	16034	IPH732BLOB_SKV
21	16391	IPH732GL
22	16736	IPH732GL_EU
23	17322	IPH732JB
24	16480	IPH732RG
25	17505	IPH732RG_V
26	16392	IPH732SL
27	15669	IPH732SL_SKV
28	15668	IPH764BL_SKV
29	17523	IPH7P128B
30	17524	IPH7P32B
31	18325	IPH7P32S
32	16455	IPH7PL128GL
33	16289	IPH7PL128JB_SK
34	16114	IPH7PL128JB_SKV
35	16217	IPH7PL128SL_EU
36	16453	IPH7PL256JB_SK
37	16216	IPH7PL256JB_SKV
38	17152	IPH7PL256R
39	16554	IPH7PL256SL
40	16082	IPH7PL32BL_SKV
41	16456	IPH7PL32GL
42	16022	IPH7PL32SL_SKV
Hárok2

 

[Joe4]

Import/link your Excel table into Access.
Then create an Update Query between this changes table and your main Access table.
Here are instructions on how to create an Update Query: Create and run an update query

Post back if you run into issues.

 

[mobilpc]

i try to make query, but doesnt work the ID are different only IDS are are the same

 

[Joe4]

Are the "IDS" values in each table unique (meaning, they do not appear more than once in each table)?
If so, you should only join the tables on this one field, and not the other one.

Basically, you only want to join on this one field, and look for values where the "EAN" fields differ.
So that "EAN" part would go in the Criteria of the query, not the join.

 

[mobilpc]

yeap, done that way, join only IDS and it works thank you

so, i just export datatabase from ERP => make copy of that where i import table from excel => make and run query => delete query and excel table from database => import back to ERP?

is this the right steps?

 

[Joe4]

There may be a few steps that we can eliminate, but I need to have a better understanding of your structure.

Is this ERP database that you ultimately want to update an Access database?
Are you able to work directly in this ERP database, or do you always have to export data out of it?