Import Access Database into Origin

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.

1)  To install the engine, Download the file named AccessDatabaseEngine.exe (32bit) or  AccessDatabaseEngine_x64.exe (64bit) on to your local machine from:

http://www.microsoft.com/en-us/download/details.aspx?id=54920

Double click the file to install.

2) Restart Origin after installation.

Now we can set up the database connection.

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.

   Select Movies.m_Number, Movies.m_Category, Movies.m_Cost,
    LateCharges.lc_date, LateCharges.lc_paid
   From Movies Inner Join
    LateCharges On Movies.m_Number = LateCharges.lc_movie
   Where Movies.m_Category = 4
   

   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.