Results 1 to 4 of 4
  1. #1
    dRAGZOR is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6

    Question What's the best way of using adding data to a linked excel table

    Greetings Access Wizards,

    Ive run into a little bit of a pickle when working with linked tables. I'm trying to create a system that take the information from our payroll system and inputs it into access so we can create easy to read reports. I've wrote a python program that coverts the output of the payroll program into a readable .xlsx of (Project Number, Project Name, Hours Budgeted, Hours Spent, Due Date). The program I wrote will update existing project's hours and due date and will add projects that are not currently on the worksheet. I was hoping to just use the excel document as a master table and through an access form add the project manger and any employees attached to each project. However you cant seem to modify or add data to an excel document from access.

    Is there a work around to this?



    Can I somehow have a local access table update to match from the excel worksheet without duplicating project numbers while updating the hours?

    Should I have a separate access table with matching keys (Project numbers) and just query them together?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't modify Excel via table link but can use VBA Excel automation. Ain't simple.

    I would not use Excel as master.

    Normalized database would have a master table of ProjectInfo (including numbers) and then payroll info would be in related table.
    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
    dRAGZOR is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    6
    Is there a way to use the excel document to update the hours of "
    a master table of ProjectInfo" with the excel document without duplicating project numbers?

    Or should I have ProjectInfo: ProjectNumber(Key), Status, Project manager, Employee
    ExcelInfo:
    ProjectNumber, Hours budgeted, hours spent, due date

    and somehow link them by the ProjectNumber with a query

    I would like it to add a project from the Excel to
    ProjectInfo if it doesnt already have it.


  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, two tables and connect in query. That's the way relational database should work.

    Yes, if you have link to Excel worksheet can use that as a source for UPDATE and/or INSERT action to local table. Review https://stackoverflow.com/questions/...g-in-ms-access
    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.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-16-2019, 03:38 AM
  2. Replies: 8
    Last Post: 05-23-2018, 02:39 AM
  3. Replies: 1
    Last Post: 01-19-2017, 10:13 AM
  4. Adding data to an external linked table
    By Toasty in forum Import/Export Data
    Replies: 2
    Last Post: 06-12-2014, 07:10 AM
  5. Adding data from linked table to existing table???
    By claysea in forum Import/Export Data
    Replies: 3
    Last Post: 02-21-2013, 12:23 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