Connecting to Databases

Connecting to Databases

This article describes database specific connection features.

See basics in the Connection Wizard topic.

Contents

Connecting to Microsoft SQL Server

The SaveToDB add-in for Microsoft Excel supports connecting to tables, views, and stored procedures of any Microsoft SQL Server version:

  • Microsoft Azure SQL Database
  • Microsoft SQL Server
  • Microsoft SQL Server Express LocalDB

Selecting Provider

Microsoft OLE DB Provider for SQL Server is pre-installed with Microsoft Windows and available by default.

This is the best provider to connect to Microsoft SQL Server and Microsoft Azure SQL Database.

To connect to file databases, for example using Microsoft SQL Server Express LocalDB, SQL Server Native Client 11.0 providers installed are required.

Connecting to Database

The server name format for Microsoft SQL Server:

<Server name or IP-address>[,<Port>][\<Instance name>]

The database field is available when the server and the logon credentials are specified.

The database list is populated with the databases available for a connection.

This is an example of a connection to the AzureDemo database in Microsoft Azure SQL Database:

This is an example of the connection to the local server on port 1433:

The 'localhost' should be used for the local server with the port specification as Microsoft Excel does not support the '.' notation with the port specified.

This is an example of the connection to the SQLEXPRESS named instance:

This is an example of the connection to a file database using Microsoft SQL Server Express LocalDB:

You may use the Browse button to select a file.

The SQL Server Native Client 11.0 provider is required to connect to a file database.

If the file database is already attached, it is available in the database list.

However, the better way is to use the file connection for a file database as the database can be detached anytime.
In this case, the file connection works while the database connection does not.

To top

Connecting to Oracle Database

SaveToDB Add-in for Microsoft Excel allows connecting to Oracle Database tables, views, and stored procedures.

Selecting Provider

The Oracle Database Client components are required to connect Oracle Database.
See https://www.oracle.com/technetwork/database/enterprise-edition/downloads/

Oracle OLE DB Provider is the best choice as it supports all Oracle Database features.

Connecting to Database

The Oracle Database server name format:

[<Server name or IP-address>[:<Port>]/]<Service name>

The service name only can be used if the service is configured in the tnsnames.ora file on the local machine.

Microsoft Excel does not support connections as SYSDBA or SYSOPER, but you may logon as SYSTEM.

This is an example of the connection to the service named Orcl:

This is an example of the connection to the service named Orcl at the server named Oracle on port 1521:

Multiple versions of 64-bit Oracle Database ODBC drivers have an error.

You may get the "Arithmetic operation resulted in an overflow" message during connection:

The solution is to update the 64-bit Oracle Database ODBC driver to the latest version.
Alternatively, you may use Oracle OLE DB Provider that has no such error.

The Oracle Database 10g providers have an error. They do not work with the program started from the path that contains the brackets.

So 32-bit Microsoft Office on 64-bit Windows installed in Program Files (x86) cannot connect to Oracle Database.
The following ORA-12154 error is appearing:

See Fixing Oracle Database 10g Connection Error ORA-12154 below.

Fixing Oracle Database 10g Connection Error ORA-12154

The Oracle Database 10g providers have an error. They do not work with the program started from the path that contains the brackets.

So, 32-bit Microsoft Office on 64-bit Windows installed in Program Files (x86) cannot connect to Oracle Database.

The best way is to update the Oracle client components as Oracle Database 11g providers do not have this issue.

Otherwise, the best way to fix the error is to create a symbolic link to the Microsoft Office installation directory and to create a new shortcut for Microsoft Excel.

To create the symbolic link, execute the following command from the Windows command line as Administrator:

mklink /D "C:\Program Files x86" "C:\Program Files (x86)"

Then create the shortcut for Microsoft Excel. For example, the path for Microsoft Excel 2010 is:

"C:\Program Files x86\Microsoft Office\Office14\EXCEL.EXE"

and the path for Microsoft Excel 2007 is:

"C:\Program Files x86\Microsoft Office\Office12\EXCEL.EXE"

The disadvantage of this solution is that you should open Microsoft Excel with the new shortcut to work with Oracle Database, but not with the click on an Excel workbook.

The alternative way is to reinstall Microsoft Office to a new path without brackets.

To delete the symbolic link, execute the following command from the Windows command line as Administrator:

rd "C:\Program Files x86"

To top

Connecting to DB2

SaveToDB Add-in for Microsoft Excel allows connecting to IBM DB2 tables, views, and stored procedures.

Selecting Provider

IBM OLE DB Provider for DB2 or Microsoft OLE DB Provider for DB2 installed is required to connect IBM DB2.

See IBM Data Server Client Packages at https://www.ibm.com/software/data/db2/linux-unix-windows/download.html
and Microsoft OLEDB Provider for DB2 at https://www.microsoft.com/en-us/download/details.aspx?id=16978.

The IBM provider is the best choice as the Microsoft provider requires the Microsoft SQL Server Enterprise Edition license.

Connecting to Database

The IBM DB2 server name format:

<Server name or IP-address>[:<Port>]

This is an example of the connection to the SAMPLE database at the server named DB2:

This is an example of the connection to the SAMPLE database at the server named DB2 on port 50000:

To top

Connecting to MySQL and MariaDB

SaveToDB Add-in for Microsoft Excel allows connecting to MySQL and MariaDB tables, views, and stored procedures.

MariaDB is completely compatible with MySQL. So you may use MySQL providers and drivers to connect both servers.

