SQL-Server Backend, MS Access Frontend: Connection

Question

Connecting from MS Access to SQL Server via ODBC (not necessarily). I would like to bind my Access forms to SQL Server data. It would be great being able to dynamically filter recordsets on the server (as with pass through queries) but I would like to avoid the forms being read-only. I am very grateful for any suggestions. Thanks in advance. I have found similar questions here but not the answer that I am looking for.

I worked with following articles:
Access DB with SQL Server Back End - Stack Overflow
Import or link to SQL Server data - Access - Office
Microsoft is Aligning with ODBC for Native Relational Data Access - FAQ
Microsoft is Aligning with ODBC for Native Relational Data Access - Microsoft SQLNCli team blog - Site Home - MSDN Blogs
Optimizing Microsoft Office Access Applications Linked to SQL Server
performance - Best Practice SQL Server_Access connections - Stack Overflow
sql server - How to make a passthrough _ passthru query editable_ - Stack Overflow
sql server - SQL and MS access- filtering of the data on the form - Stack Overflow
Using Access to build a front end for SQL Server - TechRepublic
Wiley.Microsoft.SQL.Server.2008.Bible.Aug.2009_Excerpt_Access
Access as the front end and sql server as the backend - Stack Overflow

I could not find the answer, but I might have overlooked it. Well I must have.

Solution 1

A passthrough query is only EVER going to give you read-only data in MS Access mdb's and accdb's. There is nothing that you can do to change that. So, you are going to have to work around that limitation in the manner of your choice. One way is to create a local table to hold the data of the passthrough, and bind the form to THAT data. It will then be editable. You then have to create a mechanism to push the changes made to the local data back to its original source. That involves a fair bit of heavy lifting but is entirely doable. You get the speed and precision of the passthrough, but you lose the nice functionality of live editing and must code it. Or you go with ODBC linked tables, and Access linked tables that are ODBC-linked to indexed Views and gain the ability to live edit, but lose the speed and precision of a passthrough.

You can't have your cake and eat it, too. Sorry

Solution 2

If you need to connect to SQl Server Database from MS Access then ODBC connection is the way to go, to perform that you have GUI option as well as shown below.