Microsoft Access Integration Tips

Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver. This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database. Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio. In some respects, it is the perfect tool for the casual business user who wants to drill into and share their data. So what's next?

Tired of using SSIS packages to import data from Access to SQL Server? Is SQL Server Integration Services more complicated than what you are looking for? Check out this tip to see how to import data from Access (Access 2007 or above) to SQL Server.

We have a few SQL sandbox databases set up in our environment that allow our developers and some key end-users the ability to do some temporary development without expectations of performance or durability. Quite often these individuals will want to use Access as their front-end GUI tool for these databases. Realizing that this solution exposed the Database Administrator to endless phone calls asking for assistance, I developed a document that anyone could follow in order to get an SQL-via-Access implementation up and running on their own. This is the first of four tips focusing on connecting Microsoft Access to a SQL database.

Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name (DSN) using the OLEDB driver, created a System DSN for the new SNAC (SQL Native Client) driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment! There has to be a better way!

As we continue to discuss Microsoft Access in this series of tips it seemed important to revisit in more detail a tip I produced in 2008 on using Pass-Through Queries inside of Microsoft Access. Just what is a Pass-Through query? It is a construct in Microsoft Access that allows you to code the query text in the language of the back-end relational database management system - be it SQL Server, Oracle, or the like. The query is then passed back to the RDBMS in its native language for execution; results being returned back to Access.

In a previous tip we saw how easy it was to link to SQL Server tables from Microsoft Access. As is the case with all systems, how does Access manage the changes? What happens when you modify the structure of the underlying SQL Server table? What happens to the SQL Server table if you delete the linked table in Access? We will look at each of these situations in this tip.

Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver. This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database. Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio. In some respects, it is the perfect tool for the casual business user who wants to drill into and share their data. So what's next?

Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name (DSN) using the OLEDB driver, created a System DSN for the new SNAC (SQL Native Client) driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment! There has to be a better way!

In a previous tip we saw how easy it was to link to SQL Server tables from Microsoft Access. As is the case with all systems, how does Access manage the changes? What happens when you modify the structure of the underlying SQL Server table? What happens to the SQL Server table if you delete the linked table in Access? We will look at each of these situations in this tip.

We have a few SQL sandbox databases set up in our environment that allow our developers and some key end-users the ability to do some temporary development without expectations of performance or durability. Quite often these individuals will want to use Access as their front-end GUI tool for these databases. Realizing that this solution exposed the Database Administrator to endless phone calls asking for assistance, I developed a document that anyone could follow in order to get an SQL-via-Access implementation up and running on their own. This is the first of four tips focusing on connecting Microsoft Access to a SQL database.

Tired of using SSIS packages to import data from Access to SQL Server? Is SQL Server Integration Services more complicated than what you are looking for? Check out this tip to see how to import data from Access (Access 2007 or above) to SQL Server.

As we continue to discuss Microsoft Access in this series of tips it seemed important to revisit in more detail a tip I produced in 2008 on using Pass-Through Queries inside of Microsoft Access. Just what is a Pass-Through query? It is a construct in Microsoft Access that allows you to code the query text in the language of the back-end relational database management system - be it SQL Server, Oracle, or the like. The query is then passed back to the RDBMS in its native language for execution; results being returned back to Access.

Tired of using SSIS packages to import data from Access to SQL Server? Is SQL Server Integration Services more complicated than what you are looking for? Check out this tip to see how to import data from Access (Access 2007 or above) to SQL Server.

As we continue to discuss Microsoft Access in this series of tips it seemed important to revisit in more detail a tip I produced in 2008 on using Pass-Through Queries inside of Microsoft Access. Just what is a Pass-Through query? It is a construct in Microsoft Access that allows you to code the query text in the language of the back-end relational database management system - be it SQL Server, Oracle, or the like. The query is then passed back to the RDBMS in its native language for execution; results being returned back to Access.

In a previous tip we saw how easy it was to link to SQL Server tables from Microsoft Access. As is the case with all systems, how does Access manage the changes? What happens when you modify the structure of the underlying SQL Server table? What happens to the SQL Server table if you delete the linked table in Access? We will look at each of these situations in this tip.

Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name (DSN) using the OLEDB driver, created a System DSN for the new SNAC (SQL Native Client) driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will return/load the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment! There has to be a better way!

Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver. This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database. Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio. In some respects, it is the perfect tool for the casual business user who wants to drill into and share their data. So what's next?

We have a few SQL sandbox databases set up in our environment that allow our developers and some key end-users the ability to do some temporary development without expectations of performance or durability. Quite often these individuals will want to use Access as their front-end GUI tool for these databases. Realizing that this solution exposed the Database Administrator to endless phone calls asking for assistance, I developed a document that anyone could follow in order to get an SQL-via-Access implementation up and running on their own. This is the first of four tips focusing on connecting Microsoft Access to a SQL database.