Using access 2010 as a frontend to SQL Server database

Question

I am having to develop an application very rapidly. I have chosen SQL Server (2012) as the DB backend, and I will write all my stored procs, triggers etc in the backend.

However, for UI (logon, reporting etc), I am using Access 2010 for the frontend. I am new to both Access used earlier versions of Access waaay back in the day) and SQL Server (familiar with other Db's).

The goal is to have the database reside on a server and let clients connect with an instance of Access 2010 running locally on their machine.

I am looking for a quick tutorial that shows me how to use the SQL server objects from the Access frontend (I believe its called linking) - any link to useful resource would be very helpful, as I can't seem to locate anything useful (I may searching using the wrong keywords).

Answer 1

Don't mess about with linked tables. Use an Access ADP (Access data project), which is natively connected to SQL Server. Sadly this type of access file is being phased out but it is the optimal solution for an MS Access front end with a SQL Server back end

Pros and Cons of Access Data Project (MS Access front end with SQL Server Backend)

Answer 2

Assuming you built all the tables and data on SQL server, then in Access it is a simple matter to link Access to that database.

And to save development time, you can continue to use the Access simple approach of using forms bound to those tables. As long as you launch a form with some kind of criteria (say an invoice number), then that bound form will ONLY pull down the one record from SQL server into that form. (so need to write or use store procedures etc. for that form). And any triggers etc. you have built in SQL server will run without you having to do anything from the Access side.

So a plane jane form build in Access that is bound to a table of say 1 million rows in Access does not need any “special” code – just make sure you launch the form with the “where” clause that Access provides and the form will only pull + load the one record.

So 99% of the normal development process you used in Access in the past will continue to work. Using SQL server for the most part does not change much if anything in regards to building forms that edit such data.

However, for reports and some forms that query + search for data etc., or some VBA code that needs to “process” data, you are most free to call store procedures. You simply create a pass-through query in Access. The VBA code to use that T-SQL thus looks like this:

Currentdb.QueryDefs("MyRawt-sqlPassThoughquery").Execute

Or

with CurrentDb.QueryDefs("MyPass") 
     .SQL = "exec sp_myProc" 
     .Execute
end with

In the past for most access applications you likely used liked tables – those linked tables can be to a Access file (back end), or Oracle, or SQL server – how the actual application works and functions is really much the same for all cases. (so there not really a lot of “specific” things you need to know from the Access side – if you comfortable with Oracle, or SQL server, then using Access as a front end works just fine, and the typical development approaches used in Access will remain typical.

Here is an article that outlines the linking process:

https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-A5A3B4EB-57B9-45A0-B732-77BC6089B84E?ui=en-US&rs=en-US&ad=US&fromAR=1

Keep in mind you will see MANY articles that talk about ADP projects - they have been deprecated since Access 2010, and I don't recommend using ADP projects with Access - so be careful, since many articles that talk about Access + SQL server are built around ADP projects which as noted should not be used anymore.

Answer 3

This office.microsoft.com article should give you a good overview.

With plenty of more technical information searching for "query sql server from ms access".