Access Indeterminate relationship in many-to-many table (Windows 10, Access 2016)

S

Sue Basker

Joined
Jul 24, 2016
Messages
3
Reaction score
2
I have created many-to-many relationships many times before and am trouble-shooting a complex database I created in Access 2007 and recently upgraded to Access 2016.

I have 3 tables named tblPeople, tblAddresses and tblPeopleAddresses which are used to log people where people live, who they live with and past addresses.

The primary key in tblPeople is PersonID (Autonumber, indexed No Duplicates).
The primary key in tblAddresses is AddressID (Autonumber, indexed No Duplicates).
The secondary keys in tblPeopleAddresses are:
  • PersonID (Long integer, indexed Duplicates OK)
  • AddressID (Long Integer, indexed DuplicatesOK).
The relationship between the two AddressID fields is still OK as a 1 to many, but the relationship between the two PersonID fields has disappeared and Access won't allow me to set up a 1 to many but just gives me 'indeterminate relationship'.

This has also happened with some other 1 to many relationships in the database. Compact and repair doesn't solve it. Has anyone any idea what's wrong?
Thanks
 
Hi Sue welcome to PCR enjoy, sorry I cannot help but I am sure some one else will be able to help, but nice to have you aboard.
 
Thanks! I've investigated further and find that in the back end database, some ID fields have lost their ability to specify the type of number: they have all turned from Long Integer to Integer and when I drop down the combo box to select Long Integer, it's completely empty.

I've tried creating a new table with the same fields (copyiing and pasting all the fields except the one with the problem) and I can then select Long Integer - in fact, it's automatically selected on creation, as it usualy is on ID fields.

So I can't think of any other way to salvage my 2 years' work - it's a nuisance as I shall have to import all the data into the new tables and try to retain the original autonumbers of the records in the original table. Anyone got a better idea?
 
I am sorry to hear that Sue, in future I think you need to back up on an external drive all your files that way should this happen again you will be able to restore them. I would recommend a 500 GB or greater external drive, but you should be get one at a price you can afford. If you are not sure how to back up to an external drive come back to us and someone will help you. You could also ask if anyone on PCR has a spare external drive they would be willing to sell one to you at a reasonable price. If you are not sure how to back up please ask as someone will help you. It would help if you could also fill in your system specs and operating system Windows XP, Vista, Windows 7, Windows 8.1 or Windows 10, Or Linux Distro and whether you are running 32bit or 64bit. If you are not sure if sure let us know what the Make and the age of the PC any of that will help.
 
Thanks again. I actually make backups both locally every few minutes and to OneDrive a couple of times a day, but the problem has arisen after the upgrade, I'm afraid, and importing the old data still has the same effect. I'm not working on the server yet as a new hardware/software system is in the process of installation (hence the upgrade of Access on my own machines).

At least I've now discovered a quicker way of putting it right - if I create a new ID field in the existing table it is automatically assigned to a Long Integer as usual; so I'm doing that, renaming the corrupted field and updating its data into the new field, then deleting the old ID field and renaming the new one to match the old one's name. If that makes sense!

I think Microsoft need to know about this. It's not the first glitch I've had with Office 2016. Others might find this workaround useful too.
 
Thanks for your reply, I am glad you have found a work-a-round:thumb:I am now using Linux because of the glitches I had with Microsoft and the fact I did not like Windows 10
 
Back
Top