[AccessD] Changing the ODBC Connection String to use UNC - Strange Results

Hi Brad,

I've been doing exactly this for exactly the same reason for many years and
it's been very reliable.

'---------------------------------------

Code to Refresh:

    '-- Relink the table if it is a link
    For Each tdf In dbs.TableDefs
    
        '-- Links to MDB
        If (tdf.Attributes And dbAttachedTable) Then
            '-- BE Path
            If tdf.Connect <> stgAdminPath And stgAdminType = "JET" Then
                tdf.Connect = stgAdminPath
                tdf.RefreshLink
            End If
        End If
        
        '-- Links to SQL Server
        If (tdf.Attributes And dbAttachedODBC) And stgAdminType = "SQL" Then

            '-- Refresh every time
            tdf.Connect = stgAdminPath
            tdf.RefreshLink
                
        End If
                
        intCount = intCount + 1
        Call DisplayPB(intCount)
        DoEvents
        
    Next tdf

Note:  stgAdminType is an internal variable which tells this code if the
tables are in in an MDB file or in SQL Server.
       stgAdminPath is another internal variable which contains the
connection string to the desired mdb file or SQL Server database.

'---------------------------------------

However, if your back end is SQL Server and you changed the definition of a
table, then the ODBC table link must be recreated, not refreshed.

Code to Recreate:

    '-- Get a list of all ODBC tblCollection
    For Each tdf In dbs.TableDefs
        If (Left(tdf.Connect, 4) = "ODBC") Then
            tblCollection.Add Item:=tdf.Name, Key:=tdf.Name
        End If
    Next tdf
    
    
    varProgress = SysCmd(acSysCmdInitMeter, "Recreating " & stgProjectName &
" ODBC Table Links", tblCollection.Count)

    '-- Create new tblCollection using the given DSN after moving the old
ones
    For i = tblCollection.Count To 1 Step -1
    
        stgNameOriginal = tblCollection(i)
        stgNameTemp = "~" & stgNameOriginal & "~"
        stgNameSource = dbs.TableDefs(stgNameOriginal).SourceTableName
        
        '-- Create the replacement link
        dbs.TableDefs(stgNameOriginal).Name = stgNameTemp
        Set tdf = dbs.CreateTableDef(stgNameOriginal, dbAttachSavePWD,
stgNameSource, conConnectionString)
        dbs.TableDefs.Append tdf
        dbs.TableDefs.Refresh
        
        '-- delete the old link
        DoCmd.DeleteObject acTable, stgNameTemp
        dbs.TableDefs.Refresh
        tblCollection.Remove stgNameOriginal
        
        intCount = intCount + 1
        varProgress = SysCmd(acSysCmdUpdateMeter, intCount)
        
'        Debug.Print "Refreshed ODBC table " & stgNameOriginal
        
    Next i
    
    varProgress = SysCmd(acSysCmdRemoveMeter)
    
    Set tblCollection = Nothing
    Set dbs = Nothing

'------------------------------

HTH!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, October 22, 2012 9:37 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Changing the ODBC Connection String to use UNC - Strange
Results

All,

We have an Access 2007 application that connects to a FireBird Database via
ODBC.
This application has worked nicely for several months.

There is both a Test and a Production copy of the Firebird Database on a
server.

I have mapped one drive to point at the Production database and mapped a
second drive to point at the Test database.

There is VBA code that switches between Test and Production by changing the
DbName in Tabledef.Connect.

This works nicely with the mapped drives.

Recently, I had the idea of using a UNC instead of the mapped drives.  I
wanted to do this because more users need to access the Test database and I
wanted to avoid the need to set up mapped drives on additional PCs.

I changed the VBA code to use the UNCs and I am now seeing inconsistent
results.

Sometimes the switch between Test and Production works, sometimes it does
not. (Error 3151 - ODBC Connection Failed).

Has anyone else ever run into this issue?  I have tried many tests and I
cannot understand why this is happening.

Thanks,
Brad

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