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