Using ODBC in Access

Hi,

I'm in the middle of doing something similar.

I started with all the tables I wanted from my Oracle database declared as external linked tables (using ODBC) in the table section of MS Access. That was simple, quick and easy to use, as you can treat them just as if they are a local table for reading (only) from.

But, I found that security constraints of having to give every user of the final MDE their own access to the Oracle database meant that it was better to switch to using a connection string with a unique (and encrypted) usercode/password.

Below is a summary of some of the code I use in my program. I'm using MS Access 2003. Anywhere you see an "XXXX" block I have removed a string value, to protect the innocent - You would need to replace it with the appropriate DSN value, or whatever, anyway... *grin*
And obviously some of the variables like pUsercode contain the decrypted usercode etc...


I have heavily simplied the code for readability. All my SQL strings are much more complicated, but there is no need to show them here.

If you have any questions just ask.
And anyone else that reads this that knows more about this stuff than me (I'm still learning) should feel free to offer suggestions to improve the way I do things. :)

Another intelligent and helpful response from Zwoker. Keep it up :)

I would see if you come across the same problems they have though, before abondonning the simpler way of using linked tables. ODBC links vary considerably, so you may not suffer the same restrictions.

If you do, then Zwoker has lit up the path ahead for you nicely.

Zwoker,

Thanks for the tip. I will try to use the link table solution and then try to use this solution if I run into problems.

I am developing a tool for a client (friend) that links to outside databases. They have given me a temporary Access database that is identical to a databases on their share drive (I am not on their network). Is there a way to set up a linked table in Access that looks for a file to link to on a share drive that I do not have access to, but will be available once the tool sits on my client's network? How about the Oracle tables that I do not have access to. Will I need the client to set up anything on their Oracle server for the linked table to work correctly?

Thanks for all the help!

With Access tables you simply put a similarly named database file in the same relative position on your PC (Use the Dos SUBST command to set up a drive of the right letter) which has tables (the ones you need to link to) with the same name and definition as the live database.

I appreciate the continued help. I am now having problems and cannot get much further without help.

I was granted access to have read access from an Oracle DB with a generic user name and password that will be used in the tool I am building in Access.

I DO NOT have Oracle set up on my laptop. I am wondering how to set up the ODBC connection using the Link Table option in Access without Oracle set up on the computer I am developing the tool on. I have tried a few things and cannot get any further:

1) Create New Link Table
2) Select ODBC Databases from the Dropdown of File Types
3) Receive a prompt for DSN
4) Select New...
5) Select "Microsoft ODBC for Oracle..."
6) Type OracleConnection for the new DSN and click Finish

I receive a error:

"The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corp and are part of the Oracle Version 7.3 (or greater) client software installation. You will be unable to use this driver until these components have been installed."

At this point I'm not sure of how to create the DSN that I need for Oracle. Is there a way for me to access these DBs if I have network access and a username and password for the read access?

I have been given the following:

1) Oracle version the tables are in: Oracle 9.2.0.6
2) TNS (not sure where to use this)
3) Username and password

Your help would be much appreciated! Thanks!

It looks like we're getting into specific Oracle territory here (as opposed to Access). I will post a linking thread for you in the Oracle forum asking for assistance over here (Setting up Oracle ODBC Link).

I appreciate the continued help. I am now having problems and cannot get much further without help.

I was granted access to have read access from an Oracle DB with a generic user name and password that will be used in the tool I am building in Access.

I DO NOT have Oracle set up on my laptop. I am wondering how to set up the ODBC connection using the Link Table option in Access without Oracle set up on the computer I am developing the tool on. I have tried a few things and cannot get any further:

1) Create New Link Table
2) Select ODBC Databases from the Dropdown of File Types
3) Receive a prompt for DSN
4) Select New...
5) Select "Microsoft ODBC for Oracle..."
6) Type OracleConnection for the new DSN and click Finish

I receive a error:

"The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corp and are part of the Oracle Version 7.3 (or greater) client software installation. You will be unable to use this driver until these components have been installed."

