Refresh Link to Oracle Table from VBA

[kkitt]

Is there a way to refresh the link to an Oracle Tale from withing VBA Code when a password is required? I have code that will walk though and refresh other links to MDB databases, but have not been able to get to work with the Oracle Tables.

This is the code that I am using to refresh the link. Will not work unless I have done the manual link first, then it runs like a charm. How do I get this to work the first time????

Error returned is:
Run Time Error 3151
ODBC--connection to 'Artsql' failed

Dim CurDB As Database, tdfLinked As TableDef
    Set CurDB = CurrentDb()
    For Each tdfLinked In CurDB.TableDefs
    If tdfLinked.Attributes And dbAttachedODBC Then
        If Len(tdfLinked.Connect) > 0 Then
            tdfLinked.Connect = "ODBC;DSN=ArtSql;SERVER=twdbp;DATABASE=;pwd=twbrio"
            tdfLinked.RefreshLink
        End If
    End If
    Next tdfLinked
Set CurDB = Nothing

 

[HitechUser]

kkitt,

This may help...

http://www.mvps.org/access/tables/tbl0010.htm

Also try searching the VBA forum..

 

[cmmrfrds]

I have used the following code to link sql server tables, which should be similiar for Oracle. It is probably just a matter of using the correct connection string.


Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={bigtuna};" & _
"Database=pubs;" & _
"Uid=sa;" & _
"Pwd=;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "authors"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "authors"
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={local};" & _
"Database=pubs;" & _
"Uid=sa;" & _
"Pwd=;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.type = "LINK" And tbl.Name = "authors" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function