Results 1 to 6 of 6
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Converting selected linked tables break referential initegrity

    We've split this database that I've been posting about for the usual reasons. I was hoping the wizard would let me select the tables to put in the back end, but it split the database cleanly into FE and BE. The problem is I would like to keep the lookup tables in the front end so they can be updated and redistributed to remote sites. I thought I could convert these tables to local tables, but Access barked at me. See below.



    Click image for larger version. 

Name:	local - linked-tables.JPG 
Views:	12 
Size:	29.9 KB 
ID:	28173

    I wasn't willing to cross that bridge. The relationships between these tables are well documented in the relationship window. The table designs leave a little bit to be desired, including lookup fields, calculated fields lots of column aliases(which I don't like) and are not completely normalized, but overall the database works pretty well. See the relationship window below. Most of the lookup tables are on the periphery and end in Types or Reason. You should be able to pick them out. These are the tables I'd like to make local. Am I losing more that I stand to gain? What do you suggest. I am attaching a copy of the database I previously uploaded.

    PSH Outcomes FrontEnd v0.1.zip

    Click image for larger version. 

Name:	relationships.jpg 
Views:	12 
Size:	83.5 KB 
ID:	28174

    In my time zone it's almost 5 o'clock. I may not be able to respond to your replies till Monday morning. I wanted to get all this uploaded to you before I left for the day.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is not possible to maintain referential integrity between tables in different databases as the message indicates. But you can include vba code to mimic the same principle e.g.

    Code:
    if isnull(dlookup("FKField","remoteTable","FKField=" & PKField)) then
        'do not delete local record
    but the principle of using local lookup tables is they don't change - and if they do then it is to add something rather than to delete something or to correct a spelling.

    Personally, I'm not in favour of local tables unless they are never ever going to change (like months in the year) or are only for the use of the local user - if a user changes something or adds a new record, you have the task of updating all the other users front ends with the new data.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    That is it. These tables rarely change, "like months in the year" or maybe longer and would likely be only edited or added to. Since these users are at remote locations, they do not have access to the same back end SQL Server or file servers. When we push out a new front end we want them automatically to get the latest updated lookup tables. The back end also resides at their location and is zipped up and FTP'd to the central office where the data is consolidated and analyzed. We can lock up the Front End tables enough to keep them from meddling, which they would have no reason to do. They simply key data.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In that case I would maintain the tables on sql server and when you push out a new front end, include copies as local tables - or have a routine when the front end is opened to refresh the tables. But doesn't change the fact you can't maintain referential integrity between separate databases. If the local backend is sql server, I would also consider performance issues - queries will generally be faster if all the tables are in the same db and you take advantage of using stored procedures etc to run on the server.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If they're related and almost never change, then why aren't they going into the BE anyway? The fact that they are 'lookup' tables for things that don't change (like a status value for something) isn't a good reason for keeping them local, IMHO. The only type of tables I can think of (at the moment) that I'd keep in an FE is temp.
    If I need to provide a user specific temporary transaction, that table likely goes into the FE, otherwise I have to distinguish between one user and another in the BE lest one wipe out data that someone else is working on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    We can't use SQL Server for a back end. These are independent agencies and don't have to access to our servers. If we could, we would like to have a single back end that everyone could link to, but it's not an option. They each will have their own back end that we can't update from here. If the lookup tables were in the front end we would push that front end out to the agencies to replace their existing front ends.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Referential Integrity On Linked Tables
    By ribena1980 in forum Import/Export Data
    Replies: 4
    Last Post: 08-25-2015, 11:56 AM
  2. Tables - can you break up data too much?
    By Reviewer in forum Access
    Replies: 5
    Last Post: 08-22-2015, 02:26 PM
  3. Why break tables up?
    By Canadiangal in forum Database Design
    Replies: 7
    Last Post: 01-11-2013, 01:56 PM
  4. converting a Delimited field to a linked table
    By Dozza111 in forum Import/Export Data
    Replies: 6
    Last Post: 05-16-2012, 04:46 AM
  5. Converting/Upsizing Linked Databases
    By Chaz88 in forum Import/Export Data
    Replies: 2
    Last Post: 08-11-2010, 06:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums