• Front-end/Back-end Links (Access 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Front-end/Back-end Links (Access 2000)

    Author
    Topic
    #385497

    I am preparing a database for run-time distribution on several PCs on a network that has no Access. I’d like to put the front end on the local PCs, linking to a back end on the X: drive on the server. Not rocket science.

    However, I’m developing on a standalone PC, and don’t have an X: drive on my PC. I can’t re-link the table after putting the run-time version on the client’s PC, but don’t seem to be able to change the link on my development PC to dummy up the path to the X: drive before deployment.

    Perhaps it’s obvious that I shouldn’t be able to do this, but what do developers do when they’re preparing databases for deployment on networks with different drive mapping from their development systems?

    I’m competent with VBA, so will resort to code if there isn’t a more obvious solution. Am I missing something, or expecting too much?

    Viewing 2 reply threads
    Author
    Replies
    • #665188

      I usually use a ZIP disk drive to impersonate a network drive when working on home computer. If using WIN XP it’s simple task to change drive letter for removable disk drive, using Computer Management applet (Control Panel, Administrative Tools). No reboot necessary after changing drive letter in XP. If using earlier version of Windows, changing drive letters may be a little more complicated, and may require reboot to take effect. The ZIP disk (or other removable media) should have same folder structure as actual network. I don’t know of good way to impersonate a UNC network path using ZIP drive, but if you have the development version of SQL Server installed (that is, installed on your local PC for development purposes, such as the version installed with VS.NET) you could try renaming the SQL server to match network UNC path (I haven’t attempted this, so no guarantee will work….)

      HTH

    • #665190

      The best way would be to finalize the frontend on a network PC where you can define an X: drive; that way you can test if it actually works before distributing it.

      Otherwise, you can use code to re-link tables when the database is opened. Microsofts Solutions9 sample database contains code to re-link to the Northwind database. You can download it from Sample Applications Download.

      Here is a simplified example that relinks unconditionally; Solutions9.mdb demonstrates a more elaborate, conditional approach.

      Place the following code in a standard module:

      ‘ Private constant contains full backend path1, for use in Set_Links function
      ‘ Substitute the appropriate path and filename

      Private Const strBackend = “X:BackendPathBackend.mdb”

      ‘ Set links; return True if success

      Public Function Set_Links() As Boolean
      Dim tdf As DAO.TableDef

      On Error Resume Next

      ‘ Loop through tables.
      For Each tdf In CurrentDb.TableDefs
      ‘ If Connect property is non-empty, table is linked.
      If Len(tdf.Connect) > 0 Then
      ‘ Set Connect property
      tdf.Connect = “;DATABASE=” & strBackend
      Err.Clear
      tdf.RefreshLink ‘ Try to re-link the table.
      If Err Then
      ‘ Something went wrong
      Set_Links = False
      GoTo ExitHandler
      End If
      End If
      Next tdf
      Set_Links = True ‘ Linking succeeded.

      ExitHandler:
      Set tdf = Nothing
      Exit Function
      End Function

      Call this function from an AutoExec macro, or from the OnLoad event of the startup form of the frontend database:

      Private Sub Form_Load()
      If Set_Links = False Then
      MsgBox “Couldn’t link to backend.”, vbCritical
      Quit
      End If
      End Sub

      • #665198

        A small addition/suggestion to Hans’s code:
        Store the back-end path in a small local table and retrieve it using a DlookUp
        e.g.
        strBackEnd = DLookup(“[BackEndPath]”, “zlSettings”)

        This will allow you to change the back-end path without messing with your code and eases distribution to anywhere else with different drive mappings.
        If you use a front-end .mde file this approach is needed as you cannot amend your code in an mde file.
        The small local table is handy for storing other local info like Version Numbers etc. as well.

        Just a Thought

        • #665199

          Excellent idea – much more flexible and robust. clapping

          (In fact, I often use “settings” tables in my databases. Don’t know why I didn’t think of it here. Probably because it’s one of those days ending in “y”.)

    • #667761

      Using Windows 2000 I am able to ‘map’ one of my partitions to as additional drive letter. For example, I can map my D drive as the P drive and have both of them appear in the Explorer window.

      Access does not get confused when manually linking a table to this bogus drive. It thinks that the table exists on the P drive.

      I’m not sure how the run-time would handle this since I don’t deal with run-time applications.

    Viewing 2 reply threads
    Reply To: Front-end/Back-end Links (Access 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: