Results 1 to 7 of 7
  1. #1
    KramerJ is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Las Vegas, NV
    Posts
    15

    Automate Excel Import to Access Related Tables

    I have been successful using the DoCmd Spreadsheet object to import Excel data into an Access Table, pretty straight forward. But how do I apply the import data to related tables where I need to capture initial data and place into one of the related tables and capture other data and place into a second related table, all in the same database. Help Plz.

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Use the insert query to insert the imported data to the relevant table after the import.

  3. #3
    KramerJ is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Las Vegas, NV
    Posts
    15

    Automate Excel Import to Access Related Tables

    Thanks for your reply. My goal is to completely automate the process, import and update, using VBA. Were your referring to use the Insert Query in the VBA code or manually do it.. TIA

  4. #4
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    If you want to automate the process,
    you must use access vba to execute the query.

    eg.
    Code:
    docmd.openquery "queryname"

  5. #5
    KramerJ is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Las Vegas, NV
    Posts
    15

    Automate Excel Import to Access Related Tables

    Thanks Thhui, I will give that a try while I am learning to use VBA.

  6. #6
    rommelgenlight is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    77
    what is your purpose of importing data to related tables? If you intend making a relational tables on your database, you should learn database normalization, which basically says, you can relate any two or more tables wherein they have the same common ID or reference for that matter.

    for example:

    TABLE SUPPLIER:
    fields: SupplierID, SupplierName, SupplierAddress

    TABLE PRODUCTS:
    fields: ProductID, SupplierID, ProductName

    as you can see, you can related the two tables: supplies & products BY the SUPPLIERID which they have in common.

  7. #7
    KramerJ is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Las Vegas, NV
    Posts
    15

    Automate Excel Import to Access Related Tables

    Because I already have existing related tables in the database and now I have to capture Excel worksheet data and append the information to those
    tables.

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

Similar Threads

  1. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM
  2. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 PM
  3. Setting default Unicode Compression when import from Excel
    By AndrewAfresh in forum Import/Export Data
    Replies: 0
    Last Post: 03-16-2009, 04:14 AM
  4. Split a table into related tables
    By triley01 in forum Database Design
    Replies: 1
    Last Post: 03-12-2009, 02:38 PM
  5. Unable to import or link tables through odbc in Access SP2
    By Dave Jenkins in forum Import/Export Data
    Replies: 3
    Last Post: 11-09-2005, 11:51 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