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

Dan Waters dwaters at usinternet.com
Tue Jul 8 08:31:49 CDT 2008


Mark,

This is part of the code I use to automatically relink to tables.  In my
scenario, I move a FE file from my PC to a customer server and back again.
When this happens, the code below will automatically relink to tables when
the FE is opened if needed.  

This code is for DAO - I don't know what mods might be needed for Oracle.

Hope this helps!
Dan

'----------------------------------------------------
Public Sub RelinkFrontEndTablesDemo()

    '-- This will verify that the table links to the BE are correct. Only
one table is checked.

    Dim tdfBE As DAO.TableDef
    Dim tdfTT As DAO.TableDef
    Dim stgMessage As String
    Dim stgSystemFolderPath As String
    Dim stgSystemBEName As String
    Dim stgBEPath As String
    Dim stgTTPath As String
    Dim dbs As DAO.Database
    
    '-- Get BE path
    stgBEPath = BEFullPath  '-- BEFullPath is a function returning a string
path to the BE file
    
    '-- Set tdf variables for FE Database
    Set dbs = DBEngine(0)(0)
    Set tdfBE = dbs.TableDefs("tblPeopleMain")
    Set tdfTT = dbs.TableDefs("tblTTOpenItemsList")
    
    '-- Is relinking needed?
    stgTTPath = CodeProject.Path & "\PSITempTables.mdb"
    If tdfBE.Connect = ";DATABASE=" & stgBEPath And tdfTT.Connect =
";DATABASE=" & stgTTPath Then
        dbs.Close
        Exit Sub
    End If
    dbs.Close
    
    '-- Attempt to relink
    If RelinkingTables(stgBEPath, stgTTPath, "Front End") = False Then
        stgMessage = "Automatic Front End Table Re-Linking was NOT
Successful." _
            & vbNewLine & vbNewLine _
            & "Table Name = " & MstgTableName _
            & vbNewLine & vbNewLine _
            & "Contact your System Owner." _
            & vbNewLine & vbNewLine _
            & "The system will now shut down.@ @"
        MsgBox GstgReminder, stgMessage, vbCritical + vbOKOnly, "Re-Linking
Not Successful"
        DoEvents
        Application.Quit
    End If
    
End Sub
'----------------------------------------------------
Private Function RelinkingTablesDemo(stgBEPath As String, stgTTPath As
String, stgName As String) As Boolean

    Dim tdf As DAO.TableDef
    Dim dbs As DAO.Database
    Dim intStatus As Integer
    Dim varStatus As Variant
    Dim stgtest As String
    Dim intCustomerLibrary As Integer
    Dim stgCurrentPCName As String

    '-- Set correct database
    If stgName = "Library" Then
        Set dbs = CodeDb
    Else
        Set dbs = DBEngine(0)(0)
    End If

    '-- Set up progress bar
    varStatus = SysCmd(acSysCmdInitMeter, "Refreshing " & stgName & " Table
Links . . .", dbs.TableDefs.Count)
    intStatus = 0
    
    '-- Relink the table if it is a link
    For Each tdf In dbs.TableDefs
        If (tdf.Attributes And dbAttachedTable) Then
            MstgTableName = tdf.Name
            If Left(tdf.Name, 5) = "tblTT" Then
                '-- Temp Table Path
                If tdf.Connect <> ";DATABASE=" & stgTTPath Then
                    tdf.Connect = ";DATABASE=" & stgTTPath
                    tdf.RefreshLink
                End If
            Else
                '-- BE Path
                If tdf.Connect <> ";DATABASE=" & stgBEPath Then
                    tdf.Connect = ";DATABASE=" & stgBEPath
                    tdf.RefreshLink
                End If
            End If
        End If
        intStatus = intStatus + 1
        varStatus = SysCmd(acSysCmdUpdateMeter, intStatus)
        DoEvents
    Next tdf
        
    varStatus = SysCmd(acSysCmdClearStatus)
    
    RelinkingTablesDemo = True
    
    dbs.Close

End Function
'----------------------------------------------------

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

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




More information about the AccessD mailing list