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

 

[mobilpc]

The ERP uses Microsoft SQL, with more than one table for sure and can export and import tables by one to Access.
I have access to SQL Server Management Studio too, but only work i do there was removing coinminer sheduled jobs and procedures created when external admin open port and attack arrived in few days

I need to sync some data from ecommerce (csv output only) to ERP because data are mixed up (some are there and others there), i want to set ERP database as source for most of data in near future.

I am not sure if i am able to to do things in sql directly, but maybe when i create some test enviroment with copy of that database...

I also have some ideas. Eg. automaticaly fill few atributes (eg colour- Black) if you find this "Black" somewhere in this row then set attribute Black, there must be few conditions too, not filled, in case of Blackberry ignore or maybe accept when there is Black 2 times?
I want to do this as some sql automatic jobs, but this is future

 

[Joe4]

OK, so exporting from both the ERP and Excel file is fine. But actually, you may need need to do either.

You can connect your Access database to your SQL server, and link the table in to Access. Then you do not need to worry about exporting/importing it every time.

Likewise, if you are working with the same Excel file every time, you can link to that too, so you do not need to export/import.
If you get a new Excel file each time, you have a few options:
- Link the Excel table into Access. Then just "overwrite" your Excel file (so you are saving it with the same name in the same location each time). Then you should not have to mess with exporting/importing or changing the links (though you may need to "refresh" the connection).
You could also use a native Access table, and just delete the old data, and import the new data into it.

As long as you are using the same tables in your Access database each time (no need to re-create them each timel if not linked, simply delete the old data before importing the new), there is no need to delete the Query. Queries are dynamic, and run against whatever data is in the tables at that time. So once you create the queries, you should not have to ever delete or re-create them.

Since you are not actually wanting to update the data in the Access database, but really in the ERP database, you do not need to have it be an Update Query in Access. Make it a simple Select query (just change the query type from "Update" to "Select"). Then you just export the data you need from that query, and that is what you will import into your ERP database.