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.
Feb 27 '08
17  6547 
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:\instantclien t 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:\instantclien t 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.CLIENTNAM E.com)(PORT = ####))
)
(CONNECT_DATA =
(SERVICE_NAME = XXXXXXX.CLIENTN AME.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:\instantclien t 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:\instantclien t 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.CLIENTNAM E.com)(PORT = ####))
)
(CONNECT_DATA =
(SERVICE_NAME = XXXXXXX.CLIENTN AME.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:\instantclien t\oracle_home\n etwork\admin\tn snames.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
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Access Newbie |
last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to
append all the data from a local table to a remote table.
I'm using the SQL query editor in MS Access to create the query (I
don't want to do this in VB code).
I've set up the ODBC connection string in the query properties, which
has been tested and works:
ODBC;DSN=dev001 student99;Description=dev001...
|
by: Abhi |
last post by:
FYI:
This message is for the benefit of MS Access Community. I found that
this prblem has been encounterd by many but there is hardly any place
where a complete solution is posted. So I thought I should give back
to the community by posting our findings.
Thanks you all for all your help till now by posting problems and
their solutions.
~Abhijit
|
by: stueyh |
last post by:
When attempting to connect to an Access 2000 DB using ADO.Net from within
ASP.Net running under an impersonated local user account receiving the
following error.
ERROR Disk or network
error. ERROR Driver's
SQLSetConnectAttr failed ERROR Disk or network error
Oracle ODBC connection works fine under the same user so assuming this is
something to do with either Access specific registry permissions, or NTFS
|
by: chadlupkes |
last post by:
I've been trying to create a simple (I hope) PHP application to send
emails to a membership list. I have yet to successfully connect to my
database to pull the names and email addresses. I'm running XP Pro,
PHP Version 4.3.2, Access 2000. I have created an ODBC System DNS
connection to the database file I want to access, And I get this error:
Warning: SQL error: , SQL state 00000 in SQLConnect in connecttodb.php
on line 2
Whoops!...
|
by: SKB |
last post by:
Hi,
I am absolutely new to this area. I am getting the following difficulty
:
Access denied for user 'ODBC'@'localhost' (using password: NO)
when I try the mysql command from within the C:\Program
Files\MySQL\MySQL Server 5.0\bin on the command window.
Please note that the MySQL is runnging as I have tried the command NET
START MySQL and it gives the following output:
The requested service has already been started.
More help is...
| | |
by: bazzer |
last post by:
hey,
im trying to access a microsoft access database from an ASP.NET web
application in visual basic 2003.NET. i get the following error when i
try running it:
Server Error in '/CinemaBookingSystem' Application.
--------------------------------------------------------------------------------
ERROR General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process
|
by: harsha.patibandla |
last post by:
We have a webserver, Apache, hosted on Linux and we use php as the
scripting language. Now, I am developing a form which will fill up a
Microsoft Access database (on a Windows machine). For this one needs to
use an ODBC connection. Now php has default ODBC support
(http://us2.php.net/odbc ), but I do not think this supports MS Access.
My question is what driver manager/drivers do i need to install for
this:
one option was unixODBC, but...
|
by: Jayhawk |
last post by:
Hello,
I am trying to use a MS Access front end application to link to data
tables stored in MySQL Server. I am running MySQL 5.0 Community
Server, MySQL Connector/ODBC 3.51, and MS Access 2003. I have set up
a test database and table in MySQL, but the problem comes when trying
to link to this table through Access. From the Link Tables menu
option, I choose ODBC Databases, but no "Select Data Source" dialogue
window will pop up. All...
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables...
|
by: Suresh P |
last post by:
Hi All,
I tried to access the mysql database in ODBC using ip address and
username/password.
It returns, "cannot connect to MySQL server on IP ADDRESS(10060)".
This could be related to Firewall on the server.
Is there anyway to access MySQL database using ODBC without opening
port 3306?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
