19

I want to connect to an MS Access 2003 DB from SQL Management Studio 2005 so I can run queries on the Access DB from SQL Management Studio.

John Oxley
  • 727
  • 4
  • 12
  • 23

2 Answers2

11

There are two ways to do this:

  1. Add the MDB as a linked server

    http://msdn.microsoft.com/en-us/library/aa213778%28SQL.80%29.aspx

  2. Enable Ad Hoc Queries in SQL Server and use OpenDataSource

    http://riteshshah.wordpress.com/2009/03/02/read-xls-or-mdb-file-with-opendatasource-sql-server-2005/

    http://support.microsoft.com/default.aspx?kbid=266008

Nissan Fan
  • 234
  • 3
  • 10
  • The link for adding the MDB as linked server is broken. I found [this one](https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-access-data-source-sql-server-import-and-export-wizard?view=sql-server-2017) as I'm trying the same with SSMS 2017 – Stoppal Jul 19 '18 at 05:53
4

You can also import the data into an existing database, via Tasks > Import Data. You can then select into which tables (new if desired) and how to map the columns

jeroenk
  • 140
  • 4