Compatibility Issues with MS Access and SQL Server

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.