Results 1 to 7 of 7

Import Excel to Access tables with foreign keys - use import or tabular forms?

  1. #1
    bcz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    6

    Import Excel to Access tables with foreign keys - use import or tabular forms?

    I am an Access newbie and I have a general question.
    I have a large Excel file with multiple overlapping sheets that is too hard to maintain, so I want to convert to Access. I have designed my Access tables and populated a little data manually. But now I want to dump all that Excel data into Access. I'm not clear about the best way to go about doing that. The thing I'm stumbling over is the foreign keys. My tables have autonumber primary keys, and most of my tables have foreign keys, but of course my Excel data doesn't have such database keys.
    So here is my basic question. Is there someway to import from Excel where Access figures out where to put everything or figures out how to replace the Excel data with foreign keys where needed? Or am I better off creating a tabular form that I can copy paste the Excel data into?


    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,246
    You need some value in Excel sheets that can serve as temporary key to maintain relationship. Once records are imported to correct tables, run UPDATE query to populate foreign key field with the newly generated autonumber primary key, using the temporary key to join tables. Then when done, can delete the temporary foreign key field.

    Review https://support.office.com/en-us/art...F-4106F78850B4

    There are 3rd-party tools you can buy to do conversion.
    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
    bcz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    6
    Thanks for the reply, June7. However, at this point I don't understand it. It seems as if I'll have to go thru and correct row by row, which is what I'm trying to avoid. But I need to read the link you gave me, so maybe that will clear things up.

    To simplify, I have two tables, Books and Authors. Let's say I import a bunch of rows into Authors. Now in Excel, I have a table with Book Name and Author Name. I can replace the Author Name with a fake foreign key, but I don't see how, once in Access, to replace the fake foreign key with the proper Author table primary key, without going thru row by row and doing it by hand. That is why I thought a form that makes the Author Name-to-Author table primary key connection for me might be a solution.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,246
    Build an UPDATE query that joins tables on Author name.

    UPDATE Books INNER JOIN Authors ON Books.AuthorName = Authors.AuthorName SET Books.AuthorFK = Authors.ID

    This assumes author name is a single field and spelling is consistent in both tables.

    Name parts should be in separate fields.

    Advise not to use spaces nor punctuation/special characters in naming convention.
    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.

  5. #5
    bcz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    6
    Thanks again, June7.
    I think I see now how it would work. I didn't get before that I need to build a temporary key column in Excel before I start. I'll think it through. My concerns at this point:
    -- I already have a Authors table. I gather I will be loading in Authors data with this extra field. I take it I will need to load into a temporary Authors table, then after removing the temporary key column, paste the data from that temp table into the real Authors table.
    -- I can't just use AuthorLastName. Names are sometimes repeated. I'll need to use multiple fields (AuthorFirstName).
    -- My Books - Authors relationship is a many-to-many one, so I'll have to figure out how to deal with the association table.

    Are you sure I can't design a form that would distribute the Excel data into the proper Access tables, filling in foreign key values properly where needed? That seems from my na´ve point of view to be a more straightforward solution.

    Thanks again for your input.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,246
    Establishing another field as 'temp key' in Excel would mean manually adding a number that is unique to each author name. For example, all John Smith rows would have to be given the same number. This will not help with linking to existing Authors table unless you use the key values generated by Authors table. This defeats the goal of not having to do manual updating.

    Instead, author name parts serve as temporary key - assuming name parts are in separate columns in Excel as well as separate fields in Authors table and consistently spelled - this would be a compound key. Authors table autonumber field would be designated as primary key. Now import Excel records, create a foreign key field, build query linking Authors table on author name parts, update foreign key field with the primary key, delete author name fields.

    Are there any fields in the Excel that need to update data in Authors table? Are there authors not already in your Authors table? If this is a concern, then import the Excel, delete existing author records (run Compact & Repair afterward if you want autonumber to start with 1), build a query that pulls DISTINCT record for each author, replace Authors with this complete set from Excel. Now build the query to update foreign key for AuthorBooks data, still linking on name parts.

    Could write VBA code to execute these queries, but would probably take longer to do than just manually building and running queries. This is only a one-time exercise?
    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.

  7. #7
    bcz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    6
    Ok. Thanks.

    Things are MUCH more complicated than just Books and Authors. In order to capture what I have in my Excel data I have designed tables for Authors, Books, BookSeries, Awards, BookCopies, and 3 levels of Genre. Many of these tables have many-to-many relationships. The Excel data exists in 4 sheets with a lot of overlapping info, and many Excel columns containing non-atomic data (e.g. multiple books listed for an author). I expect that in general I am going to have to pull from multiple Excel tables to fully populate an Access table. So I expect to manipulate things in Excel beforehand. A couple of the tables have 300 rows. So I expect it will be a major effort to untangle it all and get it into Access. But, yes, this is a one time exercise. Once in Access I will throw away the Excel and build forms to enter new data (this is my ultimate goal - to simplify future data entry). I might be willing to learn VBA, but I'd prefer to avoid that at this point.

    At this point I've designed my Access tables and they all have only a few lines of data. None are fully populated at this point except the top level Genre table. So, as you suggest, I could do a full dump from Excel, and then just weed out the rows I entered by hand.

    Thanks once again for your input.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Replies: 1
    Last Post: 04-25-2015, 01:44 PM
  3. Replies: 1
    Last Post: 04-25-2015, 01:41 PM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 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
  •  
Tech Forums: Microsoft Office Forums