Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    104

    Unanswered: Problem With a Split Database and an MDE

    Hi All,
    I have three files that reperesent the Db and the front end, explained as follows:

    Z:\Database\AcordeSIS2002f.mdb - Main Database File
    Z:\Database\AcordeSIS2002f_be.mdb - Database Back End
    Z:\Database\AcordeSIS2002f.mde - Database GUi Front End

    here is the problem: We need to be able to use these files in a networked setting. So for example, these files may as well be located in say \\Machine1\SomeShare\SomeSubFolder\SomeStuff\Db998 324\1A

    The problem is, whenever I open the MDE or MDB file(s) on a networked machine, it will NOT load my reports because it is looking for Z:\Database.

    How Can I overcome this problem?

    In short, I want Access to use RELATIVE paths rather than ABSOLUTE paths


    HELP!

  2. #2
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20
    Look at your linked tables and link them to the machine on the Network (not the drive letter mapping).

    Link your tables to say :

    \\Machine1\SomeShare\SomeSubFolder\SomeStuff\Db998 324\1A


    Maybe look at the VB code behind the reports (or the entire database) to see if there are any hard coded drive letter mappings - worth looking into.

  3. #3
    Join Date
    Nov 2003
    Posts
    104
    Quote Originally Posted by agduga
    Look at your linked tables and link them to the machine on the Network (not the drive letter mapping).

    Link your tables to say :

    \\Machine1\SomeShare\SomeSubFolder\SomeStuff\Db998 324\1A


    Maybe look at the VB code behind the reports (or the entire database) to see if there are any hard coded drive letter mappings - worth looking into.
    Well, That won't solve the problem either. As I mentioned Earlier, the database might be moved from one networked machine to the other, so an absolute path like say \\MachineName\ShareName\SubFolder\DB will not do. I was wondering if you cna ;link the tables into something like "PresentWorkingDir\TableName"

    Can this be done?

    Any and all feedback is well appreciated.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    So basically, from ...\PresentWorkingDir\ on you know the path and it is relative to your mde? Couldn't you read the application.currentdb.name, use mid and instr functions (searching for "PresentWorkingDirectory") to strip off the machine name and sub directories and concatenate this with the \presentworkingdirectory\someotherfolder\database. mdb to get the table path? You could then rebuild your table links using this path, perhaps as an on load event trigered by you switchboard loading up. You could read the existing table links and compare these with what you think the link should be (from your concatenated string) so the rebuilding of the table links is an occaisional event. I think I have the code in a databae somewhere if you would need it. I think I build the tables with DOA though, in case you are using ADO.

  5. #5
    Join Date
    Nov 2003
    Posts
    104
    Quote Originally Posted by pootle flump
    Hi

    So basically, from ...\PresentWorkingDir\ on you know the path and it is relative to your mde? Couldn't you read the application.currentdb.name, use mid and instr functions (searching for "PresentWorkingDirectory") to strip off the machine name and sub directories and concatenate this with the \presentworkingdirectory\someotherfolder\database. mdb to get the table path? You could then rebuild your table links using this path, perhaps as an on load event trigered by you switchboard loading up. You could read the existing table links and compare these with what you think the link should be (from your concatenated string) so the rebuilding of the table links is an occaisional event. I think I have the code in a databae somewhere if you would need it. I think I build the tables with DOA though, in case you are using ADO.
    No you can't! Using Access's Linked Tables Manager ONLY allows u to specify where the back end file is. The problem is, once you specify, it hard codes the file name in!!!!

    Also, I am using DAO not ADO!

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    shassouneh,

    pootle flump gave you the answer. Read his posting again.

    Forget about the Linked Table Manager--it will not do what you want it to do.

    You have to program what the Linked Table Manager does behind the scenes. It's not a big deal--a dozen lines of code and at table.

    As pootle flump said, you are going to have to, programmatically, determine from-where your program is being launched. Then, you are going to have to, programmatically, re-link the tables using the path you have just determined.

    Something like:

    Dim db as Database
    Dim Path as String
    Dim i as Integer
    Dim rs as RecordSet

    Set db=CurrentDB()

    Path=db.name

    For i=1 to Len(Path)
    If Mid$(Path,i,1)="\" Then ExitFor
    Next i

    Path=Left$(Path,i-1)

    Set rs=db.OpenRecordSet(TableListingAttachmentTables)

    Do Unitl rs.EOF
    On Error Resume Next
    DoCmd.DeleteObject ac_Table,rs("TableName")
    On Error GoTo 0
    DoCmd.TransferDatabase . . .
    rs.MoveNext
    Loop
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    What I do is
    create a INI file in the same Folder

    [DATABASE]
    HERE=C:\Event\data\Event Report Datasaw.MDB

    then When The Login load I check the A Table

    By trying to Opening It

    Dim dbs As DataBase, rst As Recordset

    Set dbs = CurrentDb

    ' Open linked table to see if connection information is correct.
    On Error Resume Next
    Set rst = dbs.OpenRecordset("SETUP")

    ' If there's no error, return True.
    If err = 0 Then
    Else
    Do_The_Link '<= this is the hard bit
    End if


    ==========================

    Sub Do_The_Link()
    Dim NewPath As String
    Dim DataBaseName1 As String
    Dim DataBaseName2 As String
    Dim server As String
    MsgBox ("HAVE TO RELINK TABLE" & vbNewLine & "PLEASE WAIT")
    DataBaseName1 = LoadIni("DATABASE", "HERE")
    RefreshLinks DataBaseName1, NewPath, "SETUP"
    RefreshLinks DataBaseName1, NewPath, "COST"
    RefreshLinks DataBaseName1, NewPath, "EVENT"

    LIST ALL YOU TABLE
    ...
    ...
    ...


    MsgBox ("All Table ReLink")

    End Sub

    Function RefreshLinks(DataBaseName As String,TableName As String) As Boolean
    ' Refresh links to the supplied database. Return 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 UCase(tdf.Name) = UCase(TableName) Then
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = ";DATABASE=" & DataBaseName
    err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    Exit For
    If err <> 0 Then
    RefreshLinks = False
    Exit Function
    End If
    End If
    End If
    Next tdf

    RefreshLinks = True ' Relinking complete.

    End Function

    done

    It works for me When I'm testing @ home it looks at the INI
    the Relinks All the Tables to the right place

    When I'm back @ work it relink then again. back to the work DATABASE
    Last edited by myle; 05-06-04 at 00:05.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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