You are here:

Using MS Access/How to use multiple backends

Advertisement


Question
file sizes
file sizes  
My Access DB has grown beyond front and back to 5 back ends, 2 or three back ends are over a gigabyte. Original structure was all normal, all cascade up and down, all referential integrity except for a few stand along tables. I find no way to restructure and maintain RI or Cascade in  multiple backends. This makes years of work falling apart. One table has 485,000 part numbers for my business, so you get the idea. I believe it's over 200 tables, I don't even try to count them. The 2 gig limit was reached some time ago, but at that time I was able to split backend into sections that remained almost modular and had only one relation or so between them. this is no longer the case.

Answer
I think you can only enforce RI within an MDB. However you can enforce it by code from your front end. I actually rarely use RI or cascading.

But I think you may need to look into moving your backend to SQL Server. That's really the only other solution I can offer.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

I can't give you code samples without knowing your structure. To enforce RI in code, you have to define what part of RI you want to enforce. There are three parts, orphan control, cascade deletes and cascade updates. To code orphan control you have to check, when you add a record in a child table, whether a corresponding record exists in the parent. You can usually do this with a DLookup in the After Update event of the key field. However, you can usually assure orphan control by adding records through a linked subform which automatically populates the foreign key.

For Cascade deletes you can use run a DELETE query that deletes child records that have the same foreign key as the parent record being deleted. Similarly for Cascade Updates, you run an Update query, again using a Where clause to match the FK to the parent.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.