How to Update Records in Access With Data From Oracle DB

[mp1115]

Hello All,

The background to my question:
I have an Excel sheet, an Oracle db, and an Access db. I click a button on the Excel sheet and it connects to the Oracle db and grabs some information and pastes it into specific cells on the sheet. I then manually input additional information to the Excel sheet then click another button that sends all the info off as a single record to my Access db.

The issue:
After the export to Access, some corresponding data in Oracle will change. I would like to bypass the Excel via some code in Access that I can run that will connect directly to the Oracle db and update all of my Access records.

The question:
How do I connect to Oracle from Access, run through every record in my access-table, and update access-table.status with oracle-table.status where access-table.id matches oracle-table.id?

I am including a scaled down template of my Excel code because I would like to stay as consistent with how its written as possible. Thanks in advance for any and all assistance!

The code:

Private Sub importOra_Click() 'import status from oracle

	Dim cnOra As New ADODB.Connection
	Dim rsOra As New ADODB.Recordset
	Dim connOra As String, ID As String, sql As String

	ID = [A1]
	connOra = "Connection String"

	sql = "SELECT table.status FROM table WHERE table.id='" & ID & "';"

	cnOra.Open connOra
	
	' copy status from Oracle to cell A2
	rsOra.CursorLoaction = adUseClient
	rsOra.Open Source:=sql, ActiveConnection:=cnOra
	Sheet1.Range("A2").CopyFromRecordset rsOra

	rsOra.Close
	Set rsOra = Nothing
	cn.Close
	Set cn = Nothing

End Sub

Private Sub exportAccess_Click() 'export status to access

	Dim cnAccess As New ADODB.Connection
	Dim rsAccess As New ADODB.Recordset
	Dim connAccess As String, tbl As String, ID As String, sql As String

	tbl = "projects"
	ID = [A1]
	sql = "SELECT * FROM " & tbl & " WHERE " & tbl & ".[id]='" & ID & "';"

	
	cnAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & connAccess
	rsAcces.Open sql, cnAccess, adOpenKeyset, adLockOptimistic, adCmdTableDirect

	With rsAccess
	.AddNew
	.Fields("status") = [A2]
	.Update
	.Close
	End With

	Set rsAccess = Nothing
	cnAccess.Close
	Set cnAccess = Nothing

End Sub

 

[Micron]

Without actual table/field names, here is a generic statement that you should be able to use as an example to build your own sql statement if you link the Oracle table to the Access db.
UPDATE AccessStatus Set Access.Status = Oracle.Status WHERE AccessID = OracleID
Note the statement in my signature after point #3.

 

[xenou]

If you can create a pass-through query you can have the oracle data in your access DB as a table (just the needed two fields are necessary). then run a regular access update query against that data.

 

[mp1115]

Thank you for your responses, Micron and xenou. I used a few resources and answers to questions from various websites and put together the following so I figured I'd share in case it proves useful for anyone else:

Private Sub UpdateStatus()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset, rsOra As New ADODB.Recordset
    Dim idOra As String, sqlOpp As String, sqlSelect As String, sqlOraId As String, statusOra As String, tblOpp As String
    
    tblOpp = "Opportunities"
    sqlOpp = "SELECT * FROM " & tblOpp & ";"
    sqlSelect = "SELECT Status FROM Table WHERE Id='"
    
    cn.Open "Connection String"
    
    rs.Open sqlOpp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
    rs.MoveFirst
    Do Until rs.EOF = True
        idOra = rs.Fields("Opp Number")
        sqlOraId = sqlSelect & idOra & "';"
        
        rsOra.Open sqlOra, cn
        statusOra = rsOra.GetString
        
        With rs
            .Fields("Status") = statusOra
            .Update
            .MoveNext
        End With
        
        rsOra.Close
        Set rsOra = Nothing
    Loop
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

 

[Micron]

Aside from saying it looks well written, the only other comment I have is that without any error handling, you will get error messages as a result of trying to open and close rs if for some reason the connection cannot be made. Whether or not that could be an issue depends on the user - inconsequential if it's you, I'd venture to say. Thanks for posting your solution.