7

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.

Improve this question
2
  • Downvoters please explain what is bad about this question. Otherwise I suspect you don't have a clue what I am talking about. Commented Feb 7, 2014 at 15:53
  • Judging by the answers so far my question might not be as trivial as it appeared to the downvoter. Commented Feb 7, 2014 at 20:59

3 Answers 3

Reset to default
1

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.enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for answering. Yes, linked tables are great, but my question was about bound forms, being able to filter on the server (as with pass through queries) but avoid read-only forms.
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

Improve this answer

Comments

0

Did you see article: http://support.microsoft.com/kb/303968 Also, can you just create a VIEW in SQL Server and reference that?

Improve this answer

2 Comments

Thank you for reference. But as far as I understand "Forms based on SQL pass-through queries are read-only because SQL pass-through queries are read-only." (support.microsoft.com/kb/112746) Linked SQL-Server views don't have that drawback, but I can't change them dynamically. Maybe I can't use bound forms then and I have to use the next best method to mimick the behaviour. Right?
Possibly you could create a VIEW on SQL Server that reduces the amount of data to acceptable limit, then further filter in Access? Unless you have a very large database (or slow network) you may not need to worry about the volume of data transferred.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.