Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

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

    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?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Front-end/Back-end Links (Access 2000)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Front-end/Back-end Links (Access 2000)

    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:

    <img src=/w3timages/blueline.gif width=33% height=2>

    ' 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

    <img src=/w3timages/blueline.gif width=33% height=2>

    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

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front-end/Back-end Links (Access 2000)

    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
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Front-end/Back-end Links (Access 2000)

    Excellent idea - much more flexible and robust. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    (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".)

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front-end/Back-end Links (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •