Turing's Man Blog

Part 12. Using MS Access 2010 as a frontend to Oracle Database 11gR2 backend via ODBC

Bookmark and Share

We've already finished all the basic installation and configuration tasks on both: server side, where we have a working Oracle Database 11gR2 on Solaris 11.1; and client side, where we installed MS Windows 8 and Oracle Database 11gR2 Client software. This means we're ready to install – any – Microsoft Office package, as long as it is equipped with MS Access. In my case I installed MS Office 2010 Professional, which could be downloaded for trial purposes at the time I was preparing materials for this tutorial. So, let's go with the ODBC (Open DataBase Connectivity) part.

 

First of all, small assumption – MS Office 2010 Professional installation is very easy and we will skip this process. Therefore, you should install MS Office on your own here. Moreover, one can use any modern version of MS Office – the differences between MS Access versions available in MS Office 2007, 2010 and (even) 2013 are not big enough. You'll easily get the idea behind, so this tutorial is rather quite universal.

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 1

 

Secondly, we've installed Oracle Database 11gR2 Client on MS Windows 8 (see – part 9). You can use any other MS Windows version here, as long as Oracle Database 11gR2 Client is installed and properly configured to connect with the Oracle Database server. So, I can recommend to at least refer to part 10 and part 11 of this article. Especially, one should refer to the part 11 to be more than sure that his tnsnames.ora file is correctly composed – which enables Oracle Database 11gR2 Client to use TNS interface for connections with the Oracle Database server. This is crucial, because available ODBC drivers for Oracle Database require TNS interface.

Thirdly, there are two available ODBC drivers for Oracle Database. One is provided with MS Windows and was developed by Microsoft. Personally, I don't have too much experience with this driver. I only remember that once upon a time I had some stability and performance issues with Microsoft-made ODBC driver for Oracle. These were gone when I decided to go with the original, Oracle-made ODBC driver. So, I don't want to argue which driver is better (and for what), but my opinion is simple: as long as Oracle provides their own ODBC drivers for their Oracle Database product, I'm not interested in using any – third-party in this case – drivers. Also, I have read on OTN that MS ODBC driver for Oracle uses an old Oracle 7 version of the OCI (Oracle Call Interface) API, which has been obsolete for about 15 years. This can easily explain the stability and performance issues we can have with newer versions of the Oracle client (or the database).

Fourthly, MS Access 2010 (and in fact all versions since MS Access 97) is not "database" – in the common understanding of this term. MS Access is – so called – RDBMS (Relational DataBase Management System), which helps not only in storing data, but also in database development, frontend applications development (forms to control the data, reports, sophisticated queries), data analysis and – more generally – widely understood data manipulation. This tool can be divided into two parts:

  • Microsoft ACE database engine (formerly known as MS JET database engine) – this is database engine, which is very good for smaller databases. However, it is not aimed to be competition for other database engines, like innoDB or MyISAM from Oracle MySQL, Oracle Database engine, PostgreSQL, MS SQL Server etc. MS ACE is compact and flexible, but has some significant limitations. However, we can easily migrate data stored by MS Access in its own ACE database engine to – practically – any other database, as long as appropriate ODBC drivers are available. The obvious choice seems to be MS SQL Server, which in fact is very commonly used as a backend database for MS Access applications (frontends), however, we should value MS Access for its connectivity features. This is really very open and quite scalable technology – we will see an example here, because our backend database for MS Access is Oracle Database 11gR2 (in fact – previously installed on Solaris 11.1 server).
  • Microsoft Access database development and data manipulation tools – here we have a possibility to create forms for data manipulation, reports, queries (SQL statements), macros (for automation purposes) and VBA (Visual Basic for Applications) code (stored in modules – this gives MS Access a real power in hands of experienced developer). We can also use additional technologies like ActiveX components, which give interesting options when we have to extend the basic features of our applications (please note, we can write our own ActiveX components in – for example – C++ or use thousands of components provided by third-parties). Also, DLL (Dynamically Linked Libraries) can be used from the VBA level – this gives even more power, as we can write our own libraries in almost any other language available for MS Windows platform, to enhance the basic MS Access application functionality. What else? Since MS Access 2007 we can integrate our applications with MS Sharepoint (previously, in MS Access 2003 there were – so called – Access Data Pages, ADPs, which allowed us to create very simple web applications). Additionally, MS Access gives us lots of options when it comes to data export or import between many data sources (not only database tables). All these features can be used with or without help of – so called – wizards (prepared for non-professionals).

