Overview
The Jet Excel add-in can connect to a wide variety of data sources - including SQL databases.
Before you can create a Data Source connection, you must first download and install the Jet Excel add-in from the Jet download site.
Database Information
- Server Name or IP Address
- Authentication Type (Windows or Database)
- Database Name
- Version of SQL Server installed
How-To Video
This video will be a step-by-step overview of Setting up a Universal SQL Data Source.
Step by Step
-
To create a new Universal data source connection to your SQL database, start by selecting Data Source Settings from the Jet ribbon.
-
On the Data Source Settings window, click the Add button.
-
On the Add Data Source window, provide a name for your SQL data source and select Universal for the type. Click Add.
-
On the Connection tab of the Data Source Settings window, click the Configure... button.
This will display the Windows Data Link Properties window
-
On the Provider tab of the Data Link Properties window, you will want to choose the database driver that corresponds to the version of SQL Server hosting your database.
The choice Microsoft OLE DB Provider for SQL Server should work with most databases for SQL Server 2012 or earlier. Depending upon your version of SQL Server, you may experience better performance with one of the other choices.
Some database products include their own, specific database driver. You'll want to check with the software provider of your database to determine the correct driver to use.
-
Depending upon which driver you choose, the appearance of the Connection tab can vary but should look similar to this:
On the Connection tab, specify:
- The name of the server on which your database is located
- The logon information: Windows NT or the user name and password (be sure to check the Allow saving password box if you are using a user name and password)
- The name of the database to which this data source is connecting
On the Advanced tab...
It is a best practice to trim blank spaces from fixed-length text fields.
If you find that the password will not save when using more modern versions of the SQL database driver, you may need to ensure that that setting for Persist Security Info is set to True on the ALL tab:
-
You can then click the Test Connection button to verify your settings. You should receive a confirmation message:
-
Click OK and the Data Source Settings window should show that you are licensed for the data source.
If the text reads that You are not licensed for this data source, click that text to get additional information about the issue. -
You can then click the Test Connection button and should see the Test Connection Succeeded message. Click OK
-
If you would like this data source to be the default data source that the Jet Excel add-in will use, click the Set Default button on the Data Source Settings window.
Click OK and your Universal SQL data source is now ready for use with Jet Reports.
Comments