Related Topics

Create link table in access from Oracle

Link tables/get external data issue
Hi, when I link ms access 2000 to an oracle database (using oracle 8.1.7 odbc drivers), for some reason ms access does not bring back a complete list of all the tables on the oracle database - in fact it actually brings back nothing. However, I know there are no problems with the ODBC link as I can use winsql, toad etc... to run SQLs and view all the tables on the oracle database. It seems to be an issue with access link tables front-end. Any ideas?


Access 2003 ODBC Question
I am supporting a legacy Access 2003 application with ODBC connections to several Oracle tables. My customer is upgrading to Oracle 11g which does not support ODBC. Is there any way I can create a connection string or otherwise link to an external Oracle table without ODBC?


Question on ODBC and SQL Differences (Access and Oracle)
I work at a research facility that uses Oracle (version 11) for the storage of our data. I have been trying to learn more about Access 2010. It seems (so far) much easier to use Access 2010 to design client 'frontends', than the old version of Oracle Forms we have been using.

I have two questions I'm hoping someone will know:

1. If I link an Access front end to the Oracle database using ODBC, then package the front end as an application (with install program, etc. as output by access 2010), will the app when installed maintain the link to the Oracle database? (or will I have to install the ODBC Oracle driver and relink the tables afterwards?).

2. For my queries, am I able to use Oracle's version of SQL since the SQL is being passed to the Oracle database for processing, or do I have to use Access's ANSI-92?


access oracle database from MS access
I have a set of forms in my MS access database and have linked it to oracle database with ODBC. With the link tables now available , could someone help me out as to how to write VBA code for the buttons so as to retrieve info from the oracle database ?

I would be happy if you could provide a sample VBA code for this querry i.e. when the student id is selected through drop down list and Search button is clicked it must go and look up in the table in oracle and must give all the students details


Oracle to MSACCESS
I have to perform the following task :

FORM->connects to database A -> uses DB link to query Database B table xyz.

Here Database is oracle 11g.

Earlier , I had created a direct connectivity to the table in database B using oracle client Microsoft ODBC Administrator. But client wants it to be without oracle client


ODBC link error
While using on OBDC connection to link to an ORACLE table I got an error stating "ODBC call failed - [MERANT][Oracle 8 driver] optional feature not implemented. (#0). When I remove all date fields from my query I can see the data when I add them back in all fields fill with #error.

What can I di to fix this. Is it something with the merant 3.60 oracle 8 driver I used to setupmy DNS entry?


Oracle ODBC 11g Driver
I'm having difficulties with an ODBC connection to an Oracle 11g database. I'm running Windows XP with Office 2010. I've installed the Oracle ODBC drivers and successfully created a DSN to the Oracle database. We've been able to ping the database, connect via SQL Plus and even link tables from within an Access 2002-2003 database. When I attempt to link from an Access 2007 database I get the error -

