Sql-server – SQL Server 2014 Not recognizing 64 bit drivers for Office

I had a similar problem with my installation of VS 2012 (Premium). I installed VS 2012, at the custom installation options screen I chose the option for SSDT, but when actually using it, it was really absent (same error as yours).

My idea is that the default installer only adds the template for the project, but not SSDT itself. I also had to install SSDT separately (in this case only for VS 2012) and it worked (SSDT for VS 2010 is necessary only if you want to open SQL projects in VS 2010, but I guess you know that already).

Since Access can successfully connect to the SQL Server and execute queries against it, the connection string the Access is using must be correct. The connection made through ADO, on the other hand is relying on CurrentProject.Connection property, which we don't know what it is, and fails unexpectedly. Inside AccessDataBaseEngine_X64.exe seems to have no ODBC drivers, and Access works with or without AccessDataBaseEngine_X64.exe installed. So the DSN the Access is using didn't change, therefore the problem must be the connection string used in ADO.

So if you use the same connection string as that of Access, the problem should go away.

As for why it started behaving erratically all of sudden I can't fully comment on that. But what Microsoft states about the CurrentProject.Connection property might give you a clue (the relevant section in bold).

Use the Connection property to refer to the Connection object of the current Microsoft Access project (.adp) or Access database object. You can use the Connection property to call methods on the Connection object such as BeginTrans and CommitTrans.

Note: The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database. Since the Connection property is the main Shape provider connection, the following information is necessary when using this property.

1. MSDataShape uses Recordset.CursorLocation = adUseClient. Do not set CursorLocation prior to assigning a recordset to CurrentProject.Connect.
2. MSDataShape uses Recordset.CursorType = adOpenStatic. Do not set CursorType prior to assinging a recordset to CurrentProject.Connection.
3. MSDataShape accepts Recordset.LockType = adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly (default). If set to adLockPessimistic, it is changed to adLockOptimistic.
4. The shape connection does not support the all ADOX operation, specifically the Columns.Properties collection is not supported.
5. In order to ensure that a shape connection will work correctly, the Command.CommandType must be set to adCmdTable.

Bonus:

1. How to know what files were involved with AccessDatabaseEngine_x64.exe? (I was curious!)

   Use Orca