Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Help with Access Database transfer

    Hello,

    I have a database that is a .accdr (Runtime application) file type. I have it on my work server and use it on my work computer (same location). I also have a laptop that I use for off site meetings that is not connected to the server when I go to the meetings.

    I use the database on my work computer and my laptop, but I need to know how to access the database on my laptop when I am not connected to the server.

    After I return to the office I need to be able to update the database file with the server's file.

    Whenever I try and copy and paste the .accdr file from my server to the laptop it will reference the server file location and open when I have access to the server, but when there is no access to the server then the file will not open.

    Hopefully there is an easy fix and my lack of Access knowledge is to blame!

    Thanks.

    Edit: As of right now I can only Cut and then Paste the file back and forth to get it to ask where the database is that is being referenced, so I will do that.
    Last edited by rogerpenske; 08-31-11 at 12:36.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I understand correctly what you explain, you only want to copy the tables from the server to a local database, never to update the server from the contents of local tables.

    If so, here's a solution:

    * When you want to create a local copy of the tables that are stored on the server and use them with the front-end, the following mechanism is used:

    1. Store the connection infos for the linked tables into a local table (this table can be created if necessary).

    2. Create a local back-end database that will have the same name as the front-end with "_LBE" appended to it. (e.g. if the name of the front-end database is: FrontEnd.mdb, the name of the local back-end will be FrontEnd_LBE.mdb). This local back-end is located in the same folder as the front-end. If a file with the same name already exists, it will be deleted first.

    3. Rename all linked tables referenced in step 1 by adding an underscore character (_) in front of their name.

    4. For all linked tables referenced in step 1, create a linked table with the same name, but linked to the local back-end.

    * When you want to relink the linked tables to the server, the following mechanism is used:

    5. Delete all tables referenced in step 1 that are linked to the local backend database (see step 4).

    6. Rename the tables with an underscore character in front of their name (see step 3) and refresh their link to the server.

    All the necessary functions are provided as methods of a class Cls_LinkedTableManager (provided in the attached file: its too long to be listed here). Import it into the front-end to use it

    Here is the code to create the local back-end and work with it:
    Code:
    Function LinkToLocalBackEnd()
    
    ' TO DO: Add a proper error handler (all methods of Cls_LinkedTableManager return True
    ' -----  when they succeed, any other returned value is an error code, except for
    '        Exists_Tbl_LinkedTables which returns False (0) if the table does not exist).
    '
        Dim clsLinkedTableManager As Cls_LinkedTableManager
        
        Set clsLinkedTableManager = New Cls_LinkedTableManager
        With clsLinkedTableManager
            If .Exists_Tbl_LinkedTables = False Then
                If .Create_Tbl_LinkedTables <> True Then End
            End If
            If .StoreLinkedTableInfo = True Then
                If .CreateLocalBackend = True Then
                    .LinkTablesToLocalBackend
                End If
            End If
        End With
        Set clsLinkedTableManager = Nothing
        
    End Function
    And here is the code to re-attach the front-end to the server:
    Code:
    Function LinkToServer()
    
    ' TO DO: Add a proper error handler (all methods of Cls_LinkedTableManager return True
    ' -----  when they succeed, any other returned value is an error code, except for
    '        Exists_Tbl_LinkedTables which returns False (0) if the table does not exist).
    '
        Dim clsLinkedTableManager As Cls_LinkedTableManager
        
        Set clsLinkedTableManager = New Cls_LinkedTableManager
        With clsLinkedTableManager
            If .Exists_Tbl_LinkedTables = True Then .LinkTablesToServer
        End With
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Forgot to attached the file for the class. It's a VBA module in plain text, by the way. You can open it and examine the code.
    Attached Files Attached Files
    Have a nice day!

Posting Permissions

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