Query an Access database from SQL Server

You can query an Access database using SQL Server management studio by doing the following:

1) In SQL Server Management Studio, create a "linked server" to the Access database. This is a one time setup to establish the connection to Access.

2) To query a table or view in the "linked server", you normally have to use this syntax:

Select * from LinkedServerName.catalogname.schema.tablename

This syntax is normally required when linking to another SQL Database. However, SQL doesn't seem to create a catalog name or schema name when you create a linked server to an Access database.

So to query a linked Access database you just use this:

Select * from LinkedServerName...Tablename