ADO Connection
Microsoft ActiveX Data Objects (ADO) is a data access technology that enables you to connect to a variety of data sources through OLE DB. OLE DB is an alternative interface to ODBC or JDBC; it provides uniform access to data in a COM (Component Object Model) environment. ADO is a precursor of the newer ADO.NET and is still one of the possible ways to connect to Microsoft native databases such as Microsoft Access or SQL Server, although you can also use it for other data sources.
Importantly, you can choose between multiple ADO providers, and some of them must be downloaded and installed on your workstation before you can use them. For example, for connecting to SQL Server, the following ADO providers are available:
•Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
•Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
•SQL Server Native Client (SQLNCLI)
From the providers listed above, the recommended one is MSOLEDBSQL; you can download it from https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15. Note that it must match the platform of DatabaseSpy (32-bit or 64-bit). The SQLOLEDB and SQLNCLI providers are considered deprecated and thus are not recommended.
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is known to have issues with parameter binding of complex queries like Common Table Expressions (CTE) and nested SELECT statements. |
To set up an ADO connection:
1.Start the database connection wizard.
2.Click ADO Connections.
3.Click Build.
4.Select the data provider through which you want to connect. The table below lists a few common scenarios.
To connect to this database... | Use this provider... |
|---|---|
Microsoft Access | •Microsoft Office Access Database Engine OLE DB Provider (recommended) •Microsoft Jet OLE DB Provider
If the Microsoft Office Access Database Engine OLE DB Provider is not available in the list, make sure that you have installed either Microsoft Access or the Microsoft Access Database Engine Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=54920) on your computer. |
SQL Server | •Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) - this is the recommended OLE DB provider. In order for this provider to appear in the list, it must be downloaded from https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15 and installed. •Microsoft OLE DB Provider for SQL Server (OLEDBSQL) •SQL Server Native Client (SQLNCLI) |
Other database | Select the provider applicable to your database.
If an OLE DB provider to your database is not available, install the required driver from the database vendor (see Database Drivers Overview). Alternatively, set up an ADO.NET, ODBC, or JDBC connection.
If the operating system has an ODBC driver to the required database, you could also use the Microsoft OLE DB Provider for ODBC Drivers, or preferably opt for an ODBC connection. |
5.Having selected the provider of choice, click Next and complete the wizard.
The subsequent wizard steps are specific to the provider you chose. For SQL Server, you will need to provide or select the host name of the database server, the authentication method, the database name, as well as the database username and password. For an example, see Connecting to Microsoft SQL Server (ADO). For Microsoft Access, you will be asked to browse for or provide the path to the database file. For an example, see Connecting to Microsoft Access (ADO).
The complete list of initialization properties (connection parameters) is available in the All tab of the connection dialog box—these properties vary depending on the chosen provider and may need to be set explicitly in order for the connection to be possible. The following sections provide guidance on configuring the basic initialization properties for Microsoft Access and SQL Server databases: