+ Reply to Thread
Results 1 to 9 of 9

Update data from Excel to Access

  1. #1
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422

    Update data from Excel to Access

    Hi,

    I have a excel spreadsheet.

    I need to regularly update the information from the excel to access.

    How to I add / copy the information in excel to existing access table.
    Attached Files Attached Files
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,557

    Re: Update data from Excel to Access

    Probably the easiest way is to format your Excel Table to look like an Access table, then instead of uploading, link it and whenever you make any changes to the Excel table they are concurrently updated in the link so that it appears in Access exactly the same.

    https://www.techrepublic.com/article...cess-database/

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,866

    Re: Update data from Excel to Access

    Cross-posted: https://stackoverflow.com/questions/...lues-to-access
    Rory
    I drink, and I know things

  4. #4
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422

    Re: Update data from Excel to Access

    Hi Alansidman,

    Thanks for your reply.

    The issue I have is I am looking at getting the value on the access independent of the excel workbook. As I don't want to have issues if the excel file is moved or deleted etc.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,557

    Re: Update data from Excel to Access

    Ok. Then you can use VBA and Docmd.TransferSpreadsheet

    There are lots of tutorial videos on how to implement this. Just google DoCmd.Transferspreadsheet

  6. #6
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422
    Quote Originally Posted by alansidman View Post
    Ok. Then you can use VBA and Docmd.TransferSpreadsheet

    There are lots of tutorial videos on how to implement this. Just google DoCmd.Transferspreadsheet
    Thanks

    My preference is VBA excel as I have several other vba subroutine

    Is it possible to call it from Vba excel

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,557

    Re: Update data from Excel to Access

    Last edited by alansidman; 07-01-2020 at 04:17 PM.

  8. #8
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422

    Re: Update data from Excel to Access

    Thanks alansidman

    The information was very useful.

    Specially, https://www.onlinepclearning.com/exp...cel-to-access/ is literally a gold mine, so much to take from it.

    It worked perfectly as intended, to import and export data to access.

    Only issue I had was I couldn't get it to append / delete the data.

    It shows an error and I couldn't figure out why.

    Any ideas will be greatly appreciated

    I have added the excel file here The system would not allow me to upload the database file so I have only got the screen shot of it and as a zip file.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by saravnepali; 07-04-2020 at 05:52 PM.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,557

    Re: Update data from Excel to Access

    Without looking at the DB and Sheet, what this error message is telling you is that you have one data type for one or more of the fields in Access and a different data type for the same fields in Excel. They need to be the same.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data type mismatch excel to update records in ms access
    By aarona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2019, 10:44 AM
  2. Add or update an Access database record with data from Excel using VBA
    By bemidjipatriot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2017, 10:10 PM
  3. Update Access database from excel worksheet data through SQL in VBA Macro
    By anandagrawal444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2015, 07:06 AM
  4. trying to update access after data from access is imported to excel
    By justlearning123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2015, 04:52 AM
  5. Trying to update an access table from Excel based on unique ID. rst.Update not working
    By Newbie0924 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2015, 04:15 PM
  6. import from excel into access to update access, adding or subtracting
    By Tynus in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2014, 01:49 PM
  7. [SOLVED] export access to excel. change access & update excel at same time
    By fastcar in forum Excel General
    Replies: 0
    Last Post: 06-24-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1