Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: Linked table relationships not copied over from password-protected database

    When I try to link all the tables from an Access2000 database with database password protection into a new Access2000 database, after entering the password all the tables are copied over fine, but the relationships are not. (I have used Select All in the Link Tables dialog).

    Is this a bug, and/or is there any quick way round it other than to recreate the relationships manually?

  2. #2
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    Exclamation

    Are you linking the tables to a new front end or are you importing them to a new db?

    Regards,
    Regards,



    John A

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    Thanks for your reply.

    I started off developing two databases, one with all the data in and one with the input forms (I presume this is what you call the front end). I linked the data tables from the 'data' database into this front-end. I didn't put any password protection on either database.

    Now I have completed the 'application', and decided to put password protection on both databases. Using the Link Manager to refresh the links in the front-end after setting the password in the back-end won't work, so I had to remove all the tables in the front-end and relink them, setting the password in the front-end at the same time. It all works OK, the data is pulled through etc. The only thing is that the relationships have not copied over as they would normally. I presume that this will make all the input form queries run much slower?

    I did try this out linking the data tables from the password-protected database into a new database, but it doesn't make any difference, it still doesn't copy over the relationships.

  4. #4
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    Relationships

    LouLou,

    The relationships will be in the BE database (that is the one with all the tables) if they are not there the open the BE database, Go to File /Get External Data/Import and point to your original db that had the tables and the relationships in, when you find it click import and THEN;

    Select Tables, and then OPTIONS and make sure that the Relationships check box is ticked. Then click OK and this should import your relationships. This is all assuming that you have not added or changed the NEW database that would change the set up of the original relationships.

    In you front end you can do the same to import any Menu's and Toolbars that you created in the original db.

    Hope this helps, I am no expert at Access and anyone else is free to pick me up on any errors I have made.

    HTH

    Regards,



    John A

  5. #5
    Join Date
    Jun 2003
    Posts
    5
    Thanks for your quick reply.

    Unfortunately, I am not importing the tables (which does have the option to copy over the relationships), but linking them (using File>Get External Data>Link tables). I am not given the Options button that exists in the Import dialog.

    The reason I am not importing the tables to the front-end is that the back-end database is the data store for the content of an active server page web site, and I want to keep it free of input forms/queries to keep its size as small as possible.

    What I can't understand is that if I don't password-protect the backend database the relationships do automatically copy over, but when I do password protect it, they don't.

  6. #6
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    relationships

    LouLou,

    Now I see what you are doing I understand but sorry I cannot offer any explanation. I have severl db's with BE and FE and yes you are correct FE have a copy of the BE relationships.

    Sorry I can't be any more help.


    Regards



    Regards,



    John A

  7. #7
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Since you're LINKING the tables, the relationships remain in force in the back-end.

    It's probably just a 'display' thing. You can't establish, modify or delete relationships from the front-end (unless you open a connection to the BE and use DAO or ADOX to make changes, which is actually taking over the back end). They belong to the tables. So there is no meaning in "importing" the relationships. It's like the division by zero.

    Don't worry, even though they are not displayed, they are present. Try to violate some referential integrity and you'll see.

    But of course, presuming the relationships exist in the back end. They may not exist if you exported the tables to the back end. If you did that, here is how to split a database:

    1. Make a backup copy of the 'monolyth' db
    2. Open a new database
    3. Link the tables from the 'monolyth' db
    4. Import all queries, forms, reports, macros and modules from the 'monolyth' db into the new db
    5. Delete all queries, forms, reports, macros and modules from the 'monolyth' db (don't touch the tables)
    6. Open the new db.

    If you did anything wrong, get the backup copy and try again.

    And if you don't have the original database anymore, you'll have to re-create the relationships and consider backing up your files on a regular basis.

    HTH

  8. #8
    Join Date
    Jun 2003
    Posts
    5
    Thanks for your advice;

    I'm not sure it's just a display thing however, as when I run Performance Analyzer and select the current database to optimize, it comes up with a whole list of optimization 'ideas', one for each table, telling me to relate each one to others in the database.

    Is it not true that all the queries I have in the front-end database will run faster if the relationships are established already?

  9. #9
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Originally posted by LouLou
    Thanks for your advice;

    I'm not sure it's just a display thing however, as when I run Performance Analyzer and select the current database to optimize, it comes up with a whole list of optimization 'ideas', one for each table, telling me to relate each one to others in the database.

    Is it not true that all the queries I have in the front-end database will run faster if the relationships are established already?
    Then you must have exported the tables from your original database. Please read my post. The order of actions to take in splitting the db is very important. Otherwise you may reach a dead end...

    I don't think queries will run faster or slower...The queries are usually made by joining (relating) tables. The point in relationships is to enforce referential integrity so that you have good data at all times and eliminate the risk of entering redundant/orphan information

    HTH

  10. #10
    Join Date
    Jun 2003
    Posts
    5
    Sorry, I am confused by your reply;
    I didn't export any tables to the BE. I created the BE first, complete with relationships. Then I created the FE for form viewing and form input, linking the tables from the BE from within the FE. That's all.

Posting Permissions

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