Access 2000 Relink front-end to back-end tables using a path supplied by an .ini file

I have a classic FE/BE where the FE db is in a folder Applications, and all of the various customer BE db files are in a folder called Data Files.  The Data Files folder has sub folders for each customer (Customer A, Customer B, etc.).  The BE file structure is the same for all customer BE data files.  There is a path.ini file in the same folder as the FE which holds a single text line with the path to the particular customer BE.  This make it easy to change the BE path by just altering the path.ini before opening the FE app.  However, sometimes the relink process doesn't run completely and I have some links to customer A and some to customer B.  I want to force a relink every time the FE app is opened, and perhaps streamline the code a bit.  Thanks
Public Function fRefreshAllLinks() As Boolean
    On Error GoTo Handle_Err
    'Created by RO on 4/23/2009
    Const conProcName As String = conModuleName & ".fRefreshAllLinks"
    '/ Forces a refresh of ALL table links between FE and BE
    '/ This assumes that tblSoftwareUserBE has already been refreshed properly
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConn As String             ' first part of tdf.connect connection string
    Dim strDB As String                 ' second part of tdf.connect = path to the backend database
    Dim strConnect As String         ' assembled connection string
    Dim strWrongDB As String        ' name of the backend db incorrectly linked to
    Dim intTableCount As Integer    ' count of all linked application tables
    Dim intBadLinks As Integer       ' count of tables with bad links
    Dim intStillBadLinks As Integer      ' count of corrected links
        Set db = CurrentDb()
 
    '/ Initialize return variable
    fRefreshAllLinks = False
    
    intTableCount = 0
    intBadLinks = 0
    intStillBadLinks = 0
    
  '/ Get the back end path from Harvest.ini
    strDB = fGetINIdbPath
    
    '/ Creat the first part of the connection string
    strConn = "MS Access;PWD=bluebird;DATABASE="
'Debug.Print strdb
 
    '/ Assemble entire connection string
    strConnect = strConn & strDB
Debug.Print "The INI Path is :  " & strConnect
   
    '/ Loop thru the tables collection
    For Each tdf In db.TableDefs
        '/ Just look at the Harvest application tables (start with tbl)  atbl is ONLY front-end
        ' COMEBACK WHAT ABOUT USYSUBE ?
        
        If Left(tdf.Name, 3) = "tbl" Then
        intTableCount = intTableCount + 1
            '/ Check the table's connect property
            If Len(tdf.Connect) > 0 Then
Debug.Print intTableCount & " " & tdf.Name & " - " & tdf.Connect
            Else
        Debug.Print "BAD FILE    " & tdf.Name & " - " & tdf.Connect
                If tdf.Connect <> strConnect Then
                    intBadLinks = intBadLinks + 1
Debug.Print intBadLinks
                    tdf.Connect = strConnect
                    tdf.RefreshLink
                    '/ Try it again
                    If tdf.Connect <> strConnect Then
                        '/ Name of wrong DB
                        strWrongDB = strConnect
                        intStillBadLinks = intStillBadLinks + 1
Debug.Print " **************" & tdf.Name & " - " & tdf.Connect & " ************"
                    End If
                End If
            End If
        End If
    Next tdf
 
    If intStillBadLinks = 0 Then
        fRefreshAllLinks = True
    Else
        fRefreshAllLinks = False
    End If
    
    Set tdf = Nothing
 
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            MsgBox "Error: basUtilities - Function fRefreshAllLinks: " & Err.Number & " " & Err.Description, _
                        vbCritical + vbOKOnly, "Harvest error message"
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function

Open in new window

roverturfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
Rather than re-invent the wheel, I would test out the free auto FE Updater available for download at Tony Toews web site. It has been tested over a period of years, is stable, and has all the features you requested and then some!

               http://www.granite.ab.ca/access/autofe.htm
0
roverturfAuthor Commented:
I appreciate your suggestion, and will use code from that site later to update the front end.
However, the problem is with getting a good relink after the back end has been moved, or relink to a back end in a different location.  In my case the schema of the back end is the same for all customer data..  so same back end schema but in various locations.
Thanks for your comment.  I will work on front end updates next week, so you answered a question before I even posted it !  
-Richard
 
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

