[AccessD] Same tables, Different data source (ODBC)

Mark,

I have not worked with Oracle databases, but I have used code similar to
this to change table links to different SQL Server databases located on
the same server.  If the first characters of the connection string for
Oracle are not "ODBC", then change the value assigned to strSQLPrefix.
You should also add error checking.

Joe O'Connell

Public Sub ChangeLinks(ByVal strDatabase As String)
    Dim lngBeg As Long
    Dim lngEnd As Long
    Dim strConn As String
    Dim tdf As TableDef
    Dim strSQLPrefix As String
    strSQLPrefix = "ODBC"
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
            If Left$(tdf.Connect, Len(strSQLPrefix)) = strSQLPrefix Then
                strConn = tdf.Connect
                lngBeg = InStr(1, strConn, "DATABASE=") + 8
                lngEnd = InStr(lngBeg, tdf.Connect, ";")
                strConn = Left(tdf.Connect, lngBeg) & strDatabase _
                        & Right(tdf.Connect, Len(tdf.Connect) - lngEnd +
1)
                tdf.Connect = strConn
                tdf.RefreshLink                
            End If
        End If
    Next tdf
End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Porter
Sent: Monday, July 07, 2008 6:50 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Same tables, Different data source (ODBC)

I have an app that links to tables in an Oracle DB.  I'd like to be able
to quickly change that environment from the Dev to the Test or Prod (the
tables linked will stay the same).

 

Does anyone have any code or techniques to do this quickly?

 

Thanks in advance,

 

Mark Porter

Sr. Technologist

Nana Development Corp.

Desk: 907-265-4156

Fax: 907-343-5656

 

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com