Office.com articles for using Access with SQL Server

  • Article

A customer named The Borg recently responded to our (Almost) everything you ever wanted to know about Access 2010 blog post with the following comment:

Access is such a great tool, with all sorts of data sources, but when we ask our users to migrate from using Access-centric backen data sources to only SQL server data, documentation is lacking. In our business the single biggest change will be that edict to use only SQL Data, while still using the same FE interfaces. You then look into how this can be achieved and you find subjects that refer to access services etc, but do not specifically provide Access FE -> SQL backend suggestions.

Office.com has content on four ways of using Access with SQL Server: 

  • Import or link to data in an existing SQL Server database.
  • Move Access data to a SQL Server by using the Upsizing wizard (resulting in linked tables to the SQL Server).
  • Create an Access Project (ADP) file, which provides tighter integration between the SQL Server database and the Access application.
  • Use a pass-through query, which involves sending a SQL string via ODBC to a server, having the server process the query, and only the results are returned over the network (as opposed to using linked tables, in which case some processing occurs locally). The scenario is either that you have a very underpowered desktop and want to use the server for query processing, or you want to run a query that invokes a stored procedure on the SQL Server - which isn't supported locally.

Some of the content has not been updated for the latest versions of Access, but the older content is still mostly valid because the features involved have not changed greatly over time, although there are some minor differences in the locations of specific commands.

Click the following links for more information about these options. 

When searching Office.com for more information about specific features, you can sometimes find more information by making sure your search is not "scoped" to a specific Access version. On the left side of the search results page, try clicking All Programs, or use the Edit my programs feature to add a few earlier versions of Access to your search scope, and then click Microsoft Access to search across all the versions you added.

 

 (Many thanks to the Office.com Access writers for assisting with this blog post.)