puppydogbuddyCommented:
some addtional tips/references for split databases involving Access:
              http://www.members.shaw.ca/AlbertKallal/Articles/split/

             http://help.wugnet.com/office2/Split-database-Linked-tables-ftopict847043.html

0
roverturfAuthor Commented:
Thanks !  I am checking them out now.
-Richard
0
roverturfAuthor Commented:
puppydogbuddy,

I've reviewed the links that you suggested, but they didn't really offer exactly what I was looking for.  I've attached a copy of my code to show you what I mean.  
This basLinkTables is just one module among many and the only one that is giving me trouble.  The app is designed for multiple users.   Rather than using Windows API calls to provide an Open File dialog box to locate the back-end or a table of tables in the FE, I added the .ini file to the workstation's FE folder so that the app can find the path to the BE from the .ini, preventing users from linking to the wrong file (like an old backup file).  It's been working fine for months, but somewhere along the way, I must have made some changes to the code, and I didn't document my code changes well enough.
The front end will link correctly initially, but if I change the .ini path, or update the BE tables and call for a RefreshLinks it may change the tdf.connect path for all tables but one or two.  I need for the function to force every table tdf.connect path from whatever it was to whatever is in the .ini
I'm hoping that another set of eyes on the code can help find whatever I am overlooking.  Thanks !
 

'Attribute VB_Name = "basLinkTables"
Option Compare Database
Option Explicit
 
' Set conRelinkAnyway to True to automatically relink tables when the database is opened,
' False to only relink tables if the source database has moved
' Courtesy of Andy Baron
 
Const conRelinkAnyway = False
Private Const conModuleName As String = "basLinkTables"
 
 
Public Function IsLinked(strTable As String) As Boolean
    On Error GoTo Handle_Err
    Const conProcName As String = conModuleName & ".IsLinked"
    '---------------------------------------------------------------------------------------
    ' Use inline error handling to check to see if a linked table still has a valid connect property.
    ' Returns True if still linked, False if not
    '---------------------------------------------------------------------------------------
    
    Dim varTable As Variant
    On Error Resume Next
    
    varTable = CurrentDb.TableDefs(strTable).Fields(0).Name
    
    If Err <> 0 Then
        IsLinked = False
    Else
        IsLinked = True
    End If
    
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function
 
Public Function fGetCurrentDBpathShort() As String
    On Error GoTo Handle_Err
    Const conProcName As String = conModuleName & ".fGetCurrentDBpathShort"
    
    ' Get the path of the current database by dropping characters from the right side until it finds \
    '/ Returns  "C:\Harvest Biomed Data\"  as a string ** Does not include the HarvestCEMSdata.mdb file **
 
    Dim db As Database
    Dim strName As String
        Set db = CurrentDb
        strName = db.Name
 
    Do While Right$(strName, 1) <> "\"
        strName = Left$(strName, Len(strName) - 1)
    Loop
 
    fGetCurrentDBpathShort = UCase$(strName)
 
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function
 
Public Function fGetCurrentDBpathLong() As String
    On Error GoTo Handle_Err
    Const conProcName As String = conModuleName & ".fGetCurrentDBpathLong"
    
    ' Get the path of the current database by dropping characters from the right side until it finds \
    '/ Returns  "C:\Harvest Biomed Data\"  as a string ** Does not include the HarvestCEMSdata.mdb file **
 
    Dim db As Database
    Dim strName As String
        Set db = CurrentDb
 
    fGetCurrentDBpathLong = db.Name
 
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function
 
Public Function fGetINIdbPath() As String
    On Error GoTo Handle_Err
    'Created by BB June 2006
    Const conProcName As String = conModuleName & ".fGetINIdbPath"
    
    '/ Retrieves the local DB path & name from Harvest.ini
    '/ This consolidates getting the BE path into a single function
 
    Dim db As DAO.Database
    Dim strLocalDBPath  As String
    Dim strMSG As String
    Dim i  As Integer
    Dim txtLine As String
        Set db = CurrentDb()
 
    '/ Initialize
    i = 0
    fGetINIdbPath = ""
    
    Open Left(db.Name, InStr(db.Name, "HarvestCEMS") - 1) & "Harvest.ini" For Input As #1
    
    Do While Not EOF(1)
        Input #1, txtLine
        i = i + 1
        If i = 1 Then
            fGetINIdbPath = txtLine
        End If
    Loop
    Close #1
 
