APPLIES TO: yes-img-132013 no-img-162016 no-img-192019 no-img-seSubscription Edition no-img-sopSharePoint in Microsoft 365

You can use Excel to create OLE DB or ODBC connections and then share those connections with others. An OLE DB or ODBC connection is useful for connecting to data sources, such as Excel workbooks, legacy databases, or not-Microsoft databases. When you can upload a data connectedness to an Excel Services trusted data connection library in SharePoint Server 2022, the data connection is bachelor for yous and others to utilise to create information models, reports, scorecards, and dashboards. Depending on the detail data source that is used, people tin easily refresh data in Excel Services workbooks so that the virtually current information is displayed.

Before y'all begin

Before you begin this job, review the post-obit data most prerequisites:

  • Yous must exist using Excel 2022 and SharePoint Server 2022.

  • Excel Services must be configured to include a trusted data connections library and a trusted documents library. Ideally, you'll take a Business Intelligence Center site configured that you lot can use for your data connections and workbooks. For more information, see Configure a Business Intelligence Eye in SharePoint Server 2022.

  • You must take at least Contribute permissions assigned in SharePoint Server 2022.

  • You will need info from a SharePoint admin about how information authentication is configured for the databases your organisation uses. This can affect how you connect to different data sources.

  • You lot will need info about the data source that you want to utilise. In particular, yous must know the data source name, user proper noun, and countersign to connect to the data source.

  • If yous programme to publish workbooks that contain data models to SharePoint Server 2022, Excel Services must exist configured to support data models. For more than data, see Configure Excel Services in SharePoint Server 2022 Preview.

Step 1: Utilize Excel to create OLE DB or ODBC data connections

You can utilize Excel to create an OLE DB or ODBC connection. This enables you to connect to lots of different data sources. Examples include Microsoft SQL Server, Microsoft Access, and Oracle databases, although there are many others. You can create an OLE DB or ODBC data connectedness by using one of several methods:

  • Y'all can create a connectedness by using the Information Connection Wizard in Excel. (This is the recommended method to use.)

  • Y'all tin create a connexion past using a Microsoft Query Sorcerer in Excel.

Employ the following procedures to create OLE DB or ODBC data connections

To create a connexion by using the Information Connection Magician in Excel

  1. In Excel, on the Data tab, in the Get External Data group, select From Other Sources, and then select From Data Connexion Wizard.

    The Data Connection Sorcerer opens.

  2. In the What kind of data source practise y'all want to connect to? list, select a data source, and then select Next.

  3. Depending on the kind of data source that y'all selected, specify the necessary information for that data source.

    Tip

    The info that you specify varies according to the data source. For example, a Information Feed information source requires a link or location to a data feed and logon credentials, whereas an Excel workbook data source requires you to locate where the workbook is stored. For specific details regarding the connection that yous want to create, contact a database admin.

  4. On the Select Database and Tabular array folio, in the Select the database that contains the data that y'all want list, select the database that you want to utilise. Do not select Next yet.

  5. On the Select Database and Table folio, select the tabular array (or tables) that you want to use, and then select Next.

  6. On the Save Data Connexion File and Stop page, accept the post-obit steps:

  7. In the File Name box, go on or modify the default file proper name.

  8. In the Description box, enter a brief description for the data connectedness.

  9. In the Friendly Proper name box, keep the default proper noun, or enter a new proper noun for file.

  10. In the Search Keywords box, enter some words or phrases that will aid users find the data connexion when it is published to SharePoint Server 2022.

  11. Adjacent to Excel Services, select Authentication Settings….

  12. Select None, so select OK.

  13. To close the Relieve Data Connexion File and Finish page, select End.

  14. On the Import Information folio, select Only Create Connection, and then select OK.

  15. Repeat steps one-7 until you accept created all the data connections that you want.

If you lot cannot create the connexion that you want by using the Data Connection Wizard in Excel, yous tin can endeavour to create the connection by using a Microsoft Query sorcerer. This is useful for connecting to older databases. However, the connection that you create might not be supported in Excel Services. Contact a SharePoint admin to verify that the connection that you lot create is supported and so that people can refresh information in Excel Services files. The following procedure describes how to create a connection by using a Microsoft Query wizard.

To create a connection by using a Microsoft Query wizard in Excel

  1. In Excel, on the Data tab, in the Become External Data group, select From Other Sources, and then select From Microsoft Query.

    The Choose Data Source dialog appears.

  2. To specify the data source that you desire to utilise, select the Databases, Queries, or OLAP Cubes tab.

    Tip

    The info that you specify varies according to the data source. For example, if y'all select an Access database or an Excel file, you lot'll be prompted to navigate to the database or file on your computer. Or, if you choose to use a data source that is not listed, you'll exist prompted to create a data source then specify the kind of data source, location, and credentials to connect to it. Contact a database administrator for specific details regarding the connection that you want to create.

  3. On the Import Information folio, select Only Create Connexion, and and so select OK.

  4. Echo steps 1-3 until you have created all the information connections that you lot want.

By default, the data connection is saved in the My Data Sources binder in the Documents library on your computer.

Later data connections are created, the next step is to upload it to a data connection library. We recommend that you use a Business Intelligence Center site to store and manage business intelligence content, such as information connections.

To upload a data connection to SharePoint Server

  1. Open a web browser and navigate to the SharePoint site that contains the data connectedness library that yous want to apply.

    If yous are using a Business Intelligence Center, the website address (URL) resembles http://servername/sites/bicenter.

  2. To view the lists and libraries that are available for that site, select Site Contents.

  3. To open that library, select Data Connections.

  4. To open up the Add a document dialog, select New Item.

  5. To open the Cull File to Upload dialog, select Browse.

  6. Bold the information connection is saved in its default location, select Libraries, then select Documents, and and so double-click My Data Sources.

  7. Select the ODC file that you desire to upload, and so select Open.

  8. In the Add a document dialog, select OK.

    A Data Connections class opens.

  9. In the Information Connections form, specify the following settings:

  • In the Content Type department, ostend that Office Data Connectedness File is selected.

  • In the Name box, continue or change the filename of the ODC file.

  • In the Title box, keep or change the title of the ODC file.

  • In the Description box, enter a clarification of the data connexion.

  • In the Keywords box, enter one or more words or phrases. This info is used by search queries to discover the data connexion.

    Select Relieve.

    The data connection is added to the library. Repeat this process for each data connexion that you want to share.

See likewise

Concepts

Share data connections by using Excel and Excel Services (SharePoint Server 2022)

Data sources supported in Excel Services (SharePoint Server 2022)