Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Development Vs. Production Paths (2003)

    To all,
    I'm developing a small database for work. One basic problem is different paths on the production vs. development machine. I read that Access only uses full paths. I've split the database in FE and BE. Is it possible to tell FE to call on data up one level from the current folder, regardless of the full path of the FE?
    Thanks in advance.
    Mark

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

    Re: Development Vs. Production Paths (2003)

    You could re-link the tables in code when the database is opened. Here is a function that will re-link tables:

    <img src=/w3timages/blueline.gif width=33% height=2>
    <code>
    Public Function Set_Links() As Boolean
    Dim tdf As DAO.TableDef
    Dim strBackend As String

    On Error Resume Next

    ' Substitute name of backend database
    strBackend = CurrentProject.Path & "..Backend.mdb"

    ' 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
    </code>
    <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:
    <code>
    Private Sub Form_Load()
    If Set_Links = False Then
    MsgBox "Couldn't link to backend.", vbCritical
    Quit
    End If
    End Sub</code>

  3. #3
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Hans,
    Thank you. I'll give it a try.

    Mark

  4. #4
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Hans,
    I tried including the code as a class module. Then I created an autoexec macro that called the function set_links
    When I tried opening the FE database by double-clicking, Access stops with the cursor at the line "Option Compare Database" in my set_links module.
    Why do you think this happened?
    Here's the first few lines of the module. I added the Option Explicit statement just to be safe, but it looks like Access stops at the line above.

    Option Compare Database
    Option Explicit
    ' Linking function for tables in database
    Public Function Set_Links() As Boolean
    Dim tdf As DAO.TableDef
    Dim strBackend As String

    Thanks for your help.
    Mark

  5. #5
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Hans,
    Further work with paradoxical results.
    In the AutoExec macro, I changed the action to RunCode and I now get the error message 'The expression you entered has a function name that Microsoft Office Access can't find.' When I click OK, I get a message box like this:
    Macro Name:
    AutoExec
    Condition:
    True
    Action Name:
    RunCode
    Arguments:
    Set_Links()
    Only the Halt option is enabled.
    Why doesn't Access see the function? Should there be another action selected in the AutoExec macro?
    Thanks again.
    Mark

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Development Vs. Production Paths (2003)

    Your module cannot have the same name as the function. That is guaranteed to break.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Charlotte,
    Thanks. That enabled me to run the program. It seems to take longer to load Access on my desktop. When I try opening the FE form, I get the same error as weithout Hans's code - ".... is not a valid path. Make sure that the path name is spelled correctly etc. "
    Hans or Charlotte or anyone else - any thoughts on what could be wrong. The code I'm running is cut and paste from Hans's previous posting.
    TIA
    Mark

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Development Vs. Production Paths (2003)

    Did you change this line to the correct name for your backend?

    strBackend = CurrentProject.Path & "..Backend.mdb"
    Regards
    John



  9. #9
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Yes, I did. Unfortunately, I haven't had a chance to follow up any other approaches. Maybe stepping through the code might help. Any other suggestions?
    Mark

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

    Re: Development Vs. Production Paths (2003)

    I have tested the code, it worked OK on a backend/frontend combination in which the backend resided in the parent folder of the folder containing the frontend.

    If you want to single-step, you should temporarily make the line

    On Error Resume Next

    into a comment by inserting an apostrophe <code>'</code> in front of it. The code should raise an error at some point. You can inspect the value of variables by hovering the mouse pointer over them in the code, perhaps this'll give you a clue.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Development Vs. Production Paths (2003)

    Hans,
    It works! You really helped when you mentioned the specific paths needed. I had forgotten my own post, where I specified that the BE database be one level higher than the FE database. Putting the files in the two separate folders was the final touch to get the code to run. Ugh, it's really hard doing this as an extra to my main job. Maybe I'll stay more focused in the future.

    Mark

Posting Permissions

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