We help IT Professionals succeed at work.

Access ADP query different database than current

NEVAEHSIN
NEVAEHSIN asked
on
Hey Experts,

I have a Access ADP front end on a SQL server database.  We also have an Oracle database with Java front end.

I would like to query data from the Oracle database (from ADP forms VBA) to populate the form and insert/update data on the SQL database.  

Just looking for links/sites that would be helpful with this (if it's even possible)...

Thanks!
Comment
Watch Question

Top Expert 2006
Commented:
One way is to use ADO and ODBC.

I am not 100% sure but I thought ADP's were geared towards sql server and you would be better off using mdb's for accessing oracle. I have talked to oracle via mdb's but never adp's.

MS has a link with examples
http://support.microsoft.com/kb/281998

You could also create linked tables then access them that way
Top Expert 2006

Commented:
a note on linked tables, you cannot do this in ADP so I do think you would have to go the MDB route.

Author

Commented:
"The ADO connection that is used by the recordset must use the Microsoft OLEDB provider for ODBC"

I could be wrong but I don't think I can connect to the Oracle Database using a Microsoft OLEDB provider.

I'm not looking to bind the form to the Oracle Table.  I want to create records in the SQL Database using records from Oracle and then bind the form to the SQL Database with the records.

Basically, I have a form with 2 text boxes, "Customer" and "Order Number" both bound to a SQL table.  The idea is that the user enters an order number, after update, a function call pulls the "Customer" for that "Order Number" in the Oracle database, updates the corresponding SQL records, and the form refreshes to show the customer.

I just need to know how/if the connection to the Oracle Database can be made through coding it.  Everything I've read indicates connecting to another SQL database - I assuming because you can't connect to an Oracle data source?

I had assumed that since SSRS, SPS, etc could connect that I could to something similar in ADP.  I am using Access 2010 if that makes any difference.

Author

Commented:
Oh and linking tables is out of the question - if it's possible.
Top Expert 2006

Commented:
My concern here is if you can access any kind of database outside of sql server in ADPs. As I said they were made for sql server. But my knowledge in that is limited.

The sample code uses ODBC and you can use this to create a connection to the oracle database and create recordsets. There i s no need to bind to a form, this is all vba coding.

Look towards the bottom of the link I gave for the ODBC example
Top Expert 2011

Commented:
- as what i know, ADP is specifically for Microsoft SQL Server databases and not other databases so you cannot connect directly to Oracle. however you can setup linked servers in SQL Server to connect to the Oracle and run your query via SQL Server.

- here is a few links on setting up the linked server:
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687
http://gleew.wordpress.com/2008/03/19/sql-server-linked-server-to-oracle/
 

Author

Commented:
Works like a charm...  And I was even able to make the combo box equal the value pulled e.g.

Dim ifkCust As Integer
    ifkCust = DLookup("pk_cust", "tblCust", "cust_code='" & rsORA.Fields("cust_code") & "'")
Me.cboORA = ifkCust

before closing the connection.  Didn't have to do any inserts/updates with multiple record sets.

Now I just need to create a nifty not in list event to update my customer table, just in case!

Thanks!