Can't setup Referential Integrity

[lwoodsusa]

I created a DB with two tables:

Property table

     propID     autonumber primary key

     address    short text

     city           short text

     state         short text

Residents table

    resID        autonumber  primary key

    propID     large number

   nameLast  short text

   nameFirst short text

I setup a one-many relationship between propID (Property table) and propID (Residents table).

BUT when I check the Referential Integrity checkbox in the dialog on the Relationships page, I get the following error "Relationship must be on the same number of fields with the same data type."

Both "propID" fields ARE the same data type.  

How do you set up Referential Integrity  (I never had this problem with earlier versions of Access)

[#2]

@lwoodsusa 

Your post shows that the field called propID in the Residents table is a "large number". Large Number is a different data type from "Long Integer". The AutoNumber propID in the Property table is a Long Integer, not a Large Number. Change the datatype to Long Integer for that field in the Residents table.

[#3]

@George_Hepworth Thanks.  Yes, I know that the indexes are long integer but I haven't run into this "Large Number" so I took a guess---wrongly.

Thanks much.

Larry Woods

[#4]

@lwoodsusa I'm glad you were able to sort it out.

The Large Number datatype was added for compatibility with SQL Server's BigInt datatype. If you have not worked with a SQL Server back end, though, it might not have been something you'd take note of.

[#5]

Right you are. I come from the underworld of open software and (disgusting, I know) "MySQL!" 😉

Thanks, again.

[#6]

Access does link to a wide variety of data sources, including SQL Server, MySQL, even Oracle, as well as SharePoint lists and even csv files. In other words, it's about as close to being data agnostic as it gets. I only worked with MySQL on one project, and an open source db product called "Firebird" one time in my career. They were challenging, but ultimately, it's part of expanding one's skill set.

[#7]

One last question then I will leave you alone. I am just getting back into the MS game and wonder if the "free" version of SQL Server is still available, along with a reasonable set of tools? I would like to connect my Access app to SQL Server if possible but not prepared to pay the price of the full version.

[#8]

@lwoodsusa 

Yes, SQL Server Express Download is free. SQL Server Management Studio is, IIRC, part of the package.

If you are more comfortable with MySQL, that is also a good option.