Results 1 to 3 of 3
  1. #1
    Twisted84 is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    1

    Update database with excel import while maintaining added data in access

    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...).

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How do you import? - provide your code. If you want to provide file for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    as I understand it, you have fields 1-6 coming in the Import from WMS in an Excel format, and you have manually input fields 7-9 coming from an Excel spreadsheet.

    so what you want to do is to marry these together and you have essentially two choices.

    1. join them up in Excel first, then do the Import.

    2. Import the WMS Excel, then Import the other fields into a separate table, and then join them up.

    which of these you choose depends to some extent on how you're going to tell either ACCESS or Excel which records containing fields 7-9 match up to the records containing fields 1-6. there needs to be a common field, preferably a unique identifier. without this unique identifier you are probably just spitting into the wind.


    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.
    this needs some clarifying.

    are you adding records to the database or overwriting existing records?

    and the expression, "it checks the unique ID's and updates the data accordingly" is not clear. what is "it" that's doing this checking, and what does "updates the data accordingly" mean? is it overwriting existing records, adding to the data collection, updating something else from the new data. we cannot help you unless you can explain exactly what it is you are trying to do.


    good luck with your project.


    Cottonshirt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-04-2021, 09:14 AM
  2. Import excel file / data into access database.
    By johnseito in forum Access
    Replies: 3
    Last Post: 10-18-2018, 06:34 AM
  3. Import data from excel to Access Database
    By n2d2cool in forum Access
    Replies: 1
    Last Post: 12-13-2016, 05:02 AM
  4. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  5. Replies: 7
    Last Post: 04-16-2014, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums