Using Ms Access to Update Reporting Services Database

It is strange to use legacy Ms Access to update table in reporting services database. But sometimes it happens in market. This example is because the reporting services database was first built by Ms Access as local database, then developed into SQL Server database. During the transit period the updating does still using Ms Access.

A bunch of independent queries as well as internally called queries organized into Macros. When this macro is executed, a huge reporting services table had been generated within the Ms Access.

Next step is to export this table into SQL Server by using Import & Export Data (32-bit) of SQL Server 2008. Before that, make sure the existing table in SQL Server is being changed name, else the new records will be simply append to the end of original table.

In Import & Export Data (32-bit) Date source is Microsoft Access, file name is the Access file. Leave User name and Password blank. Next, in Destination select SQL Server Native Client 10.0 and make a right selection in Server name. In Authentication tick Use Windows Authentication before make a right selection in Database. Next, select Copy data from one or more tables or views. The rest steps are easy.