Ideally, all the reports should be generated from the system itself. However, we are living in a world with limited resources. The IT Department always has different priorities from you. Your urgent request may turn out to be the lowest priority of your IT Department. If you don’t know how to do some programming yourself, you are at the mercy of the IT Department. Sometimes you have to wait for “years’, not weeks or months to get something out of the IT Department. That is certainly frustrating. In addition, you and your co-workers may have to spend after hours and weekends to try to manually get the reports out to the clients. These unfortunate events happened to me far too often.
Some people might argue that they can request IT department to download data on a regular basis. There are certain advantages of connecting to the Oracle server compared with that:
- The data you get is “real time”. It is critical to some of the time sensitive reports that are requested by the clients or the management.
- If you need to generate some ad hoc reports, you can always link to a new table, or add or modify a query to get the data you desire.
- The data you download can be further manipulated in Microsoft Access before generating the reports. You can write queries, reports, macros, and visual basic modules to filter, aggregate, sort, and do complex calculations.
Once you get the ODBC connection and if you know how to do some programming, the sky is the limit what you can do about the data. The feeling that you are in control is wonderful.
Establishment of ODBC connection
I cannot post some of the actual screen printings here on this website because it will involve company secret. I can only point out some of the steps that you can do to establish an ODBC connection with Oracle database.
To establish the connection to the Oracle Server, you may need the assistance from the Oracle DBA of the IT department. You need to get from them the connection strings that will be included into a file called TNSNAME.ora. That is a string of commands telling your person computer how to connect to the Oracle Server. The location of TNSNAME.ora should be in this directory if you use Windows operating system : //home/oracle/oracle/product/
Afterwards, you need to install the Oracle Driver. Please make sure the driver is the same version of the one on the Oracle Server. For example, if the version of the Oracle database is 11g, you need to install the Oracle Driver of 11g.
Microsoft ‘s ODBC driver may not be good enough to pull data from Oracle database. There are a few data types in Oracle database but we normally used four types of them: Character, Numeric, Date, and LOB. The LOB data type stores and manipulates large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format. Microsoft ‘s ODBC driver has no problem to pull Character, Numeric, and Date data types, but it is not capable to pull LOB data type. Microsoft might have improved its ODBC drive in later versions, but as far as I know, Microsoft’s ODBC does not work on pulling LOB data. Therefore, we have been using a program called “DataDirect” to pull the data out. So we need to install DataDirect after we install the Oracle Driver. When we pick the ODBC set up on System DSN , we picked DataDirect rather than the Microsoft ODBC driver.
Type in “ODBC” in the search box of the top right corner to search. Click the “Set up data sources (ODBC)”. When you see the pop-up screen of “Microsoft ODBC Data Source Administrator”, you can “Add” a User DSN or System DSN (no illustration because of company proprietary information, same as below). If you have installed a “DataDirect” driver, you should be able to see that as a choice for User DSN or System DSN. Choose it and fill out the required information such as a new DSN name (e.g. Marketing, HR, Sales, etc.), user name, password, database connection, etc. If you are in doubt, check with your DBA.
After you put in all the necessary information, you can click the “Test Connection” button to test the connection. If everything works, you should be able to see a pop-up screen to say “Connection successful”.
To set up the ODBC connection, press the Start button and then the Control Panel.
When you finish creating the ODBC connection, you can create “linked” tables in Microsoft Access. Open Microsoft Access, click the “External Data” tab on the ribbon and click “More” button to see the additional choices, choose “ODBC Database”.
Choose the option of “Link to the data source by creating a linked table”. Then put in the DSN name. The system will probably ask you for user name and password. Once everything is confirmed, you create a linked table. You need to do it multiple times if you have several tables to link to.
Please note that the Oracle tables that your Microsoft Access database is linking to should be images or subsets of the Oracle tables. It means that the users are not touching the true table but just a mirror image of a table. In addition, the users should only have the “read-only” right. It is critical to maintain the data integrity in the Oracle database by only giving out read-only access. Only in rare or exceptional cases that users are granted “write” privilege.
