Results 1 to 5 of 5
  1. #1
    BobV is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    2

    Prevent table from updating in query


    Hopefully a simple question here. I have a db set up that I periodically import updated data into via text file, replacing the old table with a new one. Before I import the replacement table and data, I rename the current table, appending the table name with "-old", in case something goes wrong. When I import the new data into a new table in the db, and then go to my several queries I have set up, it has automatically changed the applicable table to the -old table I renamed. Is there any way to prevent the queries from changing the table name so that they always show the same name and match the new table I'm creating with the imported data? Otherwise, I have to go into each table, each time I update my data, and add the table and then change the table name being referenced. Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont rename tables. use the same table, empty then import.
    Keep the import source files.
    we import the file to an 'import tbl', then perform validation, THEN append that data to the main table.

    renaming tables will clutter up the db, and then fill up. All the source files are still outside the db.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I don't know a direct answer to your question but this might be a workaround:
    you could move the old data to a new table then import the new data to the current table

    1) SELECT tbl_current.* INTO tbl_old FROM tbl_current;
    2) Verify tbl_old
    3) DELETE * FROM tbl_current;
    4) Import new data to tbl_current
    5) Verify
    6) DROP TABLE tbl_old;

    This way since the table wasn't renamed access wont rename the references to it in the queries.

    [EDIT]
    Yeah, what Ranman said, not what I said. Update the existing data as needed rather than dumping it all and rebuilding the data.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree that ranman's approach is tried and tested by many. This is often referred to as a landing table or temp table and is where any reviewing/vetting is done. Then, specific queries can be used to move relevant parts of the landing table data to its proper destination. Again emptying tables and importing is a standard practice. Save the import files.
    Another wrinkle on this is - depending on volumes - is to add a date field to the data going into the Landing table. This allows your Landing table to have multiple import instances data and each import instance record has the same date. Import instances can be reprocessed etc if/as needed.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    All good suggestions! If you still want to keep your existing process you need to turn off Name Autocorrect for your database (File/Options/Current Database and uncheck the Name Autocorrect options).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Updating Tables to prevent the Record Can't Update error
    By grasshopper212 in forum Database Design
    Replies: 3
    Last Post: 08-26-2019, 01:56 PM
  2. Replies: 2
    Last Post: 04-25-2018, 06:08 AM
  3. Replies: 3
    Last Post: 09-11-2015, 03:22 PM
  4. Replies: 4
    Last Post: 04-24-2013, 02:11 PM
  5. Replies: 8
    Last Post: 02-09-2012, 02:02 PM

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