Create an ODBC connection to SQL Server

To create an ODBC connection to an external database hosted on SQL Server,PostGres etc

Settings > Setup ODBC data sources

From here you have 3 choices

ODBC control panel

In early versions of MS Access the .DSN file had to be named the same as the database and placed in the same directory. This is no longer required but does make it simple to keep track of which .DSN file applies to each database file.

32 bit vs 64 bit Access databases

The majority of Office 2010 and 2016 installations are 32 bit, a 64 bit copy of MS Access cannot open a 32 bit .MDE or .ACCDE file, but you can recompile 32-bit .mde, .ade, and .accde files to make them 64-bit compatible.

As of the release of Office 2019, Microsoft now recommend installing the 64-bit version of Office unless you have a specific need which still requires the 32-bit version.

Examples:

X:\databases\contacts.accde
X:\databases\contacts.DSN

\\server64\dbshare\contacts.accde
\\server64\dbshare\contacts.DSN

The DSN file is a simple text file which can be edited with any text editor.

“A hidden connection is stronger than an obvious one” ~ Heraclitus of Ephesus

Related Links:

Upsize an Access database to SQL Server


Copyright © SS64.com 1999-2019
Some rights reserved