Dears,
I've made a database where my masterdata comes from a program (WMS) with an excel output.
Given many company software is nothing more than just a "simple" database I'm missing my "planning tool" in order to perform my work well.
I'm moderate level with excel I.E. I record macro's, can read and rewrite(from examples) them but lack the knowledge to write them from scratch. And created simple things in Access in the past (database and forms).
Hence I made a perfect working tool in Excel.
However... given my co-workers are far from expierenced with excel, I rather not have them touch my formulas in sheets and mess everything up (yet again...).
So I would like to make a user-"friendly" tool where I can actually work instead fix things every week to keep the tool working.
What I would like to achieve is that I can import my WMS data into my access database, where it checks the unique ID's and updates the data accordingly.
All the while where it maintains the added data in fields which are not coming from the excel extract. In Excel I can achieve this easily with a macro and some Vlook ups.
What I've tried so far;
My Database contains the extract from my WMS. Where my unique ID's are my ordernumbers from the extract.
I've got field 1 to 6 from the extract (contains several data like client, customer ref. etc) and want to use field 7 to 9 with data which I will enter in Access.
* Now, when I import an update from my WMS, Access either drops the new data below my current data in the database. It ignores the ID's and does not update the database with the import data.
Or it overwrites the entire data in the database which messes up field 7 to 9.
* When I create a linked database to an Excel sheet(WMS extract), it won't let me update the Excel as long as Access is open by one user (should be 4 users working in it simoustanly).
Plus I have the same issue where field 7 to 9 get's ignored, messing up the data structure.
I hope someone can help me and it's a lack of my own knowledge from access rather than that it is not possible and need to find an alternative way to achieve my future planning tool.
Apologies for the background story, but rather supply the reason why I choose for Access instead of sticking to Excel and to show the necessity of the tool in Access (plus I love learning new things haha...).