exit_here:
    On Error Resume Next
    Exit Function
Handle_Err:
    Select Case Err.Number
        Case Else
            strMSG = "Error: basUtilities - fGetINIdbPath Failed: " & Err.Number & " " & Err.Description
                MsgBox strMSG, vbCritical + vbOKOnly, "Harvest error message"
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    'Resume
End Function
 
 
Public Function RelinkTables(strSourceDB As String, strTestTable As String) As Boolean
    On Error GoTo Handle_Err
    'Created by RO on 5/26/2009
    Const conProcName As String = conModuleName & ".RelinkTables"
    '---------------------------------------------------------------------------------------
    ' Check to see if tables are linked correctly by checking the link with a test table (strTestTable).
    ' If not, then relink them to the source database (strSourceDB).
    '---------------------------------------------------------------------------------------
    
    Const ProcName As String = "RelinkTables"
    
    Dim fReturn As Boolean
    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim rs As Recordset
    Dim strDBPath As String
    Dim strMSG As String
    Dim varDBname As Variant
    Dim strDBname As String
    Dim intReturn As Integer
    Dim intTableDefCount As Integer
    Dim iPos As Integer
    Dim strdb As String
    Dim txtLine As String
    Dim strBEdatabaseName As String         'Added 11/14/2005 RO
    Dim intChangeNumber As Integer
    Dim tdfSoftwareUserBE   As DAO.TableDef
    
    
    '/ Initialize - Assume success
    RelinkTables = True
     
     '/ Always use Harvest.ini to set path to backend
     
    '/ if fReturn=true then there is a link to the test table
    '/ if fReturn=false then there is no current link
        fReturn = IsLinked(strTestTable)
 
        '/ Get the link path path from the test table
        Set db = CurrentDb
        Set tdf = db.TableDefs("tblSoftwareUserBE")
        iPos = InStr(1, tdf.Connect, "Database=")
        strdb = Mid$(tdf.Connect, iPos + 9)
        Set tdf = Nothing
 
        '/ Rename the variable
        strBEdatabaseName = strdb
    
        '/ Look in the folder that contains HarvestCEMS to read the path from Harvest.ini
        Open Left(db.Name, InStr(db.Name, "HarvestCEMS") - 1) & "Harvest.ini" For Input As #1
            Do While Not EOF(1)
                Line Input #1, txtLine
            Loop
        Close #1
        
        Set db = Nothing
        
        '/ Rename variable
        varDBname = txtLine
    
        '/ Compare the test table's path to the Harvest.ini path
        If strBEdatabaseName <> varDBname Then
            MsgBox "    The currently linked database:                  (" & strBEdatabaseName & ")" & vbCrLf & _
                "     is not the same as specified in Harvest.ini:  (" & varDBname & ")" & vbCrLf & vbCrLf & _
                "**  This is normal whenever the program has been updated or if the path to the database has been changed.          " & vbCrLf & vbCrLf & _
                "Harvest will now try to relink to the database specified in the Harvest.ini configuration file.   " & vbCrLf & vbCrLf & _
                "             Ref: Function RelinkTables", _
                 vbExclamation, "Harvest Data Systems - Relinking Tables"
              fReturn = False
        End If
    
        '/ If fReturn is true then we have a good connection
        '/ If conRelinkAnyway=False then we don't want to relink anyway (conRelinkAnyway should always be false)
        '/ So... if conditions are met then we can exit the function, If not then continue to find and link the database
        If fReturn = True And conRelinkAnyway = False Then GoTo exit_here    '/ Success !
        
        '-----------------------------------------------------------------------------------------------------------------------
        
        '/ Connection failure - so iterate through the tabledefs collection to relink.
        '/ Skip local tables or linked files of other types.
        '/ Assumes that the FE and BE table names are the same
        Set db = CurrentDb
        
        'Setup status bar
        Call SysCmd(acSysCmdInitMeter, " Updating the links to the data file . . .", db.TableDefs.Count)
         
        '/ Betts March 2007
        '/ Refresh link tblSoftwareUserBE Immediately
        '/ Fixes an issue with changing ini file and distributing updates
                            
        Set tdfSoftwareUserBE = db.TableDefs("tblSoftwareUserBE")
        If Left(tdfSoftwareUserBE.Connect, 4) = ";DAT" Or _
            Left(tdfSoftwareUserBE.Connect, 4) = "MS A" Then
            tdfSoftwareUserBE.Connect = ";DATABASE=" & varDBname & ";PWD=bluebird"
            tdfSoftwareUserBE.RefreshLink
        End If
            
        Set tdfSoftwareUserBE = Nothing
 
        '/ Loop
        For Each tdf In db.TableDefs
            If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
 
            '/ Don't refresh link if tbl name is tblEquipmentStats and change # < 69
            intChangeNumber = DLookup("BEChangeNum", "tblSoftwareUserBE")
            
            If tdf.Name = "tblCBO_RiskDeviceCriticality" And intChangeNumber < 65 Then GoTo NextTDF
            If tdf.Name = "tblSystemData" And intChangeNumber < 67 Then GoTo NextTDF
            If tdf.Name = "tblEquipmentStats" And intChangeNumber < 69 Then GoTo NextTDF
            If tdf.Name = "tblUserLogonRecord" And intChangeNumber < 71 Then GoTo NextTDF
 
            If Left(tdf.Connect, 4) = ";DAT" Or _
               Left(tdf.Connect, 4) = "MS A" Then
                tdf.Connect = ";DATABASE=" & varDBname & ";PWD=bluebird"
                tdf.RefreshLink
            End If
        End If
                
        intTableDefCount = intTableDefCount + 1
         '/ Start status bar progress meter for linking
        Call SysCmd(acSysCmdUpdateMeter, intTableDefCount)
            
