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.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|||I do a lot of Access interfaces for SQL Server back-ends, and I choose Access Data Projects every time, hands-down, no contest.