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

    Question Importing Excel files to replace existing files but retain table relationships

    I have table relationships between two different Excel import files, which are now tables within the Access database through a Form code I created that can browse and import Excel files automatically into Access Tables.

    What I am wanting to do however, is be able to save a new version of each of these two Excel import files that overwrite the previous two into my database once a week that will automatically contain the table relationships - is this possible?

    Or, is there a way to VBA code and save a relationship instruction, so that if I delete the two files once they are a week old for example, then import the two newer versions with exactly the same file names, that code could then perform a new relationship which is identical to the the last relationship between those two tables?I'm just aware that when I delete one or both of the tables, the relationships obviously break, then when I import the newer files, obviously at the moment I'm required to create new table relationships.Hope the above makes sense, and any help would be appreciated massively.



    Kind regards
    Samuel

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Don't delete the Access data tables, just delete the data in them.
    Then import the new data into those empty tables.
    Since you are not deleting the tables, the relationships will still exist.

  3. #3
    Cliffy306 is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Thank you JoeM

    That worked nicely - I just deleted the data in the tables, opened the new Excel Files, copied all the relevant cells & pasted it back into the Access Tables as appropriate.

    I hadn't thought of that, I was trying to go a bit complex and try some automated code somehow.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

    A few tips:
    - You can create a Macro to quickly delete all the data in both tables (run two Delete Queries)
    - When deleting data frequently, be sure to occasionally Compact & Repair the database to purge the deleted records and keep the database from bloating in size.

  5. #5
    Cliffy306 is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Thanks so much, I will try that out.

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

Similar Threads

  1. Importing Excel files using Access
    By nick243 in forum Import/Export Data
    Replies: 1
    Last Post: 07-25-2016, 12:11 PM
  2. Replies: 20
    Last Post: 02-02-2015, 03:02 AM
  3. Importing Excel Files
    By sathishkm in forum Import/Export Data
    Replies: 2
    Last Post: 10-02-2013, 04:16 PM
  4. Importing from several Excel files to Access.
    By cmdteardrops in forum Access
    Replies: 2
    Last Post: 10-02-2013, 03:33 PM
  5. Replies: 10
    Last Post: 12-28-2012, 02:06 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