ERP Software Logo

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

Elizabeth Moris, KTL Solutions

Creating Microsoft Excel Connections to SQL databases


    Email | Print

    Microsoft Excel can be a powerful tool to evaluate SQL data.  In Excel, a connection can be created to directly link to a particular database filtered according to your requirements.   This connection must be validated by a SQL login to ensure the contents of your databases remain secure. The connection can be refreshed to update the content from the SQL database.

    Create an Excel Connection to a SQL database

    To Create an Excel Connection:

    • Open Microsoft Excel
    • Select the Data tab
    • Click From other sources
    • Select From Data Connection Wizard
    • Select Microsoft SQL Server
    • Click Next
    • Enter the SQL Server Name
    • Select credentials to use
    • Click Next
    • Select the database and primary table you would like to use
    • Click Next
    • Enter File Name
    • Enter Friendly Name
    • Click Finish
    • Enter cell to start the data connection
    • Select how to display the data (Table, Pivot Table, Pivot Chart and Table)
    • Click Ok
    • Enter your credentials
    • Click Ok

    Refresh an Excel Connection to a SQL database

    To refresh the Excel Connection:

    • Select the Data tab
    • Select Refresh All

     

    Update Excel Connection(s) to a SQL database

    Update the connection:

    • Update an Excel Connection:
    • Select the Data tab
    • Select Connections
    • Select the Connection
    • Select the properties button
    • Modify the settings required
    • Select the Definition tab
    • Modify the Connection string to connect to a different database
    • Modify the Command Type to SQL for Queries or Table for a table
    • Modify the Command text to enter a SQL query to select particular data

    By: KTL Solutions | A Technology Consulting Firm

    Ask This Expert a Question / Leave a Comment

     

     
     
    Show Buttons
    Hide Buttons