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

Question

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!

Cyril Gandon · Accepted Answer · 2011-07-28 09:42:37Z

3

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.

edited Jul 28, 2011 at 9:42

Cyril Gandon

17.1k15 gold badges84 silver badges124 bronze badges

answered Apr 5, 2011 at 15:01

Simen S

3,20520 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David-W-Fenton Over a year ago

This answer ignores the 64-bit issue, seems to me. I don't know if 64-bit SQL Server can use 32-bit Jet ODBC. If not, you'd need to install the 64-bit version of the ACE (successor to Jet, first introduced with A2007, and released first in a 64-bit version in A2010). This is available for download from MS.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related