Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to split a database into DATA and FRONTEND (2003)

    Hi there,

    yes I do know the menu point in Access Extras>...>Split Database. That works great but here comes my problem:

    I'm kinda like the administrator of an Access Database and never change the data but the frontend (normal situation I guess). The database is spreaded to other computers BUT IN DIFFERENT FOLDERS. This is where the problem comes in!
    Access stores static paths instead of relative paths just like MyDatabaseFrontend.mdb and Data/MyDatabaseData.mdb instead of C:MyData....

    Now how can I tell either Access to store relative paths (since the folder structure will always be the same) OR how do I make like an install program to change those settings for the Data-Database on the fly on the first start or so?

    ----------------------------------

    Problem 2: I cannot create MDE Files (if you wanna point me into that direction). I converted my database into Access 2003 Format as I was supposed to and then after wanting to create it just says "Cannot create MDE File" with no real reasons besides the standard reasons for too many Indexes etc. The database itself is like 2MB so you can figure that it aint that big and not soooo many indexes...


    Hope you can help me here guys

    Dennis

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

    Re: How to split a database into DATA and FRONTEND (2003)

    There is no way to tell Access to store relative paths. It always stores full paths.

    Do these different databases on different machines each have their own data or are they all sharing a common backend? If they share data then it should be possible to find a common description of where the data is.

    If they each have their own, then you will need a big slab of code to relink the tables.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How to split a database into DATA and FRONTEND (2003)

    Do you mean that the path to the back-end is different on each workstation? If so, you may want to relink the tables each time you deploy a new front-end, or you should use a UNC connection in the form of ServerShareNameFolderNameBack-End.mdb. If on the other hand, you mean the front-end is stored in a different path on each workstation, that is less of a problem, as you simply need to deploy to several different paths - and there are tools that will help you do that if it becomes unmanageable.

    As to making MDEs, you can only make an MDE in the format that the database was created in. That is, if you are using Access 2003, you can only create MDEs for databases that are in 2003 format. Similarly if you are using Access 2000, you can only make MDE files for databases that are stored in 2000 format. Hope this is your problem.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to split a database into DATA and FRONTEND

    Ok no relative paths i'm fine with that...

    Well i'm not working in a network so that kinda makes the problem harder. The data in the tables is always the same. Basically there's one guy working with the database in City XY and I'm just solving problems with the FrontEnd in City ABC, so all I wanna do is to send out a CD, the guy has to put it into the drive and install the files (or unpack them whatever) and the problem is solved withouth destroying the data.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to split a database into DATA and FRONTEND

    It would be nice if you could name me some of this tools, because I think that is exactly what I need!

    The situation in short again: ME = Programmer sitting in City ABC. USER sitting in City XYZ. No Network between us. USER has a problem, I solve it put the database on a CD (the frontend) and he can just overwrite it, install it, unpack it or whatever, but his data does not get touched.

    MDE: Yeah thats what access tell me to, thats why I had to convert the database into 2003 format - which I did... I don't need MDE's so thats fine, I just wanted to see how it works and what I'M doing wrong.

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

    Re: How to split a database into DATA and FRONTEND

    I attach a zip file with a demo relink utility I wrote for myself a while ago, for situations like yours.
    ( I think I based it code I found at the Access Web and some stuff in a book by Alison Balter.)

    It uses an autoexec macro to run a refreshlinks function.

    1 Check if links are OK
    2 If not, Look for a file in the same folder as the frontend, with filename frontendfilename & "data.mdb", and link to that.
    3 If that was not sucessful, prompt the user to locate the data file themselves.

    * My code assumes that all table names begin with tbl.

    * code requires references to ADO Ext for DDL and Security and Microsoft Office Library

    This might itself create other problems. I have Ver 2.8 ADO Ext for DDL and Security, while a machine I regularly install on only has 2.7, so I need to fix the references each time. ( I need some code to do this!)
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How to split a database into DATA and FRONTEND

    It looks like <!profile=jacksonmacd>jacksonmacd<!/profile> and <!profile=johnhutchison>johnhutchison<!/profile> have pretty well dealt with the issue - I think the Access Developers Handbook also includes code to do this sort of dynamic table relinking. Good luck.

    postscript <font color=blue> I just bumped into a thread that starts with <post#=404740>post 404740</post#> which might be some additional useful into about drive mapping - that would be another way of solving your problem.</font color=blue>
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to split a database into DATA and FRONTEND

    Yes everything was very helpful so thanks to y'all!

    As I can see some solution like that involves alot of coding... Is there any other way to maybe leave the database as a whole and update just the forms and not the tables?

    This problem must be something I'm not the first to experience - Leave the data but change the structure of the Interface!

    Sorry for the delayed answer I only have access to a real computer on the weekend, my cellphone serves as my computer for the rest of the week - no time.....

    Dennis

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to split a database into DATA and FRONTEND

    <P ID="edit" class=small>(Edited by WendellB on 29-Oct-04 13:21. activate url link)</P>Solution #1 - enforce a common folder structure (may not be acceptable in your situation)

    Solution #2 - see http://www.mvps.org/access/tables/tbl0009.htm for a starting place. For my own applications, I have a procedure similar to this on that first tests for a valid link to a nonimated table in the BE. If it fails, then look in the folder containing the FE for the BE file, and relink the tables. If the BE is not found in the same folder as the FE, then ask the user for its location.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How to split a database into DATA and FRONTEND

    Unfortunately, the solution you have is the best solution I can suggest. If the computers aren't on a LAN, or at least a WAN, then you have only two real choices - the one you are using, and replication. In my view replication works better for data than for design changes to forms, reports and queries, and replication introduces several issues with how the syncronization must occur. On LANs, we use a deployment manager that we have created, and it works quite well.
    Wendell

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

    Re: How to split a database into DATA and FRONTEND

    You are certainly right that you are not the first person to have this problem.

    I often try to recreate on my machine the same directory as the target machine , and have a standard install location for a db that is being installed on multiple machines. If it is to mapped network drive, I use the dos command subst to temporarily create the same drive on my machine.

    If you are only installing in a small number of locations, a no code solution is to the teach the other users to use the Linked Table Manager themselves to locate the data file.
    Regards
    John



Posting Permissions

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