• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 980
  • Last Modified:

Change datasource for linked tables during session

{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
  • 3
1 Solution
Rey Obrero (Capricorn1)Commented:
you may need to delete first the old connection in the msysobjects table
lucki_lukeAuthor Commented:
Thanks, i'll try that tomorrow at work.
lucki_lukeAuthor Commented:
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.
lucki_lukeAuthor Commented:
Never mind, I eventually switched completely to DSN-less connections and rewrote the code so that it now works.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now