vba to relink oracle tables in msaccess 2007

I want to use msaccess 2007 linked ODBC Oracle tables with different oracle DBs e.g. dev, uat.

e.g. original msaccess  link is to Oracle table schema1.tablex in SIDA. Access would call this schema1_tablex.

I want a script to relink schema1_tablex to e.g. schema2.tablex in SIDB. Need msaccess to still use the name schema1_tablex. As you can see, the Oracle table name stays the same, but schema and SID need to be able to be respecified when required.

I need a script, not the linked table manager in the menu.
graham-nAsked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
I am coding to paste some code in that I use for SQL Server.
The idea is the same.
The problem is that, if you are NOT using Windows Authentication, Access will NOT save the password in the connection string.
For that, you must use the GUI or delete and re-link tables.

If you have worked out the required connection strings, you should be able to transmogrify my code appropriately
Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "DATABASE=YourNameHere")
        If intSubStringLoc > 0 Then
            TD.Connect = "DRIVER=SQL SERVER;SERVER=MyServer\MyInstance;DATABASE=YourNameHere;Network=DBMSSOCN;Trusted_Connection=Yes"
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
0
 
graham-nAuthor Commented:
the link above looks like it is for msaccess tables. Oracle tables are linked by ODBC, the do not have a path.
0
 
graham-nAuthor Commented:
I will attempt to work out a solution using parts of the replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.