SQL Server: Open Recordset in SQL Server from MS Access

We can open tables from MA Access, Excel and other MS Office applications. Here we shall see how records can be retrieved from MS Access
Supposing we have test.mdb file on your desktop(C:\Users\mine\Desktop\test.mdb) and has a table by name Table1 containing 4 records in it.

SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Users\mine\Desktop\test.mdb"')...Table1;

You may obtain this error if you are running first time.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

You need to execute below two queries and execute above query. It works without any issue.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

If you need to open recordset from access 2007 and above versions you have to use this query

SELECT *  FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\Users\mine\Desktop\test.accdb"')...Table1;