Finally, I don't have to cover the fact, that I like MS Access very much. However, I was skeptical about this tool – as many other IT guys – some years ago. Therefore, please don't get me wrong. I appreciate all the strengths of MS Access, but I know its weaknesses, too (let's leave them for dedicated article). If someone still cannot understand what the MS Access is, maybe we can use a kind of generalized comparison: "in the world of Microsoft technologies, MS Access is like Oracle Forms, integrated with Oracle Reports, oriented for smaller database projects, simplified in some areas (VBA, Sharepoint integration, many wizards etc.), enhanced with its own database engine (JET or ACE) and an option to connect with any ODBC-ready database backend (including Oracle Database and Oracle MySQL), together with very easy data import/export capabilities between many data sources". Yes, I know! That's said like in advertisement, but I hope you get my idea.

Since we clarified all the initial assumptions and doubts, we can start with the short experiment – we will create ODBC connection to the Oracle Database 11gR2 and use it from MS Access to:

  • Link MS Access 2010 (frontend) with the Oracle backend database – we will use hr schema for this case. This scenario is quite common when we have to work with the data from external database or we want to quickly develop frontend application (for example – for the prototype purposes).
  • Import all hr schema tables from Oracle to the MS Access – to process them locally with the usage of MS ACE engine. This scenario is quite popular among data analysts, who sometimes have to import a part of data from external database for local processing.

So, here it goes.

 

Scenario 1. Link MS Access 2010 (frontend) with the Oracle backend database

Let's start MS Access – in my case I will use this highly-sophisticated MS Windows 8 metro-style Start menu (smartphone-like):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 2

 

Once MS Access is started, we should select "Blank database", provide a filename (I propose: "HR_Oracle_ODBC_linked") for our database and click "Create" button:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 3

 

A new database will be created (we can see there is one local table in edit mode):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 4

 

Then, we should go to the "External data" tab on the ribbon menu and select "ODBC database" button – "Get External Data – ODBC Database" window will be displayed with the two available options (we should select the second one):

  • Import the source data into a new table in the current database – this will be used in second scenario.
  • Link to the data source by creating a linked table – this is what we want to do. Select this option.

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 5

 

Then click "OK" button – there will be "Select data source" window displayed – in my case it is empty, as there are no data sources defined in the system. We should change the tab from "File data source" to "Machine data source" – like this:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 6

 

Click "New" button to create new data source for our Oracle database – a new windows will be displayed:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 7

 

Select "System Data Source (Applies to this machine only)" instead of "User Data Source (Applies to this machine only)" which is selected by default. Then click "Next" button and select Oracle in "OraClient11g_home1" – or similar name in your case – to choose Oracle-made ODBC driver (be aware – don't use Microsoft-made ODBC driver here):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 8

 

Once selected and double-checked it is in fact Oracle ODBC driver (which was installed during part 9 of this series together with the rest of Oracle Database 11gR2 Client software), we can proceed with "Next" button – a summary windows will be shown:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 9

 

Click "Finish" – you'll see "Oracle ODBC Driver Configuration" windows in which we have to provide correct information to establish a connection with the hr schema in our Oracle database server:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 10

 

Please note, we have to select "TNS Service Name" here – fortunately, we managed to configure TNS interface on the client side in part 11 and we can use TURINGSMAN service name to establish the connection. So, once we filled all the fields with connection information, we can use "Test Connection" button to check if really everything is correct – let's do it ("Oracle ODBC Driver Connect" windows will be displayed):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 11

 

On "Oracle ODBC Driver Connect" windows we have to provide a password for our user (hr user was unlocked and the password "turing$man" was set in part 11) – then, click "OK":

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 12

 

If the test connection was successful, please click "Ok" on the "Testing Connection" window. Then click "Ok" on the "Oracle ODBC Driver Configuration" – we will have "HR database" data source on the list in "Select Data Source" window:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 13

 

We should select his data source and click "Ok". When "Oracle ODBC Driver Connect" window will appear, provide the password ("turing$man") for hr user and click "Ok":

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 14

 

After a while, we will see "Link Tables" window, which presents all the tables in TURINGSMAN database. We don't want to use them all, so please select only the tables that belong to hr user (for the sake of convenience, check "Save password" checkbox):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 15

 

Then click "Ok". We can ignore security messages (this is only development environment – we want to focus on other things) by pressing "Save Password" button each time:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 16

 

When "Select Unique Record Identifier" window appeared, please select the column which will be treated as an indexed unique identifier (this is not required, but recommended from the performance point of view):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 17

 

After a while we will have all hr schema tables linked from MS Access to the Oracle Database via ODBC. We can see them all on the left "Tables" pane – double-click one of the linked tables (please note, there are 8 linked tables, which are stored in Oracle Database and 1 local table: Table1 – which is stored in MS Access/ACE database engine):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 18

 

All data in tables, which are stored on the Oracle database server (in our case Oracle Database 11gR2 on Solaris 11.1), is now available from our MS Access 2010. We can now take the advantage of all MS Access features to create forms, reports, SQL queries etc. – and even the whole forms based frontend application with some smart VBA coding. Fortunately, this task is quite a different story – we will come back to the application development with MS Access one day (but now, let's come back to our exercises with ODBC):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 19

  

MS Access 2010 to Oracle 11gR2 via ODBC, step 20

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 21

 

 

Scenario 2. Import all hr schema tables from Oracle to the MS Access

We will reuse "HR database" connection created before to import all tables from Oracle Database 11gR2 to our MS Access 2010. Let's imagine we have to work with these data locally, for example – during business trip. MS Access will help us a lot – maybe we will not have an access to the most recent data, but we can assume this is not an issue. Or, let's imagine we want to copy all data from Oracle database do MS Access and then, after some tweaking, we will export all tables to another database, like MS SQL Server? There are many possible options and justifications for this scenario. Let's begin.

First of all, we have to execute MS Access 2010 – then, we have to choose "Blank database", provide the file name (in my case: "HR_imported_from_Oracle") and click "Create" button:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 22

 

A new database will be created:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 23

 

Similarly to the previous example, click "External data" tab on the ribbon menu, then click "ODBC Database" button:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 24

 

However, this time we will select the first option (should be selected by default) – "Import the source data into a new table in the current database" – then we have to click "Ok". On the "Select Data Source" window we have to click the tab: "Machine Data Source" and select "HR_imported_from_Oracle" from the list). The data source is configured already, so we can click "Ok":

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 25

 

The procedure is the same here like in previous example – remember to provide the correct password („turing$man"):

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 26

 

Then select only the tables which belongs to the hr schema – these will be imported to our MS Access 2010 database and handled by MS ACE database engine locally:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 27

 

After import is ended there will be "Get External Data – ODBC Database" window displayed – we're not interested in saving import template at this moment, so click "Close" button:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 28

 

Here it is, we have all tables, which were previously stored on Oracle Database 11gR2 on our database server (running Solaris 11.1), imported to the local MS Access 2010 database. Since that moment, we can work with data locally – every database operation will be handled by built-in MS Access 2010 database engine – MS ACE. Please double-click on any available table which we can see in the left pane:

 

MS Access 2010 to Oracle 11gR2 via ODBC, step 29

 

We can now work with MS Access 2010 and do whatever we want – forms, reports, SQL queries, VBA, Sharepoint integration, data export/import, anything... The only difference is that all data is stored locally and handled by MS ACE, since we're not using linked tables anymore.

 

Bookmark and Share

Comments   

 
0 #20 AZ 2017-04-04 05:23
is there a possibility to connect Oracle Forms 10g with Access DB
I mean using the Form Builder 6i or 10g to connect to MS Access Database....

Please reply...
Quote
 
 
0 #19 Sal 2016-03-03 16:30
When using ODBC drivers to connect to Oracle database. Does MS Access create a separate DB connection for each table that I link or is it sharing the same conection?
Quote
 
 
0 #18 zia uddin 2016-02-09 16:33
:lol: thanks its very useful post.How can I delete that DSN? The Windows ODBC Data Source Administrator tool does not list these Oracle Machine DSNs which I created in MSAccess, and the Select Datasource window in MSAccess only lets you Select or Create New.
:D Thanks again.
Quote
 
 
0 #17 Michael Berry 2015-12-21 18:08
We need to have each person connect as their userid in oracle through access. It seems when I need to create a separate database for each user. I have tried to use the linked table manager to switch the tables from one user to another.. and that doesn't work. the user gets "ODBC--Connecti on to 'my table' failed. thoughts?
Quote
 
 
0 #16 Don 2015-12-15 22:12
Thanks, this was the exact sequence to answer my need and works nicely. In the process, messed up on one and created a DSN with an incorrect TNS. How can I delete that DSN? The Windows ODBC Data Source Administrator tool does not list these Oracle Machine DSNs which I created in MSAccess, and the Select Datasource window in MSAccess only lets you Select or Create New. Thanks again.
Quote
 
 
+1 #15 carl 2015-04-01 13:51
Thanks! I love using Access with Oracle 'cause it allows me to tie our PLM system together with other Access databases. We just moved to Access 2010 and I couldn't find where the @#$@#% Oracle ODBC driver got to. Your tutorial set me straight. Thanks again!
Quote
 
 
+1 #14 Lev 2015-03-06 18:19
Very good guide, thank you!
note1: to make TNS to work I had to copy it exactly from my server
note2: for access 2013 I had to chose User Data Source (not System) to make it work (otherwise it did not appear after creation)
Quote
 
 
0 #13 Daniel Wojtasiewicz 2014-10-10 18:07
How can i install Oracle client software? I have personal Oracle at home now.
Quote
 
 
-1 #12 Daniel Wojtasiewicz 2014-10-10 18:05
how do i install personal oracle client software? I have Personal Oracle 11g.
Quote
 
 
0 #11 Jane Hudson 2014-05-13 18:26
I don't even get the step about driver configuration. the dialog box doesn't even come up.
Quote
 

Add comment


Security code
Refresh