At this point I'm not sure of how to create the DSN that I need for Oracle. Is there a way for me to access these DBs if I have network access and a username and password for the read access?

I have been given the following:

1) Oracle version the tables are in: Oracle 9.2.0.6
2) TNS (not sure where to use this)
3) Username and password

Your help would be much appreciated! Thanks!

Hi. Until you get more specific help from the Oracle experts via Neo's post in that forum checkout Oracle InstantClient. It's an ODBC driver that does not require an installation of Oracle itself on the client PC. The following link takes you to the Oracle page describing the product:
http://www.oracle.com/technology/tec...ent/index.html

The TNS you refer to is a small script which identifies the source server and service name. You don't need to use a TNS script with Instantclient, but Instantclient does need to know the server address and the service name, as well as your username and password.

InstantClient is free, although you have to register with Oracle before downloading it. You need the whole of the Win 32 install set, which is in two sets of files. Installation is basic - done through a batch file from a command line - and setup a bit of a trial, but when you get the driver set up right it is then very easy to link the Oracle tables to your Access application.

-Stewart

I have downloaded Intant Client, but am having trouble understanding their installation notes, specifically step 3:

Installation Instructions

Installation Steps:

1. Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic or Basic Lite package.

2. Unzip the packages into a single directory such as "instantclient".

3. Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable. On Windows, PATH should be used.

4. Start your application and enjoy.

I'm not sure how to install the client on my computer.

I have downloaded Intant Client, but am having trouble understanding their installation notes, specifically step 3:

Installation Instructions

Installation Steps:

1. Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic or Basic Lite package.

2. Unzip the packages into a single directory such as "instantclient".

3. Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable. On Windows, PATH should be used.

4. Start your application and enjoy.

I'm not sure how to install the client on my computer.

Hi. You need to unzip all the Instantclient files into a single folder on your PC. Although it does not matter where this is, for convenience call the folder InstantClient and place it in the root of drive C: (as you have to add a reference to this folder to the PATH environment variable). To alter the PATH in XP go to Control Panel, select system, advanced, environment variables. In the list of system variables scroll to Path and select Edit. Add the folder to the Path statement. Mine is below as an example.
After unzipping all the files to the Instantclient folder in the root of C: install the driver. Select Start, Run, Cmd to start a DOS session, CD instantclient, then type ODBC_Install to run the installation batch file. Exit from the DOS session afterwards.

Finally, setup the ODBC driver from the Control Panel. Select Administrative Tools, Data Sources (ODBC), System DSN then Add. If the installation was correct you should see in the source list Oracle in InstantClient10_2. Once you get to this stage you are then in the hands of Instantclient itself for setting up the service-specific data.

Although this is quite an involved process it does not take long to do.

Hope this necessarily lengthy reply helps.

-Stewart

I really appreciate you walking me through that process!

Are the ODBC Drivers backwards compatible? Meaning, with the 10.2 driver work if I am working with Oracle9? Otherwise, is there an instant client for Oracle9?

Also, when I try to go through and set up the new ODBC Connection now, I get an error:

“The setup routines for the Oracle in instantclient10_2 ODBC Driver could not be loaded due to system error code 126″

I did a little research and found that I may be missing a couple of .dlls (mfc71.dll and msvcrt.dll) so I downloaded those and put them in my c:\instantclient directory. Still I receive that error. After a little more research, I see that I may need tnsnames.ora and sqlnet.ora files set up? If this is necessary, how should I go about setting these up? I created a new System Variable called TNS_ADMIN and pointed it to my c:\instantclient directory, but for that to work, I think I need the tnsnames.ora file? Not sure though.

Thanks.

***********************EDIT***********************

Ok... so I uninstalled the InstantClient and the Drivers and reinstalled all of them and it worked. So now I can get to the DNS setup screen for the Oracle ODBC Driver Configuration.

Where do I need to set up my TNS? I was given this:

