Results 1 to 5 of 5

ODBC problem

  1. #1
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    50

    ODBC problem


    I'm trying to create DSN-less connection to one SQL Server table through MS Access. My problem is that it only works for me and I don't know why it doesn't work for anyone else. The Access database lives on a network and should be available for anyone to use. Other users get error messages when they try to open the linked table, however. Maybe the problem is that my user ID is somehow automatically populated in the connection string but I don't know how to substitute another user's ID when they open the table.

    Does someone have experience with this? Thanks.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,242
    please can you clarify your terminology. You say 'to one SQL Server table' and 'The Access database lives on a network'. Are you saying you have two databases? or the front end is your Access database? If the latter, you are asking for trouble - front ends should not be shared, but located on each users machine. Failure to do so will lead to corruption.

    Either way, ODBC drivers are installed on users machines

  3. #3
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    50
    I'm sorry I wasn't clearer.

    I have an Access front end and I have the single table on SQL Server.

    The front end is shared and corruption has not been a problem.

    So are you saying that it's impossible to have a common Access front end with an ODBC connection?

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,242
    The front end is shared and corruption has not been a problem.
    give it time, it will be - plenty of threads on this and other forums where this has caused complete loss of the file. It is also again Microsoft advice. See this link, share a split database section https://support.office.com/en-us/art...a-68da245a0446

    So are you saying that it's impossible to have a common Access front end with an ODBC connection?
    the fact the FE is sitting on a server somewhere does not enable users who access it to access the sql server backend unless they have the appropriate permissions and drivers. So not impossible, but they won't get drivers from the FE and permissions is as much about the security policy for sql server as anything else

  5. #5
    Join Date
    Apr 2017
    Posts
    922
    a)
    You have SQL Server database. You create Access FE with SQL Server DB as BE. All users have computers in same LAN as SQL Server, and have proper rights for SQL Server DB. Every user has a copy of FE in his/her computer. In case there is need for remote access for user, the user logs remotely (using a VPN connection) into his/her computer in LAN, and opens DB from there (this means, the computer must be left working, or it has the configuration which allows remote wake-up).

    b) With SQL Server all is same as with variant a. You have a terminal server in same LAN as SQL Server, and every user has a profile in TS. On TS, Access is installed, and every user has FE in his/her TS profile. Users log in into TS from their computers in LAN, or from any remote location (using a VPN connection), and open their FE copy from there.

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

Similar Threads

  1. ODBC Connection Problem
    By Robert2150 in forum SQL Server
    Replies: 3
    Last Post: 02-04-2019, 08:19 AM
  2. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  3. ODBC problem
    By SColaux in forum Import/Export Data
    Replies: 7
    Last Post: 01-09-2017, 07:51 AM
  4. Access front-end database ODBC problem
    By cuddles in forum Access
    Replies: 0
    Last Post: 07-31-2014, 12:47 PM
  5. Replies: 6
    Last Post: 08-18-2013, 12:47 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
  •  
Tech Forums: Microsoft Office Forums