Selecting Provider

Two types of providers can be used to connect MySQL:

  • MySQL ODBC Driver.
  • MySQL Data Provider for .NET.

You may download MySQL ODBC drivers at https://dev.mysql.com/downloads/connector/odbc/.

Microsoft Excel supports MySQL ODBC drivers, and use of ODBC drivers is preferable.

A Unicode version of MySQL ODBC drivers completely supports national characters; ANSI drivers can have issues with national characters.

.NET MySQL Data Provider is integrated into the SaveToDB add-in and does not require additional installation.
However, working with MySQL is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

Connecting to Database

The MySQL server name format:

<Server name or IP-address>[;port=<Port>]

This is an example of the connection to the test database at the localhost:

This is an example of the connection to the test database at the localhost on port 3306:

To top

Connecting to PostgreSQL

SaveToDB Add-in for Microsoft Excel allows connecting to PostgreSQL tables, views, and functions.

Selecting Provider

Two types of providers can be used to connect PostgreSQL:

  • PostgreSQL ODBC Driver.
  • Npgsql .NET PostgreSQL Data Provider.

You may download PostgreSQL ODBC drivers at https://www.postgresql.org/ftp/odbc/versions/msi/.

Microsoft Excel supports PostgreSQL ODBC drivers but does not support connecting to functions that return ref cursors.

Npgsql .NET PostgreSQL Data Provider is integrated into the SaveToDB add-in and does not require additional installation.
However, working with PostgreSQL is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

Connecting to Database

The PostgreSQL server name format:

<Server name or IP-address>[;port=<Port>]

This is an example of the connection to the test database at the localhost:

This is an example of the connection to the test database at the localhost on port 5432:

To top

Connecting to NuoDB

SaveToDB Add-in for Microsoft Excel allows connecting to NuoDB tables, views, and stored procedures.

Selecting Provider

NuoDB ADO.NET Driver is integrated into the SaveToDB add-in and does not require additional installation.
Working with NuoDB is possible only with the SaveToDB add-in installed as Microsoft Excel does not support .NET providers.

You may download the latest NuoDB ADO.NET Driver at https://www.nuodb.com.

ODBC connections are not supported.

Connecting to Database

The NuoDB server name format:

<Server name or IP-address>[:<Port>]

This is an example of the connection to the test database at the localhost:

This is an example of the connection to the test database at the localhost on port 48004:

To top

Connecting to Microsoft SQL Server Compact

SaveToDB Add-in for Microsoft Excel allows connecting to Microsoft SQL Server Compact databases.

The SaveToDB add-in supports working with Microsoft SQL Server Compact itself as there are no OLEDB providers or ODBC drivers supported by Microsoft Excel.

Selecting Provider

Microsoft SQL Compact Data Provider supports working with Microsoft SQL Server Compact 3.5,
and Microsoft SQL Compact Data Provider 4.0 supports working with Microsoft SQL Server Compact 4.0.

Microsoft SQL Compact Data Provider 4.0 is embedded into SaveToDB and works by default.

You may download Microsoft SQL Server Compact 4.0 at https://www.microsoft.com/en-us/download/details.aspx?id=30709

and Microsoft SQL Server Compact 3.5 SP2 at https://www.microsoft.com/en-us/download/details.aspx?id=5783.

Connecting to Microsoft SQL Server Compact Database File

An existing database file must be specified to connect to Microsoft SQL Server Compact.

A password must be specified if the database file is encrypted.

If the database file located in the active workbook folder, then SaveToDB uses a file name only.
This feature allows moving a workbook and its database files to another folder or computer.

The Microsoft SQL Server Compact database can be created during connection.

This feature is useful for the Publish Wizard and the Application Installer.

Creating Microsoft SQL Server Compact Database

SaveToDB allows creating Microsoft SQL Server Compact databases including versions 3.5 and 4.0.

The database version is defined by the Microsoft SQL Server Compact provider selected at the first step.

Check Overwrite existing database file to recreate a database file.

You may specify a password to encrypt the database file.

Encryption modes:

  1. Platform Default: The algorithms used in this mode are AES128_SHA256, where AES128 is the encryption algorithm with 128-bit key and SHA256 is the hash algorithm with 256-bit key. This is the default encryption mode option on all SQL Server Compact 4.0 supported platforms.

  2. Engine Default: In this mode, the database is encrypted using AES256_SHA512, where AES256 is the encryption algorithm and SHA512 is the secure hash algorithm. The default key length is used to maintain backward compatibility with SQL Server Compact 3.5.

To top

Connecting to SQLite

Microsoft Excel supports working with SQLite via ODBC drivers.

The SaveToDB add-in also supports working with SQLite via built-in .NET SQLite Data Provider.
So you may work with SQLite without installing additional software.

Selecting Provider

SQLite Data Provider is embedded into SaveToDB and works by default.

You may download and install SQLite ODBC Driver at http://www.ch-werner.de/sqliteodbc/.

Connecting to SQLite Database File

An existing database file must be specified to connect to SQLite.

A free version of SQLite with a public domain license does not support encrypting.

If the database file located in the active workbook folder, then SaveToDB uses a file name only.
This feature allows moving a workbook and its database files to another folder or computer.

The SQLite database can be created during connection.

This feature is useful for the Publish Wizard and the Application Installer.

Creating SQLite Database

SaveToDB allows creating SQLite databases.

A free version of SQLite with a public domain license does not support encrypting.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.