none
Automatic Updates of Access table from source Excel Spreadsheet RRS feed

  • Question

  • <g class="gr_ gr_18 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="18" id="18">Hi</g> all!

    I'm sure this has been asked before, but I wanted to run something by you all. I have an access database that I created using data from an Excel spreadsheet. I want that database to update whenever I update the spreadsheet. Is there a way I'm missing on how to do that. 

    For reference, I'm doing this to power a SharePoint list. We can't turn on web services for Excel so I'm using the Open in Access in SharePoint to get the data. I don't have other options at this point and YES, I realize that it's not the best way. It's the only way I got right now. 

    Thanks so much in advance. 

    Tuesday, September 19, 2017 9:34 PM

All replies

  • Hi JamiBenson79,

    I would suggest you to create a linked table in access.

    Please refer to below link for help.

    https://support.office.com/en-us/article/Import-or-link-to-data-in-another-Access-database-095ab408-89c7-45b3-aac2-58036e45fcf6?ui=en-US&rs=en-US&ad=US#linkdata

    Best Regards,

    Terry

    Wednesday, September 20, 2017 9:50 AM
  • I'm sorry if this sounds dense, but let me understand what you're proposing. I should create ANOTHER access database to link my data to? That seems like a whole lot of work. Regretfully connecting to the actual SQL source of the data isn't something we can do. Our only real means of working with this data is via Excel. At least until I can convince someone to drop the data into SQL database for me. 

    Any other thoughts? 

    Thanks for your response! 

    Thursday, September 21, 2017 5:05 AM
  • Hi JamiBenson79,

    Are you using Access as front end database and SQL Server as Back End database?

    If you want to update front end using Excel, I would suggest you use ADO or DAO in excel VBA to update Access Front End.

    Please refer to below link for more information.

    How To Use ADO with Excel Data from Visual Basic or VBA

    Best Regards,

    Terry

    Friday, September 22, 2017 8:52 AM
  • Hi JamiBenson,

    >> I want that database to update whenever I update the spreadsheet.

    Do you need to update excel data from Access database? If not, you could try create a link table from Excel spreadsheet to Access database.

    >> I should create ANOTHER access database to link my data to?

    No, you could create link table on the current database.

    >> I should create ANOTHER access database to link my data to?

    It is very convenience, you could follow below steps.

    External Data->New Data Source->From File->Excel->Enter the File name->Select Link to the data source by creating a linked table.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 28, 2017 7:21 AM