Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Best way to Connect MS Access to SQL 2000 Tables?

    Hello All;

    I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?

    Can anyone answer that simple question?

    Grarful for ANY resonse,

    Larry.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Larry Dumoulin
    Hello All;

    I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?

    Can anyone answer that simple question?

    Grarful for ANY resonse,

    Larry.
    I've not worked with Access in a while, but here is my .02. Your mileage may vary:

    1. Linked tables are done through ODBC. ODBC has some limitations. Access has some additional limitations. It's fairly straightfoward to link tables, but you may run into issues down the road. We ran into one recently where MS Access mis-identified the primary key and displayed all data as "#Deleted" for a specific table. This wasn't something I set up, just something I was asked to trouble shoot.

    Users can't make schema changes to linked tables/views; however depending on their permissions they can add/update/delete records. MS Access defaults to opening a linked table as an updateable recordset.

    2. Access Data Projects (or ADP) eliminate the shortcomings of ODBC, but there may be some limitations on MS Access of which I am unaware. I have resorted to using ADPs only on my local desktop as a quick way to generate reports with better formatting than my limited web skills will permit.

    ADPs give you a window directly onto the database server; you need to be cautious about what permissions you assign to users as changes that they make (in views or tables) will affect your database. I AM NOT condoning, recommending or endorsing the ADP route; I have made very limited use of it for writing reports that are for my use only. I don't know how well it behaves "in the wild". ADP does give you the ability to link to objects other than just tables and views (specifically, you can access stored procedures).

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I do a lot of Access interfaces for SQL Server back-ends, and I choose Access Data Projects every time, hands-down, no contest.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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