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. Follow the steps below to install:
1) Download the file named AccessDatabaseEngine_x64.exe on to your local machine from:
2) Before running the exe file, open the Registry Editor (type regedit in the Windows search box) and find the following registry key for the name mso.dll under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths
This registry key name would be present if Microsoft Office 64-bit was installed on the computer, in which case you just run the exe file to install the 64-bit Access Database Engine. Otherwise, please keep going with the following steps.
7) Restart Origin after installation.
Now we can set up the database connection.
- With a new workbook active, click Open SQL Editor toolbar button on the Database Access toolbar to open up SQL Editor dialog.
- 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.
- In the left panel of SQL Editor dialog, the tables inside this database are listed.
- Paste the script below into the right panel.
C++12345Select Movies.m_Number, Movies.m_Category, Movies.m_Cost,LateCharges.lc_date, LateCharges.lc_paidFrom Movies Inner JoinLateCharges On Movies.m_Number = LateCharges.lc_movieWhere Movies.m_Category = 4
- You can preview the result data on the bottom panel by clicking the Preview the result data button
- Click Import data to worksheet button to execute the importing:
- 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.
- Anytime you want to reimport data from database, click Import Data button on Database Access toolbar.
- In SQL Editor, you can save the Connection String (*.ODS), or both Connection String and Query String (*.ODQ) for future use.
- 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:
- Drag and drop table Customers from right panel to the center Main tab.
- 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.
- E.g. we want to extract the rows with certain movie category. Add = 4 to the Criteria column as shown below. Click OK.