SQL SERVER & ACCESS: working on large tables

Tweet

When dealing with large tables, with big quantities of data, it might be a good idea to link a SQL Server table to Ms Access.
I know that the above statement doesn't sound too much professional, but in my experience it is incredibly easier to manage data using the Ms Access interface than using SQL Server Management Studio (SSMS).

Linking a SQL Server table to Ms Access is fairly easy. From the file menu we can find the appropriate command to link an external table. We actually have different choices, but what we need to do is to connect to the ODBC service and link to a SQL Server database. It is clear that the first step is to create a System DSN for the appropriate SQL Server database. To do so is quite easy, because the ODBC control panel will guide us through the procedure: just follow the steps and create the DSN connection.
Once we have done that, we can link a specific table inside the connected database directly from Ms Access.

When Ms Access has a linked table, we can freely work on the data contained and benefit from the flexible tools provided by Access. Some of this basic tools are not really available in SSMS and even every day shortcuts like cut, copy and paste are managed in an easier way through the Ms Access interface.

Some little tricks need to be follow, though.
Bit data fields
First of all, we need to remember that SQL Server and Ms Access treat bit data differently. For that reason, we might get crazy trying to understand why Ms Access sometimes gives us a warning about different users updating a table at the same time, not allowing us to save the changes. In most of the cases, that warning is due to bit data fields.
To avoid the above warning, we should remember that when we want to duplicate a record, we need to select all the columns (avoiding the hiding of columns). The same when we need to insert a new record: it is better to copy and paste a row than inserting it from scratch.

Copy and paste
Copy and paste can be used quite freely, even on entire columns, records selections, single values in fields and even specific parts of a field. That will give us an incredible freedom when we need to update large quantities of data.
The same can be said when we need to use Ms Access search and replace functions. For example, if we need to change a specific word inside a field, we can use the replace function quite effectively.
I know that someone reading this very post, will think that with the use of an appropriate update SQL statement in SSMS we can obtain the very same result (with much more control)... well... that is true. However, sometimes working with specific SQL statement can be more complicated than just selecting the column, applying a filter and using the search and replace function in Ms Access.

Filters and columns selection
Other important things are related to filters. As you may already know, in Ms Access applying filtering to data is very easy: just press the right hand mouse button and apply filter according to selection.
That is a really powerful feature, especially in conjunction with the selection of specific columns. We can infact show and hide columns and filter data, as explained above, at the same time, giving us the opportunity to work only on specific sections of the table and specific groups of recordset.

Inserting new columns

It is clear that we cannot perform any operation on the table structure using Ms Access. Even if we can see the linked table structure, we can't insert, delete or update the columns name or the data type. That job can only be done through SSMS.
However remember that after changing the table structure, we need to update the linked table. That can be done inside Ms Access in the tools menu (linked tables).

Conclusion
I know that I just scratched the surface of the matter. I have been working on large amount of data for a long time, importing data from external sources, using SSMS and SQL statements. However, today I am more than sure that linking a table to Ms Access can make things a lot easier. The possibility of copy and pasting data from an Excel spreadsheet, working with search and replace functions or filtering data without a query, in many cases can be a faster solution.

I would like to know your experience on the matter. Please, do use the comments section below and let me know what you think.