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

Tue Jul 8 08:50:35 CDT 2008

I used Jennifer's approach for years.

I had users who had laptops who would connect to the server data when they were in the office and local lap top data when they were away.  I had a procedure that checked to see if the server environment was available.  If it was, it checked the connect string of a particular table.  If the server is available and the current link points to the C drive, then the active tables are renamed with a prefix and the inactive tables are stripped of a different prefix.

The renaming function:

Private Sub ReNamer(strSetInActive As String, strSetActive As String)
    On Error GoTo ErrorHandler

    Dim A As Variant
    Dim lngI As Long

    A = Array("tblCompany", "tblCompanyContact", "tblContact", "tblDistance", _
      "tblEmployee", "tblEmployeeCourse", "tblEquipment", "tblEstimate", _
      "tblProjectArchitect", "tblProjectChange", "tblProjectCompetitor", _
      "tblProjectEngineer", "tblProjectEquipment", "tblProjectInvoice", _
      "tblProjectSubContractor", "tblProjectSubContractorChange", _
      "tblProjectSupplier", "tblProvince", "tblRegion", "tblSafetyCourse")
    DoCmd.Hourglass True
    For lngI = 0 To UBound(A)
        DB.TableDefs(A(lngI)).Name = strSetInActive & gDB.TableDefs(A(lngI)).Name
        DB.TableDefs(strSetActive & A(lngI)).Name = Mid$(gDB.TableDefs(strSetActive & A(lngI)).Name, 8)
    Next

ExitRoutine:
    On Error Resume Next
    Set DB = Nothing

    DoCmd.Hourglass False
    Exit Sub
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - " & _
                  "modStart.ReNamer"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Sub

The caller:

Private Sub InitializeLinks()
    Dim strConnect As String
    Dim strPrefix As String

    Set DB = CurrentDb
    strConnect = DB.TableDefs("tblCompany").Connect
    If Mid$(strConnect, 11, 1) = "C" Then
        strPrefix = "usysLap"
    Else
        strPrefix = "usysSrv"
    End If
    If fnServerAvailable Then
        If strPrefix = "usysLap" Then
            ReNamer strPrefix, "usysSrv"
        End If
    Else
        If strPrefix = "usysSrv" Then ReNamer strPrefix, "usysLap"
    End If
End Sub

I used an array of names which turned out to be the quickest way to limit the tables to be switched.  The initialize links procedure ran at startup in far less time than the most optimized relinking procedures, especially when you switched ODBC linked tables.  We're talking .01 seconds vs 2 minutes.

This approach requires you to link to multiple sets of tables, each with a unique prefix.  I could, for example, when I am connected to the server, deliberately connect to a local copy of the data just by running the following code:

ReNamer "usysSrv", "usysLap"

The effect of the approach is also to hide the currently inactive set of tables from the database window unless you have your properties set to show hidden or system tables.  The usys prefix identifies the tables as system tables so they are ordinarily not visible.

My actual array of tables was somewhat larger, on the order of 150 tables, and the code still took only a tiny fraction of a second to change environments.  It would be easy enough to set up with additional sets of intermittently active sets of tables.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com

> Date: Mon, 7 Jul 2008 16:48:35 -0800
> From: jengross at gte.net
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Same tables, Different data source (ODBC)
>
> Hi Mark,
>
> Am I understanding correctly that during development you either will not, or
> chose not to be connected to the live Oracle tables, but in your test or
> development environment you have re-created those tables in another format
> so that you can work with them?
>
> If that is the case then I have a database that connects to Oracle, do not
> have that connection during development or testing and what I've done is
> fairly simple (read lame). I have the Oracle connected table and the Access
> linked table, both with the same name, except whichever table is NOT live at
> the time I put one or two underscores after the name. When I post an update
> to the live environment I put the underscore(s) on the Access linked table
> names and remove them from the Oracle connected tables.
>
> I am anxious to hear what others have to say because I have been meaning to
> streamline this process into code for a while now.
>
> Jennifer
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Porter
> Sent: Monday, July 07, 2008 2: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

_________________________________________________________________
Try Chicktionary, a game that tests how many words you can form from the letters given. Find this and more puzzles at Live Search Games!
http://g.msn.ca/ca55/207