+ Reply to Thread
Results 1 to 6 of 6

Issues with Data Connections and opening connected files

  1. #1
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Issues with Data Connections and opening connected files

    Hello all,

    I have a problem with a file that we use at work, that feeds from data from 4 separate files. These 4 separate files are updated individually, but the problem comes when the main file is opened. When this main file is opened by anyone, it blocks access to the rest of the files, making the user open the secondary file in read only. Why does this happen if the main file is opened up as Read only? Does the connection have to do with this?

    How can I fix this?

    Regards,
    Bacon

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Issues with Data Connections and opening connected files

    What Version of Excel are you using, and what type of data connection is used (i.e. what type of tool is used to connect to source files, MSQuery, ADODB, PowerQuery etc)?
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Re: Issues with Data Connections and opening connected files

    Hey CK! Thanks for your quick reply.

    I am using Excel 2016 and the data connection is not something I am 100% sure about. What I did is:
    • Data
    • Connections
    • Add
    • Select the Excel File
    • Select the table that has the data

    These are the steps that I used to connect to the other files. Does this clarify the type of connection a bit?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Issues with Data Connections and opening connected files

    If you are getting data using "Get & Transform Data" under Data ribbon. Then that's through PowerQuery and should not lock up source file when Master is open.

    If the file was created in older version originally, that may be the cause of such issue.

    When you check Property of the connection, what does "Connection string" found under Definition tab, show as Provider?

  5. #5
    Registered User
    Join Date
    03-20-2019
    Location
    costa rica
    MS-Off Ver
    windows 10
    Posts
    10

    Re: Issues with Data Connections and opening connected files

    Hello CK,

    The connection was made from the "Connections" tab in the data ribbon. Now that you mention that, it is right beside the "Get & Transform" group. I never clicked what you describe. What do you mean "If the file was created in older version originally"?

    Provider shows: Provider=Microsoft.ACE.OLEDB.12.0

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Issues with Data Connections and opening connected files

    So you are importing data using legacy method. Use Get & Transform -> From workbook.
    It should give Microsoft.Mushup.OLEDB as provider.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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