Solved

vba to relink oracle tables in msaccess 2007

Posted on 2011-09-08
7
360 Views
Last Modified: 2013-01-13
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.
0
Comment
Question by:graham-n
  • 2
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
0
 

Author Comment

by:graham-n
Comment Utility
the link above looks like it is for msaccess tables. Oracle tables are linked by ODBC, the do not have a path.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
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
 

Author Comment

by:graham-n
Comment Utility
I will attempt to work out a solution using parts of the replies.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now