Change datasource for linked tables during session

Posted on 2007-08-08
Last Modified: 2013-11-27
{text edited for sensitive material by asker, and reposted by ee_ai_construct, cs moderator 8/9/07}


we are using MS Access 2003 to link Oracle Databases to the Access DB.
Currently we have 2 available databases, one for developing and a production database. I'd like to be able to switch from one database to another during session.

I have tried the following code (also tried with several modifications) with no success. It deletes the linked tables correctly but instead of linking to the new DB it still uses the old one:

Public Sub CheckUser()
    Dim sUser As String
    Dim sDomain As String
    Dim sODBCConnect As String
    Dim rsUser As DAO.Recordset
    Dim sDatabase As String, sDBLogin As String
      sDatabase = Nz(DLookup("value", "localConfig", "name = 'database'"), "DBQ=localhost;")
    sDBLogin = Nz(DLookup("value", "localConfig", "name = 'auth'"), "UID=TESTUSER;PWD=xxx;")
    Call Global_UnAttachTables 'delete attached tables
    'Somehow this is neccessary to make the next step work
    Call Global_AttachTableDSNLess("GROUPS", sDBLogin & sDatabase)
    CurrentDb.TableDefs.Delete "TAB_GROUPS"
    Set gdbOracle = OpenDatabase("ODBC", False, False, sODBCConnect)

    Call Global_GetSystemData(sUser, sDomain) ' gets the current user

    Set rsUser = gdbOracle.OpenRecordset("SELECT * FROM T_BG_USER WHERE UPPER(WIN_LOGIN) = '" & UCase(sUser) & "'", dbOpenSnapshot, dbSQLPassThrough)
    sConnectionString = sDBLogin & sDatabase
    If rsUser.RecordCount > 0 Then
            'relink tables
            Call Global_AttachTableDSNLess("ALL_CUSTOMERS", sDBLogin & sDatabase)
            Call Global_AttachTableDSNLess("CONFIG", sDBLogin & sDatabase)
            Call Global_AttachTableDSNLess("COUNTRY", sDBLogin & sDatabase)
        Global_CheckUser = True
        MsgBox "unauthorized", vbCritical
        DoCmd.SetWarnings False
        Global_CheckUser = False
    End If
End Sub

Public Function Global_AttachTableDSNLess(sTabName As String, Optional sConnectStr As String = "") As Boolean

    Dim Tb As TableDef
    Dim sODBCConnectStr As String
    If sConnectStr = "" Then
        sConnectStr = sConnectionString 'this is a global var
    End If
    Set Tb = CurrentDb.CreateTableDef()
    Tb.NAME = "TAB_" & sTabName
    Tb.SourceTableName = "T_BG_" & sTabName
    Tb.Connect = sODBCConnectStr
    CurrentDb.TableDefs.Append Tb
End Function

Strangely, when debugging I noticed the sODBCConnect Variable in the Checkuser sub is filled correctly etc however after calling OpenDatabase, the connect property of gsdOracle holds the old connection settings.

Any help is greatly appreciated.

Deleted by modus_operandi, 500 points refunded. - 8/24/2007 6:56:15 AM
Question by:lucki_luke
    LVL 119

    Expert Comment

    by:Rey Obrero
    you may need to delete first the old connection in the msysobjects table
    LVL 9

    Author Comment

    Thanks, i'll try that tomorrow at work.
    LVL 9

    Author Comment

    Hm, thanks for the tip, however that wasn't the problem.

    The problem was, that although the function was called AttachTableDSNLess, it still used a DSN entry and it was linked one datasource. So making it really DSN-Less solved the problem.

    But maybe you can help me with another issue:
    As you may have noticed, before looking up a user using a DSN entry, some code is required:

        'Somehow this is neccessary to make the next step work
        Dim Tb As TableDef
        Set Tb = CurrentDb.CreateTableDef()
        Tb.Name = "TAB_GROUPS"
        Tb.SourceTableName = "T_BG_GROUPS"
        Tb.Connect = "ODBC;DSN=DSNENTRY;UID=" & sDBUser & ";PWD=" & sDBPass & ";DBQ=" & sDatabase & ";DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;TABLE=DB.TAB_GROUPS"
        CurrentDb.TableDefs.Append Tb
        CurrentDb.TableDefs.Delete "TAB_GROUPS"

    Is this a normal behaviour? If we omit that part, we get the runtime error 3151, ODBC-Connection to X failed.
    LVL 9

    Accepted Solution

    Never mind, I eventually switched completely to DSN-less connections and rewrote the code so that it now works.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now