Experts Exchange connects you with the people and services you need so you can get back to work.

Need help with making an OLEDB connection to SQL Server`

I have a front end that is currently using an ODBC file DSN to connect to SQL server for table linking and pass through queries. Our IT department has asked that a OLEDB connection be used to SQL Server.

I followed the instructions at the link below and created a udl file, but I am unsure of how to use it to connect to data.
http://wiki.answers.com/Q/How_to_set_up_an_OLE_DB_connection

How do I link my tables and pass through queries using this?

snyperj

Asked: 2011-09-12

Advertise Here

Microsoft Access

2 Solutions

LVL 77

peter57rCommented: 2011-09-12

As far as I know, you don't have such choices.

Access to SQL Server linked tables is ODBC - that's it. There is no OLEDB for linked tables.

Wait for others to confirm/contradict.

(You may be able create the linked tables using code that uses an OLEDB connection but as far as I know the link will still require/operate as an ODBC link)

LVL 77

peter57rCommented: 2011-09-12

I think you need to establish what the issue is here that results in this request from your IT people.

snyperjAuthor Commented: 2011-09-12

They have stated oledb connections are faster than odbc, and thus all data connections must be oledb.

LVL 77

peter57rCommented: 2011-09-12

Well you can only implement what is technically possible.

I know that have expressed my previous response in manner than suggest doubt - but in reality I'm not that doubtful. You just never know what some clever fellow might have come up with.

LVL 9

Armen Stein - Microsoft Access MVP since 2006PresidentCommented: 2011-09-13

In any case, ODBC is going to be supported instead of OLEDB:

http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/29/microsoft-aligning-with-odbc.aspx

Also, the main thing is that the Access app is optimized for SQL Server. I've written a PowerPoint presentation about it. It's called "Best of Both Worlds" at our free J Street Downloads Page:
http://www.JStreetTech.com/downloads

Cheers,
Armen

snyperjAuthor Commented: 2011-09-13

Thanks for the help and also for the interesting ppt. I was happy to discover that I HAVE been using most of the optimizing techniques mentioned in one manner or another.,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Need help with making an OLEDB connection to SQL Server`

Join & Write a Comment Already a member? Login.

Featured Post