XXX.CLIENTNAME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX.CLIENTNAME.com)(PORT = ####))
)
(CONNECT_DATA =
(SERVICE_NAME = XXXXXXX.CLIENTNAME.com)
)
)

What do I need to do with this? Thanks.

I really appreciate you walking me through that process!

Are the ODBC Drivers backwards compatible? Meaning, with the 10.2 driver work if I am working with Oracle9? Otherwise, is there an instant client for Oracle9?

Also, when I try to go through and set up the new ODBC Connection now, I get an error:

“The setup routines for the Oracle in instantclient10_2 ODBC Driver could not be loaded due to system error code 126″

I did a little research and found that I may be missing a couple of .dlls (mfc71.dll and msvcrt.dll) so I downloaded those and put them in my c:\instantclient directory. Still I receive that error. After a little more research, I see that I may need tnsnames.ora and sqlnet.ora files set up? If this is necessary, how should I go about setting these up? I created a new System Variable called TNS_ADMIN and pointed it to my c:\instantclient directory, but for that to work, I think I need the tnsnames.ora file? Not sure though.

Thanks.

Hi again. As far as I know Instant Client is compatible with a number of previous Oracle versions, including 9. Oracle's own forum posts should be able to confirm this.

Not sure about the DLL errors - not somethying that happened to me with either InstantClient 10.1 or 10.2.

You don't need the tnsnames.ora and sqlnet.ora files with Instant Client. I have used them in the past, located in a directory called Network or something - it was for a previous Oracle connection that was superseded by use of Instant Client. I have no tnsnames.ora file on my PC.

I'll attach a screenshot of my Instantclient folder so you can compare files.

-Stewart

I'm not sure if you saw my edits above... I reinstalled and fixed the problem I was having and can now get into the ODBC Configuration.

Now I am running into a problem with the TNS Service Name Dropdown. What do I need to use here? If you look at my edit to my last post, I have included what the DBA gave me for the TNS. I am not sure how to use this though. Thanks.

Ok... so I uninstalled the InstantClient and the Drivers and reinstalled all of them and it worked. So now I can get to the DNS setup screen for the Oracle ODBC Driver Configuration.

Where do I need to set up my TNS? I was given this:

XXX.CLIENTNAME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX.CLIENTNAME.com)(PORT = ####))
)
(CONNECT_DATA =
(SERVICE_NAME = XXXXXXX.CLIENTNAME.com)
)
)

Hi again. I'm not sure is the honest answer! I will attach a screenshot of my setup which you can compare (blacked out in places for security). The service name must match at the Oracle end, and the TNS name looks like your xxx.clientname.com. You may want to play around with these and click on the Test Connection button - it will stall for a while and timeout eventually if it can't connect.

-Stewart

I have played around with this a bit. This is the error that I receive every time (see attached screenshot... I have changed the name for security reasons, but have tried it with the actual name). If anyone can help resolve this issue, I would appreciate it very much. I feel like I am so close!

I have played around with this a bit. This is the error that I receive every time (see attached screenshot... I have changed the name for security reasons, but have tried it with the actual name). If anyone can help resolve this issue, I would appreciate it very much. I feel like I am so close!

Hi again. The error message is telling you that the TNS service name is not valid. It will be some form of network server address URL like //xxx.clientname.com/, perhaps qualified by a port address as per your TNSNames script. To check out that you have a valid server address you can always try to ping the server address (using a DOS session - Start, Run Cmd) and see what happens. If it is correct you should receive a response to the ping.
-Stewart

SUCCESS!!!!

I received a response from another messageboard that fixed this.

I had to create a tnsnames.ora in the directory: c:\instantclient\oracle_home\network\admin\tnsname s.ora.

I created the tnsnames.ora file with the TNS script that I posted earlier and once this was set up, I was able to connect. Stuart, thank you so much for getting me so far along. I feel like I have learned a lot in a short amount of time about how ODBC works and how to set it up.

Case closed.

SUCCESS!!!!
...
Case closed.

Well done!! The use of TNSnames.ora with InstantClient is one to remember for the future.
-Stewart