Hi! We transfered our DB from SQL 2005 to SQL2008 by creating scripts in SQL
Studio2008. After that we can't update correctly records in subforms(if this
subform have two tables as datasource).
For example:
We have
A ([ID] [int] IDENTITY(1,1) NOT NULL,[LASTNAME] [nvarchar](56) NULL)
B ([ID] [int] IDENTITY(1,1) NOT NULL,[TITLE] [nvarchar](56) NULL)
C(A_id [int] not null,B_id[int] not null)

Form1 - datasource is table A
subForm2 - datasource are tables B and C
Table C is like linking table. We correctly transfered all of CONSTRAINT and
REFERENCES. Tables A and B have relations with each other by the use of table
C.
But now we have very a strange problem with updating subForm2.
We can't insert a correctly record in this subForm - fields [ID] from table
B and therefore [B_id] don't have a correctly increment (though their
properties let them to do it).
Unfortunately, while we had SQL server 2005 and clients applications in
Access 2007 we didn't have these problems - our DB was working correctly all
the time.
We'll thank you very much for any advices.

Helena,

You might find more help available on the
microsoft.public.access.adp.sqlserver site where you also posted this
question. I have very little Access experience but, if you are not familiar
with it, please read:
http://support.microsoft.com/kb/308312 In part it says:

"Other limitations and security considerations
Subforms not working

Unlike with other database objects, Access does not always use the same
connection to retrieve the data source of a subform. Access frequently (but
not always) creates a new connection to SQL Server just to handle the
subform recordset ... a permissions error may be generated if you do not
have explicit permissions to the database object. "

If this is your problem, it could be that when you scripted over the objects
from 2005 to 2008 you did not get all the security settings scripted across.
(Just a guess, of course.)

http://allenbrowne.com/Access2007.html
Comments: Access 2007 does not support ADPs as well as previous versions.
Microsoft recommends using attached tables instead, as explained in the
book, Microsoft Access Developer's Guide to SQL Server by Andy Baron and
Mary Chipman.


RLF

"Helena" <Helena (AT) discussions (DOT) microsoft.com> wrote