NextTDF:
            Next
            Call SysCmd(acSysCmdRemoveMeter)
 
exit_here:
    On Error Resume Next
    'rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    Exit Function
Handle_Err:
    RelinkTables = False
    Select Case Err.Number
        Case Else
            MsgBox "Error Number: " & Err.Number & " " & Err.Description & vbCrLf & _
            "Tables failed to relink.  basLinkTables-Check Harvest.ini for valid file name"
            HandleUntrappedError ProcName
    End Select
    Resume exit_here
    'Resume
End Function
 
 
Public Function fRefreshLink(strTableName As String) As Boolean
    On Error GoTo Handle_Err
    Const conProcName As String = conModuleName & ".fRefreshLink"
    '--------------------------------------------------------------------------------
    'Created BB on 1/30/2007
    '/ Refreshes the - ONE - link for a the given table. Only to be used for Globals Updates
    '/ This assumes that tblSoftwareUserBE has already been refreshed properly
    '--------------------------------------------------------------------------------
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strdb As String
        Set db = CurrentDb()
 
    '/ Initialize return value
    fRefreshLink = False
 
    '/ Get the back end path from Harvest.ini
    strdb = fGetINIdbPath
 
'  Debug.Print strdb
 
    '/ Loop thru table defs collection
    For Each tdf In db.TableDefs
        '/ Look for TableDefs that have a connection property path and begin with tbl
        If Len(tdf.Connect) > 0 And Left(tdf.Name, 3) = "tbl" Then
'  Debug.Print tdf.Name & " - " & tdf.Connect
            '/ Now look for the given table name
            If tdf.Name = strTableName Then
'  Debug.Print tdf.Name
                tdf.RefreshLink
                fRefreshLink = True          '/ Table found and linked! - set return value
            End If
        End If
    Next tdf
 
    Set tdf = Nothing
    
    '/ If table cannot be linked then fRefreshLink returns False. Relink error handled by calling procedure
    
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            MsgBox "Error: basUtilities - Function fRefreshLink: " & Err.Number & " " & Err.Description, _
                        vbCritical + vbOKOnly, "Harvest error message"
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function
 
 
Public Function fRefreshAllLinks() As Boolean
    On Error GoTo Handle_Err
    'Created by RO on 4/23/2009
    Const conProcName As String = conModuleName & ".fRefreshAllLinks"
    '/ Forces a refresh of ALL table links between FE and BE
    '/ This assumes that tblSoftwareUserBE has already been refreshed properly
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strconn As String             ' first part of tdf.connect connection string
    Dim strdb As String                 ' second part of tdf.connect = path to the backend database
    Dim strConnect As String         ' assembled connection string
    Dim strWrongDB As String        ' name of the backend db incorrectly linked to
    Dim intTableCount As Integer    ' count of all linked application tables
    Dim intBadLinks As Integer       ' count of tables with bad links
    Dim intStillBadLinks As Integer      ' count of corrected links
        Set db = CurrentDb()
 
    '/ Initialize return variable
    fRefreshAllLinks = False
 
    intTableCount = 0
    intBadLinks = 0
    intStillBadLinks = 0
 
  '/ Get the back end path from Harvest.ini
    strdb = fGetINIdbPath
 
    '/ Creat the first part of the connection string
    strconn = "MS Access;PWD=bluebird;DATABASE="
