Import Access Database into Origin

posted in: Worksheet | 0

Connecting to a database server and import dataset into an worksheet is one application feature in Origin. In this example, we will show you the procedure to retrieve tables from a Access database (accdb) file and import the specified columns into Origin. Download the attached .accdb file and put it under any location, e.g. D:\ .  

Note:  Both 32-bit and 64-bit Origin support importing Access database, provided the corresponding database engine is installed. If you have 32-bit MS Office in your PC, The 32 bit Database engine is automatically installed.  But you need to install 64-bit database engine to read Access database file in a 64-bit Origin. Refer to this page for how to install it without having to uninstall 32-bit Office. You need to restart Origin after the installation.

  1.  With a new workbook active, click Open SQL Editor toolbar button      on the Database Access toolbar to open up SQL Editor dialog.    
  2. In the dialog, select File:New… to open up Data Link Properties dialog. Under Provider tab, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Under Connection tab, within Data Source box, type D:\EugeneVideo.accdb. Click the Test Connection button to test if it’s connected. There is no password for it so check Blank password. Click OK twice.                                                                                                                                            
  3. In the left panel of SQL Editor dialog, the tables inside this database are listed.
  4. Paste the script below into the right panel.

    Note: You can double click table and column in table on left panel to add them into right panel.
  5. You can preview the result data on the bottom panel by clicking the Preview the result data button 
  6. Click Import data to worksheet button to execute the importing:                               

 

Note

  1. Icon on upper left corner of the workbook indicate the data is connected with Database. With the workbook active, you can click SQL Editor button again to modify the SQL Query and import again.
  2. Anytime you want to reimport data from database, click Import Data button on Database Access toolbar.
  3. In SQL Editor, you can save the Connection String (*.ODS), or both Connection String and Query String (*.ODQ) for future use.
  4. In 32 bit Origin, user can use Query Builder dialog by clicking   button within SQL Editor dialog or directly from Database Access toolbar to generate SQL Query in a GUI way:
    1. Drag and drop table Customers from right panel to the center Main tab.
    2. Check the checkboxes of columns you want to import. The select column names will be shown in the lower panel and from there you can manually add\change query conditions.
    3. E.g. we want to extract the rows with certain movie category.  Add = 4 to the Criteria column as shown below. Click OK.                  

Leave a Reply