Access 2000 referential integrity

howste · March 4th, 2002, 01:01 PM

I'm developing a database which is linked to tables in different Access databases. For some fields I need to enforce referential integrity. IOW when I update a field in one table it should update the same field in other tables. Unfortunately, I can't get it to enforce referential integrity unless the tables are in the same database (not linked). When I try it, the options are grayed out (see attached pic).

In reading the help file, I found this info:

Quote:

Both tables must belong to the same Microsoft Access database. If the tables are linked tables, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.

I have tried opening more than one database to set the referential integrity, but it doesn't seem to help. Does this mean that if they are linked tables, they must be linked from the same database?

If I can't get Access to enforce it, I will probably have to create update queries to update the other tables every time a change is made to any of them - what a pain!

ctaylor · March 4th, 2002, 02:06 PM

Sorry to hear about the referential integrity problem - I have never quite gotten referential integrity to work for me as I always wanted to enforce referential integrity in a one-to many scenario and never saw figured out how to implement it other than in a one-to-one relationship.

I will take a look-see on the net and let you know if I find anything.

howste · March 4th, 2002, 05:10 PM

I've been OK with the referential integrity of one-to-many relationships, but they've always been in the same database before.

Rhino · March 4th, 2002, 07:37 PM

Thats what it sounds like to me. Probably a safeguard. Might as well see if its feasable to merge the Databases.

howste · March 4th, 2002, 08:02 PM

Originally they were all in the same database. The reason the databases are separated now is to increase the data space available. Access has a 2GB limit per database, so I have split out some of the tables which will contain graphics and large text files.

If it really can't be done with Access referential integrity, I'll do it with update queries. I'm just trying to avoid it if at all possible.

qball · March 4th, 2002, 09:53 PM

what is 'IOW'? scuse my ignorance.

Anyway, the answer is no, as 'Referential Integrity' enforced by constraints, is only in the scope of the given DB. As it should be.

Now, having said that, many people have enforced RI across DBs, because "they had to", "had no other option", "my boss made me"... They will tell you, as I am, it's a pain in the arse. One accomplishes this through:

Replication
Triggers and Stored procs...

Now, if one uses Replication across DBs to enforce RI, I say what about h-ing Normalization! Replication is normally performance based, but people will do most anything...

Now, if one uses Triggers and Stored procs (or any other mechanism), one will find DB accessiblity will wreak havoc with most attempts to synchronize. If one does not have these problems, they should only have one DB in the first place.

Rhino · March 4th, 2002, 10:53 PM

Are you sure Access has a 2 GB limit? Cuz that's the file limit for FAT 32. If you were using NT or Win2K, XP (in NTFS of course), would that limit increase? Must have a lot of high quality graphics. I mean, I downloaded a database from I forget the site, but it lists like every baseball player since 1990, all of his stats for every year, and it was around 10 megs I think.

vass0922 · March 5th, 2002, 03:22 AM

I know Access97 has the 2GB limit ... not sure of Access2k but would assume so given that MS would love you to buy SQL Server after that limit has been reached.

Howste, given how many DB's you run has your firm considered moving up to sql server?
It'd be tons easier for larger databases, and SP's and triggers are a god send for times like this...
You could still use the Access DB forms against linked tables instead of real ones as well.

Just a thought

qball, Access2k doesn't have triggers and SP does it? Well I guess it has trigger events and modules... but not quite the same.

As for the problem at hand, I've typically tried to avoid using Access if possible unless only for small tasks like quickly manipulating smaller amounts of data so not sure on the RI.

No idea on the IOW

**Socalgal** · March 5th, 2002, 04:57 AM

IOW = in other words..

ctaylor · March 5th, 2002, 10:05 AM

OK, OK, this is off topic:

Beginning with MS Server 7, you are supposed to be able to use utilities to migrate an Access DB into a SQL Server DB. I can tell you that you can migrate with MS SQL Server 6.5, but you need to write large script files to make migration happen.

Another option I have heard of (but not gotten around to trying) is the freeware solution. One person I met at a wedding was using MySQL on Linux with MS Access as his SQL translator. He would write his queries in design view then copy/paste the SQL view of the query into code he was writing. You could also use the MS Access front-end provided you had the ODBC drivers.

As you may imagine, I have not heard of any utilities which would migrate an Access DB into a MySQL or PostgreSQL DB.

EDIT: From what I have heard, MySQL is a faster database for simple queries, but PostgreSQL is more feature rich. There has been a lot written about comparisons of these two databases.

SECOND EDIT: As far as database size limitations, MySQL claims to be a production database for Texas Instruments and NASA. Their website indicates that the size limitation is more OS dependant: (http://www.mysql.com/documentation/mysql/bychapter/)

Linux-Intel 32 bit 2G, 4G or more, depends on Linux version
Linux-Alpha 8T (?)
Solaris 2.5.1 2G (possible 4G with patch)
Solaris 2.6 4G
Solaris 2.7 Intel 4G
Solaris 2.7 ULTRA-SPARC 512G

other information on database solutions may be found here" http://linas.org/linux/db.html