MS Excel and ODBC/OLEDB for Database connectivity

Flat files like Excel workbooks have limitations when it comes to data storage. Though the new release of Excel has enough number of rows and columns to accommodate and analyze data, working with huge flat files in an Excel is a difficult job. Here comes the ‘database’, for our rescue.

A relational database management system (RDBMS) is an organized way of storing huge amounts of structured/columnar data. It also allows us to access the required records through SQL queries (SQL – Structured Query Language). MS Access, MS SQL server, Oracle, DB2 etc. are a few popular RDBMS.

This article is all about connecting Excel to MS Access & SQL Server using ODBC or OLEDB connection string. ODBC or OLEDB are the mediums to connect databases to any application.

 

Connecting to MS Access from Excel (2016)

The below procedure uses OLEDB connection string to establish the connection between the applications. To import data into an Excel workbook from MS access, there should be at least one table inside MS Access database. Save the created database in any location.

Step1: Open Excel Application -> Data tab -> Get Data -> From Database -> From MS Access Database.

Step2: Then select the database file located on your drive and then double click the “Table” you want to import.

Step3: You will get an option to edit the table in a “Power Query” editor as shown below.

Step4: Click on “Click & Load to” option on the top left section of the ribbon. Then you will get a pop up to select the destination cell as shown below.

Step5: Click ‘OK’ to get the data into the selected range of the excel worksheet. The data is dynamic, once MS Access table gets updated, same can be reflected into Excel workbook by clicking “Refresh All” option in the “Data Tab“.

Connecting to SQL Server Database from Excel

Connecting SQL has same type of data connectivity as MS Access, but the only difference is to enter the SQL server name in the field provided. Please refer below image.

Open Excel -> Data Tab -> Get Data -> From Database -> From SQL Server Database -> Enter SQL server name -> Enter Login Credentials -> click Next -> Select the database and table -> Click Next -> Select the cell/range to load the SQL table content -> Click OK

Connecting any database from Excel application follows the above procedure in establishing the connection. We can also use Visual Basic programming to automatically connect and refresh data from the database.

 

Check out our Tableau Pages

• • Tableau Expert San Francisco, CA
  • Tableau Expert San Jose, CA
  • Tableau Expert Los Angeles, CA
  • Tableau Expert San Diego, CA
  • Tableau Expert Sacramento, CA
  • Tableau Expert Dallas Fort Worth, TX
  • Tableau Expert Austin, TX
  • Tableau Expert San Antonio, TX
  • Tableau Expert Houston, TX
  • Tableau Expert Norwalk, CT
  • Tableau Expert Chicago, IL
  • Tableau Expert Atlanta, GA

• Tableau Expert Philadelphia, PA
• Tableau Expert Pittsburgh, PA
• Tableau Expert Phoenix, AZ
• Tableau Expert Boise, ID
• Tableau Expert New York, NY
• Tableau Expert Rochester, NY
• Tableau Expert Jersey City, NJ
• Tableau Expert Washington, DC
• Tableau Expert Charlotte, NC
• Tableau Expert Seattle, WA
• Tableau Expert Miami, FL
• Tableau Expert Boston, MA