+ Reply to Thread
Results 1 to 3 of 3

directory for Access DB when using ODBC

  1. #1
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    directory for Access DB when using ODBC

    I am extracting data from an Access DB to an Excel file. For the connection I use an ODBC (64 bit).

    Now the issue is the following.
    In my PC I have the Access file in Documents. I set up the ODBC. Then I make a pivot table in my Excel, I refresh data (Excel "Data" menu) and everything looks fine.

    Then I go to another PC, I copy Access to the local Document folder and I make a local ODBC connection pointing to the just copied Access file. However the refresh in Excel does not work.
    In order to make it work I have to move the Access file to C: (and remake the ODBC).

    Shouldn't it work with Access in Documents? Is it a problem of permissions? Should the user have some specific grants?
    Since the Access file needs to be replaced often, with new versions (at least in this phase), the choice of the location is important: the user cannot copy the file to C:\ by himself and he needs to contact the admins.

    Thank you for any suggestion
    F

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

    Re: directory for Access DB when using ODBC

    Each user's Documents folder path will be different, which is why your approach won't work. You'd need to use a path that will be the same on each machine, or use code to amend the connection.
    Rory
    Yea though I walk through the valley of the shadow of death, at least it's a change of scenery

  3. #3
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Re: directory for Access DB when using ODBC

    Quote Originally Posted by rorya View Post
    Each user's Documents folder path will be different, which is why your approach won't work. You'd need to use a path that will be the same on each machine, or use code to amend the connection.
    yes, ok, but I made a new ODBC in the users's PC. And the ODBC points to the folder where data is (C:\Users\user_name\Documents)
    Is there anything hard-coded?
    by the way the error is [DataSource.Error] ODBC: ERROR [IM002] [Microsoft] [ODBC Driver Manager] Data source name not found etc.

    Anyway a location such as C: or C:\something is ok for my software...

    Thanks
    F

+ 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. Multiple Access in ODBC sheet
    By jarett in forum Excel General
    Replies: 0
    Last Post: 05-08-2019, 11:57 AM
  2. ODBC Access driver login failed
    By aharreluark in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2010, 08:32 PM
  3. [SOLVED] Updating data with an ODBC link to ACCESS
    By in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-06-2006, 09:10 AM
  4. [SOLVED] Excel/Access ODBC
    By Emma Hope in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2005, 10:05 PM
  5. [SOLVED] VBA - ODBC database access help?
    By Carl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2005, 04:05 PM
  6. Excel / Access ODBC connection
    By Rasmus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2005, 08:06 PM
  7. [SOLVED] ODBC database access
    By ooo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2005, 09:44 PM

Tags for this Thread

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