Using ODBC in Access

[bplantes]

I have loved the quick responses that I have received using thescripts.com for a couple of my prior questions. I thought I would ask the question here as I haven't been able to get a good result from my Google searches thus far.

I am trying to use Access to read a table from another Access database and read 2 tables from an Oracle database. With these tables I will be performing some "SELECT" queries... I have no need to make any updates or adds in any of the outside tables.

I have not been able to find a good online tutorial/walkthrough for how to do this within Access. I have found some tutorials on setting up the ODBC connection in the "Administra tive Tools->Data Sources (ODBC)" menu, but am not sure what to do or how to code to use the connection within Access after that. I believe that I have to define the connection, open the connection, and grab the tables within the Access and Oracle databases that I want to use... but am for sure leaving out some steps.

Please point me in the right direction. I appreciate the wisdom from the more experienced. Thanks.

[Zwoker]

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...

connStrForDB = "Provider=MSDASQL.1;Password=" & pPassword & ";Persist Security Info=False;User ID=" & pUsercode & ";Data Source=XXXX"
Set connForDB = New ADODB.Connection
connForDB.ConnectionString = connStrForDB
connForDB.CommandTimeout = 0
connForDB.CursorLocation = adUseClient
connForDB.Open
 
SQLstring = "Select * From " & SchemaName & ".CUST;"
CustRst.Open SQLstring, connForDB, adOpenStatic, adLockReadOnly

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. :)

[NeoPa]

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.

[bplantes]

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!

[NeoPa]

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.

[bplantes]

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 OracleConnectio n 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!

[NeoPa]

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).

[Stewart Ross]

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 OracleConnectio n 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

[bplantes]

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.

[Stewart Ross]

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.

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\system32\WBEM;...; C:\Program Files\Intel\DMIX; c:\instantclient;c:\Program Files\Microsoft SQL Server\90\Tools\binn\

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