'Debug.Print strdb
 
    '/ Assemble entire connection string
    strConnect = strconn & strdb
'Debug.Print "The INI Path is :  " & strConnect
 
    '/ Loop thru the tables collection
    For Each tdf In db.TableDefs
        '/ Just look at the Harvest application tables (start with tbl)  atbl is ONLY front-end
        ' COMEBACK WHAT ABOUT USYSUBE ?
 
'    Dim db As Database
'    Dim tdf As TableDef
'    Dim tdfs As TableDefs
'        Set db = CurrentDb
'        Set tdfs = db.TableDefs
'
''Loop through the tables collection
'       For Each tdf In tdfs
'        If tdf.SourceTableName <> "" Then                           'If the table source is other than a base table
'            tdf.Connect = ";DATABASE=" & NewPathName     'Set the new source
'            tdf.RefreshLink                                                     'Refresh the link
'        End If
'    Next
 
 
        If Left(tdf.Name, 3) = "tbl" Then
        intTableCount = intTableCount + 1
            '/ Check the table's connect property
            If Len(tdf.Connect) > 0 Then
Debug.Print intTableCount & " " & tdf.Name & " - " & tdf.Connect
            Else
        Debug.Print "BAD FILE    " & tdf.Name & " - " & tdf.Connect
                If tdf.Connect <> strConnect Then
                    intBadLinks = intBadLinks + 1
Debug.Print intBadLinks
                    tdf.Connect = strConnect
                    tdf.RefreshLink
                    '/ Try it again
                    If tdf.Connect <> strConnect Then
                        '/ Name of wrong DB
                        strWrongDB = strConnect
                        intStillBadLinks = intStillBadLinks + 1
Debug.Print " **************" & tdf.Name & " - " & tdf.Connect & " ************"
                    End If
                End If
            End If
        End If
    Next tdf
 
    If intStillBadLinks = 0 Then
        fRefreshAllLinks = True
    Else
        fRefreshAllLinks = False
    End If
 
    Set tdf = Nothing
 
exit_here:
    Exit Function
Handle_Err:
    Select Case Err.Number
       Case Else
            MsgBox "Error: basUtilities - Function fRefreshAllLinks: " & Err.Number & " " & Err.Description, _
                        vbCritical + vbOKOnly, "Harvest error message"
            HandleUntrappedError conProcName
    End Select
    Resume exit_here
    Resume
End Function

Open in new window

0
roverturfAuthor Commented:
BTW -
In this app there is always only ONE back-end db.
0
puppydogbuddyCommented:
Hi roverturf,
You did not say what output the code was producing.  Irregardless, I think I see the problem.  you need  test for a linked connection starting on line 25, otherwise your code won't return an error.  Your modified code is shown below:

    Dim varTable As Variant
    On Error Resume Next
   
    varTable = CurrentDb.TableDefs(strTable).Fields(0).Name
'&&&&&&.add the following code&&&&&&    
   If Len(varTable) = 0 then
      Err = 0
    Else
      Err <> 0
    End If
 '&&&&&&&&&&&&&&&&&&&&&
    If Err <> 0 Then
        IsLinked = False
    Else
        IsLinked = True
    End If
   
exit_here:
0
puppydogbuddyCommented:
roverturf,
<<<<<  I need for the function to force every table tdf.connect path from whatever it was to whatever is in the .ini >>>>>> 

