View Single Post
  #2 (permalink)  
Old January 30th, 2012, 03:58 PM
gjgriffith's Avatar
gjgriffith gjgriffith is offline
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Why not create a Linked Table in the Access database to Oracle?

Hello Leopoldo,

Thank you so much for posting your question about using Oracle data in the Microsoft Access database application. Also, thank you for reading the Microsoft Access 2007 VBA Programmer's Reference book, we appreciate your readership! To try to answer your question, you asked:

I need to know...if there’s a way to bring those records “via ODBC”...directly INTO a Local access table. I Can’t just do a SELECT * from A Into B, since A is in ODBC environment and B is in a Local Table in my Access.mdb.
Ok, so I believe you have at least 2 options to do this:

1. You can create a "Linked Table" to the Oracle tables. Once you have the ODBC drivers for Oracle installed on your computer ( You should be able to create linked tables to Oracle (using the "ODBC" button from the "External Data" Ribbon) and then call that linked table from VBA using DAO. Although the data won't technically be local, you should still be able to Access it with DAO.

2. If you really do need the data local, and not just in a linked table, you can create the linked table tot he oracle database, create another table with the same structure as the Oracle linked table, and then create an Append query to append the data from the Oracle linked table to the local Access table.

Anyway, hopefully these answers make sense to you, but please let me know if you have any questions about this. And thank you again for reading the Access 2007 VBA Programmer's Reference, we truly appreciate it!

Geoffrey L. Griffith

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote