troubleshooting Question

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

Avatar of kosenrufu
kosenrufu asked on
Microsoft Access
5 Comments1 Solution372 ViewsLast Modified:
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


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros