In most organizations , Power Users use MS Access to query the data from other RDBMS (via ODBC) for their reporting needs.
For this approach , their learning curve is minimal ; the downside is that there are few compatibility issues ( with data types) ; Performance , and so on.
In my view , it is better that they should reporting tools that takes advantage of RDBMS in a native way.
Let us look at one of my compatibility issues . Not all datatypes translates nicely in MS-Access.
The datatypes 'datetime2' and 'bigint' are some of them.
Here is an example .
Created a table called 'DimDate' with one of the column as 'bigint'.
a) Here is the Structure of the table in SQL Server via SSMS ( SQL Server Management Studio)
b) Here is the data viewed in SQL Server via SSMS.
I have linked this table in MS Access .
c) Structure of the table in MS-Access
d) Data viewed in MS-Access
Changed the data to int; and re-linked the table in MS Access.
f) Structure of the table in SQL Server via SSMS
g) Structure of the table in MS-Access
h) Data viewed in MS-Access
Apart from the datatypes issues , there are performance issues especially , when you link a view in MS-Access , it offloads all the data in the underlying base table to MS-Access and performs the filter criteria at the client , which is not the best way to do queries.
Comments welcome.
