Results 1 to 3 of 3
  1. #1
    MarketaD is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    2

    Question How to use excel file stored on sharepoint in an access database

    Hello,



    I have a question. I need to create a new database from excel files that are stored on sharepoint. Unluckily, I could not find a way to establish a connection between the Access and excel tables. All I just found was, that there is a way to create a list of sharepoint files and keep the files updated with the aim of access. I did not find a way to link the access database with an excel file so that I would actually see the excel table as usually and create queries with it. Any ideas?

    Many thanks in advance.
    Marketa

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In my very limited experience with Excel in SP I had to have permissions on the SP server. In other words, I could see it in Windows File Explorer - before I could do any development. Only certain users had use of this particular process and they all had permissions on that server as well so it's my recollection that it would not run for anyone who didn't have those permissions. However, I used Automation to push Access data to the workbook, probably because a workbook could not be modified without "checking it out" first. If anyone was in it, it could not be accessed, thus I never even attempted to link the sheet in Access. Moving sheet data into/out of Access via automation might be your only option. Your code would have to determine if the workbook is checked out, which is an actual property of the workbook IIRC.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MarketaD is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    2
    Quote Originally Posted by Micron View Post
    In my very limited experience with Excel in SP I had to have permissions on the SP server. In other words, I could see it in Windows File Explorer - before I could do any development. Only certain users had use of this particular process and they all had permissions on that server as well so it's my recollection that it would not run for anyone who didn't have those permissions. However, I used Automation to push Access data to the workbook, probably because a workbook could not be modified without "checking it out" first. If anyone was in it, it could not be accessed, thus I never even attempted to link the sheet in Access. Moving sheet data into/out of Access via automation might be your only option. Your code would have to determine if the workbook is checked out, which is an actual property of the workbook IIRC.
    Hello,
    Many thanks for your reply. Nevetheless, it did not work. I came up now with a new idea. I will create a SP list and try to connect the MS Access DB directly to it. I hope it will work.
    Have a nice day.
    Marketa

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

Similar Threads

  1. Replies: 2
    Last Post: 10-08-2020, 11:33 PM
  2. Replies: 1
    Last Post: 04-26-2016, 03:56 AM
  3. Replies: 1
    Last Post: 07-24-2015, 07:06 AM
  4. Importing Excel file stored on sharepoint
    By tezza79 in forum Import/Export Data
    Replies: 0
    Last Post: 01-27-2015, 01:29 AM
  5. How to access excel file from sharepoint site
    By selvakumar.arc in forum Forms
    Replies: 0
    Last Post: 07-22-2014, 11:18 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