Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: URGENT - why can't I link to Access?

    Hello,

    I think I may be going a little bit mad. Last week I created a database on SQL Server. I then created an access database on a different windows server, and set up an ODBC connection on that windows server to my database on SQL Server. I then set up linked tables within Access to point to my tables on the SQL Server. I'm fairly sure that I opened those links at the time and managed to display and update the data from Access. However I have come to this today and when I go into the Access database and attempt to open one of the links I get a connection failed message. When I check the ODBC connection on the windows server it is fine. Does anyone have any ideas on this? I really need this to be working by tomorrow to do a demo, and I'm so convinced it worked last week I am a little bemused!

    Many many thanks in advance of your wonderful and prompt replies!!!

    Paula

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    try setting up a access data project and please do not open whole tables in sql server from access.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    The whole point of the exercise is to centralise the data. Setting up an Access database used by many is never a good idea, which is why I am moving the data to SQL Server. I do however want the users to be able to use queries etc within Access (depending on the security level they have in SQL Server) to view the data as they are currently accustomed to doing. They are not trained in SQL Server and will never have enterprise manager on their machines, therefore they need Access. All the data is static and not of great volume. I of course would not provide a user with an Access front end to view massive tables held in SQL Server. I'm a DBA!

    My problem was the connection failure I was experiencing. I now realise that I need to set the ODBC connection up on individual user machines rather than on the server. So problem solved, but thank you for your response.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sigh. An access data project (.adp) does not hold data. It provides a more lightweight front end to SQL Server. Instead of linking tables, you connect to the database in sql server and the connection information does not rely on an odbc dsn on the user machine but on connection information stored in the project.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Ok I see, how do I create a .adp instead of a .mdb?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    here the directions from the access 2003 help file. first time I have used access in 6 months...

    Code:
    On the File menu, click New. 
    Do one of the following: 
    Create a Microsoft Access project and connect it to an existing Microsoft SQL Server database
    
    In the New File task pane, under New, click Project (Existing Data).
    
    In the File New Database dialog box, click a location in the Save in box, and type a name in the File name box; make sure that Microsoft Access Projects appears in the Save as type box, and then click Create.
    
    On the Connection tab in the Data Link Properties dialog box, enter a server name and the information necessary to log on to the server, and then select the database on the server. For additional information, click Help in the Data Link Properties dialog box.
    In 2000 I believe it was Fie-->New--> Access Data Project.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thank you.

Posting Permissions

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