Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unanswered: Access/ODBC/SQL Server Fun and Games

    Right Chaps...

    We have a single copy of an access database to be held on a server, which will be accessed via a shortcut to a shared path holding the database on 2 or 3 client machines.

    Problem is...

    The database has three tables in it which come from an SQL Server database. In the previous incarnation of the system, the database was only used on one machine, and so one local User DSN pointing to the appropriate SQL database did the trick....

    However, now, I think I need a DSN on each machine that will access the central database file... This works when opening the database from the machine where it actually lives, but Access dies a spectacular death if I try to open it from a remote machine.

    Yes I know it sound like a bad solution, and it is, but I'm not designing I'm just following up help-calls from the users...

    Any clues as to where I'm going wrong, or whether this is even possible would be much appreciated.

    Cheers.

    Soy.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's a little trickier if you're not "designing". There are methods to programmatically link tables from within access using vba. You may be able to pull off a file dsn as well...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by BigSoy
    Right Chaps...

    We have a single copy of an access database to be held on a server, which will be accessed via a shortcut to a shared path holding the database on 2 or 3 client machines.

    Problem is...

    The database has three tables in it which come from an SQL Server database. In the previous incarnation of the system, the database was only used on one machine, and so one local User DSN pointing to the appropriate SQL database did the trick....

    However, now, I think I need a DSN on each machine that will access the central database file... This works when opening the database from the machine where it actually lives, but Access dies a spectacular death if I try to open it from a remote machine.

    Yes I know it sound like a bad solution, and it is, but I'm not designing I'm just following up help-calls from the users...

    Any clues as to where I'm going wrong, or whether this is even possible would be much appreciated.

    Cheers.

    Soy.
    All you need to do is change your USER DSN to System DSN in your ODBC set up. Call it a name and point the configuration to your Access database path/name. YOu don't need to have a DSN in each workstation when you use the System DSN set up because it would be shared within your networking
    WHen you open your connection, call out this new name. YOu would want to share the directory where the database resides also. GOod luck
    Last edited by fredservillon; 11-01-05 at 17:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •