Results 1 to 7 of 7
  1. #1
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32

    Access query in Excel

    Hi



    I've finally made a database that I'm pretty happy with as it's my first go, I'm sure I've done a lot wrong to annoy the experts, but I'm really enjoying learning how it works.

    I'm having an issue, my database has a linked table in it, this table sometimes needs to be opened by users to make some edits, if the database is open when they do this, the database crashes. I think this is happening because the linked table is also looking at the database in the form of a query. So I think some circularity is causing some problems.

    So to summarise:

    Database1 looks at spreadsheet1 (as a linked table)
    Spreadsheet1 also looks database1 (as a access query)

    If the spreadsheet is opened while the database is already open, it opens read-only
    if the database is opened while the spreadsheet is already open, the database crashes.

    Is there anyway to have them both open and still be able to make changes to spreadsheet1.

    I realise that it would be better to not have a linked table and just have spreadsheet1 as part of database1 as an import, and then make all the changes in the database, but at this point in time, this isn't feasible.

    Many thanks,

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    As you have newest version of Office, and there are added different new ways to link tables lately, then I suspect you have the Excel table linked for Read-Write. When you link the Excel file as Read only, another user having the Excel file opened don't affect Access.

  3. #3
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Quote Originally Posted by ArviLaanemets View Post
    As you have newest version of Office, and there are added different new ways to link tables lately, then I suspect you have the Excel table linked for Read-Write. When you link the Excel file as Read only, another user having the Excel file opened don't affect Access.
    THanks for this, yes I am using the query feature on Excel that I've not used before. I'm not sure how to edit it to make it Read-Only though, do you have any advice?

    Many thanks,

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    In the data link properties, you can specify it to only read. See attached.
    Attached Thumbnails Attached Thumbnails DataLinkProperties.PNG  

  5. #5
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Apologies, How do I access the data link properties menu? is it Access or Excel?

  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    The data link properties is in Excel, you can get it by going to the Data tab and clicking Existing Connections. If you already have a connection that is.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Perceptus View Post
    The data link properties is in Excel
    I'm afraid in current case OP needs the link in Access to be read-only! I don't have MS Office available at moment, so I can't check possible solutions myself, but maybe this link will be helpful
    https://support.office.com/en-us/art...d-e084913cc958

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

Similar Threads

  1. Replies: 2
    Last Post: 12-15-2017, 08:09 PM
  2. Replies: 2
    Last Post: 04-06-2015, 12:08 PM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  5. Replies: 1
    Last Post: 08-31-2009, 10:24 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