MS Access and Oracle ODBC insert

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.
mjfigurAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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.
0
 
Wasim Akram ShaikCommented:
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
0
 
mjfigurAuthor Commented:
Thank you so much I will check this out. We are currently using Oracle 11g
0
 
mjfigurAuthor Commented:
Thank you ODBC was exactly what I was looking for in this situation.
0
All Courses

From novice to tech pro — start learning today.