Topics Search

Create link table in access from Oracle

Create link table in access from Oracle
Views: 8
I am trying to create a link table to oracle. I've created the ODBC and I can see the list of oracle tables. A view of the table I need to access was generated but I can't see it in the list of tables.

I was told I would need to create code in the database to bring in the viewtable. I was given the below to play around with. I created a button on a form and put this code behind the OnClick event. However I'm having no joy with it.

conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "data source=" & datafil & ";Jet OLEDB:Database
Password="
conn.Open datafil
SQL = "SELECT * & " & _
INTO " & _
"" & Temp_Table & " " & _
"FROM " & _
"[ODBC;DSN=orcl;UID=SYSTEM;PWD=password;].[system.id_card_master_view]"
conn.Execute SQL
Set conn = Nothing
Sponsored Links:

More topics

 

Link tables/get external data issue

Preview
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

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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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?

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