How do I select data from a Microsoft Access database in SQL Server?

Could anyone here give me a complete and thorough set of instructions for connecting to a Microsoft Access Database from开发者_如何学编程 SQL Server? The SQL Server I'm using is 2008--the 64-bit version. I was reading some forum post saying that the 64-bit version of SQL Server has trouble with JET, but even if that is the case is there some way to do it by setting up an ODBC data source?

Again, please be as thorough as possible. Here are some specific questions to help guide your answer: Do I need to enable Ad Hoc Queries? How do I set up the ODBC data source (assuming I have to do that)? Do I need to add the data source as a "linked server"? What sort of query can I write to access the data? For instance, can I use OPENDATASOURCE() or OPENROWSET()? What would that query look like?

Super big thanks, in advance!

ODBC provides a standard software API method for accessing both relational and non-relational DBMS (source: Wikipedia). In other words MS Access (Jet) specific trickery isn't going to be available.

How to set up ODBC datasource? Search on Google for 'set up odbc data source'

Should the MS Access database be configured as an SQL Server linked server? If you want to have stored procedures on the SQL Server accessing the Access database, then Yes - it must be linked. If you want to have both SQL Server and MS Access database data available to the same application, then you could access the two databases separately and avoid the strange link from SQL Server to MS Access.