Duplicating data in continuous subform linked to SQL

[mcarlyle via AccessMonster.com]

Ok, I am working on upsizing a FE/BE DB that I have to a SQL 200X BE. I did
the upsizing and everything works except one thing...

I have a main form that has multiple subforms under tabs...

the subforms show data linked to the main form by an autonumbered ID

The subforms show the first record repeated as many times as there are
records that are related. Sounds confusing I know but what I mean is this...
If Bob Smith has 6 notes on his account then when I pull up the notes subform
it would show the first note 6 times.

The added confusion is that if I refresh the data it will correct and show
all 6 notes correcty but only after a refresh.

I really need the data to pull up correctly the first time.

Thanks in advance for any help.

 

[Sylvain Lafontaine]

The first thing would be to tell us if you are using an MDB or ACCDB file
with ODBC linked tables or an ADP project connected to a SQL-Server. It's
not only because this group is about ADP but also because these two products
work entirely differently.

As to your problem, it's looks to me that either the upsized table don't
have a primary key or that Access has some trouble finding it or using it;
for example because you would have a composite primary key (two or more
fields) or because you have another unique index on the table and Access is
using this second unique index as the primary key.

Take also a look at the UniqueTable property of the subform (or something
like that). Using the wrong table as the UniqueTable might give you this
strange behavior.

In all cases, you should take a look with the SQL-Server Profiler to see
what's going on; ie., to see what Access is doing against the SQL-Server.

 

[mcarlyle via AccessMonster.com]

The problem ended up being a primary key.... Not sure how to answer your
questions properly; however, I am using an Access front end with ODBC linked
tables to a SQL 2008 DB that was upsized with Access 2003 to SQL 2005 then
backed up and restored int SQL 2008. Everything seems to be working properly
after I added primary keys to all the tables that did not have them.

The first thing would be to tell us if you are using an MDB or ACCDB file
with ODBC linked tables or an ADP project connected to a SQL-Server. It's
not only because this group is about ADP but also because these two products
work entirely differently.

As to your problem, it's looks to me that either the upsized table don't
have a primary key or that Access has some trouble finding it or using it;
for example because you would have a composite primary key (two or more
fields) or because you have another unique index on the table and Access is
using this second unique index as the primary key.

Take also a look at the UniqueTable property of the subform (or something
like that). Using the wrong table as the UniqueTable might give you this
strange behavior.

In all cases, you should take a look with the SQL-Server Profiler to see
what's going on; ie., to see what Access is doing against the SQL-Server.

Ok, I am working on upsizing a FE/BE DB that I have to a SQL 200X BE. I
did

[quoted text clipped - 17 lines]

Thanks in advance for any help.