Based on your statement, I think the attached snippet is much shorter and sweeter and will accomplish your objective in a much more intuitive way.  The code is from the Tips page of  www.aadconsulting.com    and in my opinion contains a better approach.
Public Function RefreshLinks(strFilename As String) As Boolean
' Refresh table links to a backend database - strFilename (full path)
' Returns True if successful. 
 
___Dim dbs As Database
___Dim tdf As TableDef
 
___' Loop through all tables in the database.
___Set dbs = CurrentDb
______For Each tdf In dbs.TableDefs
_________' If the table has a connect string, it's a linked table.
_________If Len(tdf.Connect) > 0 Then
____________tdf.Connect = ";DATABASE=" & strFilename
____________Err = 0
____________On Error Resume Next
____________tdf.RefreshLink ' Relink the table.
_______________ If Err <> 0 Then
__________________RefreshLinks = False
__________________Exit Function
_______________ End If
_________End If
______Next tdf
 
___RefreshLinks = True ' Relinking complete.
 
End Function

Open in new window

0
roverturfAuthor Commented:
puppdogbuddy,
Thanks for the code snip.  I think I've narrowed down the problem a bit.  The problem occurs when the app had been in use with the following paths (for example):
FE path:  C:\My App\app.mde      and the BE path:  C:\My Data\MyDataFile.mdb
Here are the problem conditions:  1.) IF the BE connection path is changed in the .ini from    C:\My Data\MyDataFile.mdb  to H:\My Data\MyDataFile.mdb  2.) and the FE app is opened normally  3.) and  the FE's Globals code checks for any new updates to the BE structure 4.)  IF the FE creates a NEW table in the BE,  THEN  the BE tables will now be correctly linked to the new location (H:\My Data\MyDataFile.mdb)  EXCEPT  for the new table, which will be linked to the old currentDB (C:\My Data\MyDataFile.mdb ).
It looks like it is a problem of sequence of events timing.  If currentDB was set to the path from the .ini BEFORE the Globals code runs, the problem should be solved.
What do you think ?  Thanks
-Richard
0
puppydogbuddyCommented:
Which B/E code are you using, the code snippet I gave you from aadconsulting, or the code you got from Andy Barron?

One thing I did notice is that you are using mapped drive letters in your path.  You need to use UNC in your path.  If you use UNC paths, you should be able to avoid the problem with the new table being linked to the old Current Db,  Below is an excerpt from the link I gave you to Albert Kallal's article on splitting your Access db into FE and BE components.

"One last thing about linking the FE to the BE is that you need to always use what is called UNC path names (Universal Naming Convention). All this really means is that you REALLY REALLY REALLY do not want to use mapped drive letters when you link.  Mapped letters are a 20 year throwback to the old PC DOS and CPM days when we used floppy disks! (When is the last time you used a floppy disk). Don't use mapped drives to a file share on the server. Drive mappings have all kinds of problems. Just plugging in a USB jump drive (memory stick) into your computer and the drive mappings can change. Further, you have to setup EACH pc for drive mappings and that is a pain. Further, even plugging in a digital camera can change drive letters. So, just avoid drive letters and mapping like the plague. Simply put, don't use them, and they are horrible from a support point of view. If you do risk using drive letters, then you will get a large increase in support calls for your software.

In other words, always use a path name to the back end files. What this means is when you use the linked table manger, always navigate through network places to the server, and then to the folder, and then the back end mdb file. DO NOT use mapped drives, and do NOT use the linked table manager to navigate to a back end by a drive letter.  If you use a mapped drive, then your path name will be something like:

e:\mydata\backend.mdb

If you navigate by the network places, you will get a path name that does NOT have a drive letter. The path name (UNC) will look like

\\servername\mydata\backend.mdb

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
roverturfAuthor Commented:
The problem happened with either set of code when under the same conditions.  I hadn't considered that a mapped drive could be the culprit, but that is easy enough to change and check.   I'll post back to let you know how that worked out.
Thanks again,
-Richard
0
roverturfAuthor Commented:
puppydogbuddy - Thank you for all of your help.  It turned out that the main problem was using mapped drives.
0
puppydogbuddyCommented:
Richard,
You're welcome. Glad I was able to help you get your problem resolved.  Thanks for the points and grade.

PDB
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.