Avatar of kosenrufu
kosenrufu
 asked on

How can I establish a link to a table in an oracle database from Access using the following script?

Hi Experts,

I have the following scripts. Thanks

Public Function Y_Connection()


    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection

    sConn = _
        "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
        "(Host=xxxx)(Port=1521)))(CONNECT_DATA=(SID=p150)));" & _
        "User Id=ieaudt1;Password=ieaudt1;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    Set adoRS = New ADODB.Recordset

End Function


 

      Dim td As DAO.TableDef

 

 

 

  '**Establish link to table

        Set dbd = OpenDatabase(DestPathIs & "\" & strDatabase)

        For Each td In dbd.TableDefs

            If Left(td.Name, 4) <> "msys" And Left(td.Name, 1) <> "~" _

                And InStr(td.Name, "ImportErrors") = 0 Then

                DoCmd.TransferDatabase acLink, "Microsoft Access", DestPathIs & "\" & strDatabase, acTable, td.Name, td.Name

            End If

        Next
Microsoft Access

Avatar of undefined
Last Comment
kosenrufu

8/22/2022 - Mon
Bill Ross

Hi,

What is your question?
kosenrufu

ASKER
How can I create a link to a table in Oracle without using ODBC connection? I need to join a table in Access to a table in Oracle and append to a table in Access. This is why I need the link so I can create a query. Hope this makes sense. Thanks
Bill Ross

What is the purpose of code shown in your question?   Are you using the code now?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
kosenrufu

ASKER
I am using the following code now to read from the table:

Public Function Y_Connection()


    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection

    sConn = _
        "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
        "(Host=xxxx)(Port=1521)))(CONNECT_DATA=(SID=p150)));" & _
        "User Id=ieaudt1;Password=ieaudt1;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    Set adoRS = New ADODB.Recordset

End Function

I guess what I need is the script to append the data from the Oracle table named PR1.ORDERS to a local Access database table named ORDERS.

I thought somehow I can use the following script to create the link to the Oracle table:

      Dim td As DAO.TableDef

 

 

 

  '**Establish link to table

        Set dbd = OpenDatabase(DestPathIs & "\" & strDatabase)

        For Each td In dbd.TableDefs

            If Left(td.Name, 4) <> "msys" And Left(td.Name, 1) <> "~" _

                And InStr(td.Name, "ImportErrors") = 0 Then

                DoCmd.TransferDatabase acLink, "Microsoft Access", DestPathIs & "\" & strDatabase, acTable, td.Name, td.Name

            End If

        Next
ASKER CERTIFIED SOLUTION
kosenrufu

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.