ODBC, OLE DB, SQL Pass through- differences, pros, cons please....

Posted on 2007-10-09
Medium Priority
Last Modified: 2008-01-09
I am looking into ways to connect to a SQL server 2000 database from Access.

Could someone give me PRO's & CONS of connecting via ODBC as compared to OLE DB?
 Additionally- Can much of the same be done with SQL pass through queries?

 I am looking for the easiest way to get look-up only access to some SQL server 2000 tables and views from a split Access 2000 database of about 40 users.  

Any info appreciated as I try to learn this.
Question by:snyperj
LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 20039968
If you have an mdb frontend then for linked tables you will be using ODBC not OLEDB.  You don't have a choice.

If you use an adp frontend you use oledb. You don't have a choice.

LVL 18

Assisted Solution

jmoss111 earned 1000 total points
ID: 20041316
For 40 users with read only access you'd probably be better of using pass through. While not as easy as linked tables, the performance and decrease in network traffic would be worth it.

Author Comment

ID: 20248761
thanks for the advice

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question