Unenforced relationships in Access --> SQL Server (1 Viewer)

[bhavdahl]

Sorry this is kind of a crappy title. I think I'm basically wondering what the affect of removing unenforced relationships that exist in an Access database might be? ...what potential problems I'm looking at by doing it basically.

I'm moving the tables from an Access database to SQL Server, where to set up these relationships they would have to be enforced and can't be because of the data. So if I blow them off in order to move the data into the SQL Server tables, I'm wondering how bad of an idea that is.

I'm sort of stuck, but wondering what kind of ideas others would have about the situation. Appreciate any comments at all. Thanks!

 

[Banana]

Normally, when I have linked tables, I don't bother to use relationships since it's best that server handle the relationship rules on their side without complicating by doing it on both side (and let's not even think about the inconsistency between each server's relationship rules!). So I see nothing wrong with deleting the relationship so you can migrate to SQL Server, *providing* that you replace the relationship once it's all settled in SQL Server and *before* anyone is allowed to edit it.

 

[bhavdahl]

Thanks for the thought. That's sort of the trouble though, replacing that kind of relationship in SQL Server. The problem is there are relationships set up in the Access backend that have Enforce Referential Integrety turned off creating an unenforced relationship. Several years of data now exist with duplications and nulls in the fields used for those relationships. The fields would have to be set up as keys in SQL Server in order to create any relationship on them and they can’t be.

So my question is then, with referential integrety turned off, does a relationship really do anything in the first place? Is there any point in having it? Cause SQL Server won’t let me create one anyway.

I may have to blow off putting the database in SQL Server, but that’s not very good either. Again, thanks for any comments or help. I really appreciate it!

 

[Banana]

Aha. A relationship without referential integrity, IMHO, isn't really a relationship.. It's more like a "hint" to Access how to join the tables.

Either you would have to clean the duplicates and nulls out or leave them as it is.

Another alternative is just to import them into archive tables for old data, while limiting the cleanup efforts to only active data then you can enforce relationship for now and future. You can then write some kind of scripts to import the data from archive table into the new table if they get "re-activated" on a go-by basis.

 

[bhavdahl]

Thanks! That was my thought too. That they weren't really doing anything. There are a lot of them though, with multiple keys that aren't really keys. So I was hesitant to leave them all out. But I think I really have no choice.

I can't really do much data clean up. For one table, 75% of the records had those problems. The database exists to maintain old data in the first place. So the activity between and among it is too hard to predict to figure out what to archive.

But the archive idea and going forward with a new structure really does makes sense. I'll keep that in mind for the future. I'd really like to create relationships and enforce them. I'm sure it's just asking for problems down the road, but I think it is what it is in this case. They've been using the database this way for years and it works ok with what they have. ...I guess.

I just want to get the back end out of Access so the front end won't get screwed up again. Somebody moved the back end around a couple times in the past and broke all the queries, etc. I had to fix them all once and I don't want to do it again. There were almost 300 queries, 200 forms and about 100 reports to look through for problems. ...75 tables too.

Anyway, thanks for the help!