Ridiculously Simple Relationship, But It's Not Working!

[Criki]

OK, I'm not new to SQL by any means (no expert either) but this is blowing my mind!
I have a 3rd party application that uses Access as it's database. We use SQL and have linked all our tables in Access to use this app.

Previously, there were 3 tables, tbl_parent, a child tbl_child, and a lookup table tbl_LU. This worked fine, but our rules changed so our structure changed.
Now we have tbl_parent, tbl_subParent, and tbl_LU really wasn't a lookup table so it became a parent of tbl_child (and a child of tbl_subparent).

All the data is there. There are no orphan records.
But for some reason some of the tbl_LU and/or tbl_Child do not show. Even after I verify they are in the db and all the related fields are completed and correct.

I haven't been able to find any rhyme or reason to why some show fine and others do not. The only thing I can think of, it must be an Access thing. When creating a view in SQL all records show fine, it's just in Access or the App that I have issues.

If anyone can shed any light on this I would greatly appreciate it. I know it's very vaugue, but that's why I can't solve it either.

Thanks SO much!

 

[donutman]

Try the Access forum. It's more like that someone there will know about bizzare behavior within Access.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[HBWAL]

so, if they are linked tables. You need to drop the tables and relink them.

If it is and ADP then check to see if you have limits on how many rows the table can return.

Good luck.