Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Recreating ancient database

    Hello,

    I have taken on the challenge of building a new Access DB to replace our outdated, glitchy one from 2001. However, the old one was constructed such that each table is in its own DB, thus no relationships. So I have a "Jobs" table in it's own DB, and a "Contacts" table in its own DB, but no way to figure out which Job goes with which Contact. ALL the tables are like this. Yikes.

    I can only assume that there is some external programming that links these but for the life of me I can't find it. I'm not a programmer, just have some basic DB knowledge. Can you think of any way that I can recover/export/etc. these relationships?

    Lastly, reporting was done with Crystal but I can't open or even view any of the reports because the version is too old. Could the connections be hidden in there somehow?

    Thanks!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The first order of business would be to either establish a relationship with a good mental health counselor or with a liquor store that delivers!

    Seriously, the reason that over a hundred members have looked at this, without responding, is that there is simply no way for anyone (except, possibly, the original perpetrator of this disaster) to answer your questions! The very fact that the developer placed each Table in its own Access file shows that he or she had absolutely no idea as to what they were doing!

    The mechanism to connect one Table to the other wouldn't be in the Crystal Reports, I wouldn't think, but is more likely to be in yet another Access file! There's probably a Table in there that connects the other two Tables. Unless you can locate this file, and even if you do, I suspect that you're going to have to simply Import all Tables into a new, blank database file and proceed from there. If you cannot find this third file, you're going to have to resort to using data from other sources, within your organization, to connect the two Tables. At a minimum, you'll need an ContactID field as well as a JobID field, and use them to relate one Table to the other.

    I have to tell you, with over two decades of developing databases, half of which has been using Access, that if asked to tackle this problem, I'd probably pass unless your company was willing to agree to a flat hourly rate agreement, because there is simply no way to estimate how much time something like this could take! And to be honest, it's really unfair of the powers that be to expect anyone who is not a programmer, just a DB user, to tackle something like this!

    Strategy-wise, starting on the machine holding the two, known files, I would run a search for files ending with .mdb and .mde, trying to find any other existing Access files. If you come up with .mde files, it is absolutely imperative that you find the corresponding .mdb files, because you cannot get 'inside' of the .mde file's design to see what's going on.

    As for the Reports, I think you'll need to recreate those, using Access. Hopefully you can find hard copies, but finding a machine still loaded with the original CR version and still using an OS that supports it would be better. I know of no product that can be used to automatically port Crystal Reports into Access. You might ask around about where old PCs from your company go to die; many times when machines are 'retired' they end up in the homes of employees.

    Sorry that I can't be of more help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Linq - Thanks so much for the detailed reply. I am afraid this might be over my head. It's not a paid thing - was just looking for ways to make my department more efficient. I ran the searches you suggested to no avail, but I thank you very much for taking the time to reply.
    Cheers,
    Michelle

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry I couldn't be of more help, but given the facts, simply developing a new Database, possibly saving/using one or both of the existing Tables, is the only practical way to go!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you have found all the tables
    I'd suggest you merge them into one DB
    then examine the column definition for each table

    look for columns of the same type and similar names
    then open each table and check to see if the data is the same, if sdo there is a high probability that those columns are a PK/FK relationship
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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