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

Sherri

When we were discussing Berrien last week - did you approve moving it to
live?  I remember the discussion, but not the outcome!

Let me know,

Thanks,
Sheri

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe O'Connell
Sent: Monday, July 07, 2008 11:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

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