We help IT Professionals succeed at work.

MS Access and Oracle ODBC insert

Medium Priority
304 Views
Last Modified: 2012-03-17
I just left a project and was wondering how this would be accomplished. There is an access database that is managed by another group and we need that data in our Oracle tables. We have a query already in access that will join the various tables in the Access Database and the result of the query is in the exact layout of our Oracle Table. How would we go about creating an ODBC connection and executing an insert based on the result of the Access query?

I have done ODBC connections to view Oracle tables in Access, but never tried an insert and truncate from Access back to Oracle.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Yes, it can be done using the Database Gateway, you haven't mentioned the version of your oracle..

There is one article on EE written by sdstuber which will help you in this regard, refer to this article ..

http://www.experts-exchange.com/Database/Oracle/A_9850-Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html

Author

Commented:
Thank you so much I will check this out. We are currently using Oracle 11g
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
Gateway would be used to pull the data from the Oracle side.  It sounds more like you are trying to push it from the Access side.  If that is the case, then you don't need Gateway.  Simply having ODBC set up correctly will work.

As long as you can select from the Oracle database from Access and all the links are set up correctly, you insert the same way you would select.  I prefer to use passthru queries in Access as you have more control over how the query is constructed.

Author

Commented:
Thank you ODBC was exactly what I was looking for in this situation.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.