*****
ODBC--call failed.
Specified driver could not be loaded due to system error 127 (Oracle in OraClient11g_home1). (#160)
*****
I can't find anything to tell me what driver is not being loaded or what system error is occurring.


Oracle ODBC 11g Driver
I'm having difficulties with an ODBC connection to an Oracle 11g database. I'm running Windows XP with Office 2010. I've installed the Oracle ODBC drivers and successfully created a DSN to the Oracle database.

We've been able to ping the database, connect via SQL Plus and even link tables from within an Access 2002-2003 database. When I attempt to link from an Access 2007database I get the error -

*****
ODBC--call failed.

Specified driver could not be loaded due to system error 127 (Oracle in OraClient11g_home1). (#160)

*****

I can't find anything to tell me what driver is not being loaded or what system error is occurring.

I would appreciate any help as I really don't want to downgrade these databases in order to make the connections work.


Office 2010 ODBC linking question
Just got Ofc2010 & Windows7 machine. Had to setup ODBC connections to an Oracle DB again with a later version of Oracle. Had to re-LINK all my Access tables to the Oracle DB tables. All going well so far, but my queries using the linked tables are still not working.


It is possible to connect Access to Oracle by using DAO?
Currently, I'm learning how to open databases by using DAO objects and retrieve Recordsets.

I would like to know whether it is possible to connect a DAO object to Oracle. I have been googling for finding this, but so far, what I have found is all about ADO and not DAO. In the case, where it is possible to connect Access to Oracle by using DAO, could anyone kindly give me an indication (code, link to a tutorial, etc.)


Load csv data to Oracle through Access
My front end is Access and backend is oracle (moving to oracle). As of now iam using
Docmd.TransferText acImportDelim, , "Invoice_tab", filename, True to load csv data in Access database. I created Invoice_tab table in oracle and linked it in Access.
Now if I use the same Docmd it inserts the csv data into Oracle. It was working fine when I tried wit test data.
But now I tried inserting around 1 lac records. it is taking around 8 mins (to load into Oracle) where as the previous process took only 1 min (directly loading into ms-access database). Can anyone plz tell me is there any other way where I can laod csv data into oracle through access (front end should be access without writing a ctl file


Access update query not working
I created two versions of the below Access query to update a field based on matching two fields from the table the update field is in against two columns in another table. I am getting an errors in both instances that prevent me from running it. Any ideas?

Paul

update local
set RegistrationID = oracle.MEDIATOR_ID
from local, oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME;

update local
set RegistrationID =
(select oracle.MEDIATOR_ID from oracle
where local.FirstName = oracle.FIRST_NAME and local.LastName = oracle.LAST_NAME


load csv data into linked table
My front end is Access and backend is oracle (moving to oracle). As of now iam using
Docmd.TransferText acImportDelim, , "Invoice_tab", filename, True to load csv data in Access database. I created Invoice_tab table in oracle and linked it in Access.
Now if I use the same Docmd it inserts the csv data into Oracle (the linked table - Invoice_tab ). It was working fine when I tried wit test data.
But now I tried inserting around 1 lac records. it is taking around 8 mins (to load into linked table) where as the previous process took only 1 min (directly loading into ms-access database). Can anyone plz tell me is there any other way where I can laod csv data into oracle through access, any programming like sql loader or external tables in oracle is fine, but the end user uses only ms-access. plzzz help me, iam stuck here. or give some idea to proceed


Oracle Application Express Application Migration Workshop
The Oracle Application Express Application Migration Workshop is a feature of Oracle Application Express (Oracle APEX) that assists customers in migrating Microsoft Access applications to Oracle APEX. It works in harmony with the Oracle SQL Developer Migration Workbench to provide a total solution for your Access Migration requirements.


Link to an Oracle table using VBA code
I'd like to link to an Oracle table using VBA code. How can I do this?


Access 2007 Linked Oracle table, Memo Field issue
I have a linked Oracle table that I have a simple query against. I am having an issue with the field (which is varchar2 in the Oracle table) is tagged as a MEMO field in access, so I cannot join on it.
I get various errors, from "Cannot Join on Memo, OLE, or Hyperlink field." I then used the Query wizard to build an unmatched query and now I get "The expression is typed incorrectly, or it is too complex to be evaluated.

I am trying to find SQL values (from substring of a CHAR(50) field) such as V8CC0000 that do not have matching

Oracle values such as VC80000 (this is the VARCHAR2 field in Oracle DB


Can't get real oracle error with Access error 3155
I'm using Access 2003 and I have a oracle linked table with ODBC.
When I try to insert in that table, I get an error 3155 (Insert on a linked table failed) but the oracle error is not displayed. How can I get the Oracle error when I use my table in Access ?


OLE Object
I am using MS Access 2003 to link to an 11g Oracle Database. There is an Oracle Table that has an OLE Object column field and I want to be able to get the information from that column. I can run the following SQL statement from an SQL Plus session and get the values but I want to run the same type of statement in MS Access.

How can I convert the statement below so it will run in MS Access?


ODBC uses new password but Access uses old password
I have an Access link to an Oracle database that I have used for years. My recent upgrade to Win7 successfully set up the 32 bit ODBC driver to the Oracle database and Access successfully used it for several months.

The ODBC driver is hard coded with the password by putting the "userID/password" in the user ID field of the ODBC driver.

Recently the database changed policy on the user ID and the password expired. During the time that they reset the user ID so the the password does not expire, they changed the password. I changed the userID/password in the ODBC driver and successfully tested the connection.

However the Access link still references the old userID/password and I could not change or remove the old userID/password using the Linked Table Manager in Access. I can successfully usethe Access link if I overtype the correct userID and password after the automatic login fails but it occasionally results in a locked user ID with following tasks to get the user IDunlocked.

Why does the ODBC driver remember the new userID/password but Access insists on using the old userID/password? How can I fix this?


changes in VBA code when oracle and MS access are linked
I have till now used MS access tables for database and thus peacefully used Dlookup for selecting a particular column value and insert and delete and update for row modifications.

Now I am going to destroy these tables and link my MS access forms to oracle database. I would like to make the specified modifications to the VBA code of the buttons .

1. Is dlookup also applicable to Oracle or should I write a select SQL command. If so how to retrieve just a column value using SELECT command and assign it to a text box. ( Problem here is that the SQL when run returns a record set not just a single variant.

2. What modifications should I do before I execute a SQL command for insert delete and update. could you please give the syntax for connecting to oracle database