Results 1 to 8 of 8
  1. #1
    okevino is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3

    Import records from another table (override if exists)

    Hello,



    I would like to update one access table from another access table.

    There are no primary keys nor other calculated fields.
    The column definitions are all the same in both columns, only the values are different in some cases.

    Sadly I can't delete the old database and use the new one, because multiple users are using it at the same time. (It is locked).

    My first try did look like something like this:
    UPDATE STDLIB_EANCU000DH
    SET
    EANLAN = EANDU000.EANLAN,
    EANBBN = EANDU000.EANBBN,
    ... (70+ other columns)

    Access wants that I enter some Parameter values but I thought it gets the data from the other one.

    (One table is named: EANDU000 the other is STDLIB_EANCU000DH.)

    Then on my second try I did choose the "append" Option. This didn't work because the database is bigger than 2 gb.

    Are there other Options to merge two databases with the same fields?

    I'm using Access 2013.

    Kind regards
    Kevin

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is this a one time process or will you be merging records from these 2 tables continuously? You said you want to overwrite if exists yet there are no primary key fields. What values in EANDU000 would match values in STDLIB_EANCU000DH? Or do you just want to merge all the records in both tables into 1 table? What are you going to do with this merged data? If just displaying you could do a Union Query and get rid of duplicates if you are just trying to display data.

  3. #3
    okevino is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Hello, this process will be continiously, arround every 4 weeks. We have 4 columns which could make a unique key in both tables. (EAN -Barcode is splitted in 4 columns). The values that are matching are arround 95%.

    I have 1 old table and 1 new table. I want to merge the data from the new table into the old table. The data will be displayed in a label Software, which is connected to the database.

    And thanks for your fast answer.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This should be a simple Update query. The reason that you are being asked for a parameter is that there is an unknown field listed somewhere in the SQL. Did you use query design to create the query?

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There would also be new records to add - use the query wizard to create an unmatched query and then change it into an Append query. Will there be records to be deleted? Also an unmatched query but from the opposite side.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    pseudo code/logic
    Code:
    For each input record
       if record already exists in table then
         use UPDATE sql
      else
         use  INSERT/APPEND SQL
     end if
    Next  Input record

  7. #7
    okevino is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    I tried to use an update query however it exceeds the Maximum allowed file size of 2 Gigabyte. Is this possible?:
    Create an Select Statement which selects everything from a linked table and save that select Statement, so I can press Refresh and the new records are available?

    My first goal would be just to replace the Mdb file (which contains only 1 table (900MB)) however the file is locked because some users are using it at the moment.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That would make sense, to replace the table and not worry about updating. You could have an automated macro which runs at a time when no one is using the database which replaces the table and compacts the database. Is it possible to link to that table instead of making a copy of it in this database? That would be a better option.

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

Similar Threads

  1. Replies: 11
    Last Post: 04-27-2017, 05:16 PM
  2. Replies: 1
    Last Post: 03-31-2016, 03:33 AM
  3. Replies: 26
    Last Post: 07-28-2015, 07:52 PM
  4. Replies: 5
    Last Post: 02-21-2014, 07:55 PM
  5. Excel Import - Check if Spreadsheet Exists
    By kagoodwin13 in forum Programming
    Replies: 3
    Last Post: 11-21-2013, 02:38 PM

Tags for this Thread

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