OLE DB Connections
Connecting to databases using an OLE DB connection is very common due to the overhead of maintaining ODBC connections. To create a new connection, go to the Data tab on the Database Expert and open the Create New Connection folder. Within it find the OLE DB (ADO) folder and open it. This opens the OLE DB dialog box shown in Figure 10-5.
Figure 10-5. Selecting an OLE DB connection.
The Provider list shows you all the available OLE DB connection types. As you can see in the above figure, it is an extensive list. Scroll down to find the SQL Server provider (or whichever database type you are using) and select it. Then click the Finish button.
The OLE DB dialog box changes to prompt you for the server name, connection info, and database name. It is shown in Figure 10-6.
Figure 10-6. OLE DB connection info.
Entering all the information on this dialog box probably requires help from your tech support or the database administrator. You first need to enter the server name. If you weren’t given one, you can click the dropdown box and it will show you a list of available servers that it sees on the network. If you are running a copy of SQL Server on your computer, you can enter a server name of “(local)”. If you have a special User ID and Password for accessing SQL Server, enter that next. If your Windows login is used as your SQL Server login, then click the Integrated Security option in the middle. Lastly, enter the database name. Again, if you don’t know the exact database name, you can click the dropdown box to see a list of available databases on the server and select the one you need.
If you click the Database dropdown and don’t see any databases listed, then it means that you haven’t entered the proper connection information. To retrieve the list of databases, the dialog box tries to locate the database server you entered and send it your login credentials. If it succeeds, it retrieves the list of database names. If it fails, the database list stays empty and you have to double-check that you entered the correct information.
After clicking the Next button, you are presented with an Advanced Information page displaying technical details like encryption flag, service number, and other stuff that doesn’t have any relevance to us. There is nothing required of you to do on this screen except click the Finish button to close the dialog box.
Click the Finish button to save your changes and return to the data table. The new connection is now shown and the various tables are shown under it. Click on the Links tab to set relationships between the tables.
