Hi! We transferred 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.

Hi,
try to relink these tables (perhaps you have recently changed the schema)
do all tables have primary keys?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Helena" <(E-Mail Removed)> wrote in message
newsB8CF2A3-60E6-4DE6-84E3-(E-Mail Removed)...
> Hi! We transferred 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.
>

Hi! Thanks.
We have already done relink and DBCC DBREINDEX. All tables have primary
keys. Table C has a compound key.
Can I send the script what we used to transfer our DB? Next idea - maybe we
have too many records in our DB? DB contains not less than 5 tables with 400
000 records each. Is it a problem for Access?
Thanks.

"Alex Dybenko" wrote:

> Hi,
> try to relink these tables (perhaps you have recently changed the schema)
> do all tables have primary keys?
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
> "Helena" <(E-Mail Removed)> wrote in message
> newsB8CF2A3-60E6-4DE6-84E3-(E-Mail Removed)...
> > Hi! We transferred 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.
> >
> .
>

If you want to create both the new record for B and C at the same time, this
is the multi-step feature of Access. This doesn't work reliably in ADP
unless you are using some sort of trigger. So do you have any trigger that
you would have forgot to copy over to SQL-2008?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Helena" <(E-Mail Removed)> wrote in message
newsB8CF2A3-60E6-4DE6-84E3-(E-Mail Removed)...
> Hi! We transferred 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.
>

On Tue, 1 Dec 2009 02:03:13 -0800, Helena
<(E-Mail Removed)> wrote:

Was it really necessary to script the objects (and perhaps forget a
few)? Rather I would make a backup of the SQL2005 one, and then
restore it in SQL2008. It guarantees that everything comes across.

-Tom.
Microsoft Access MVP


>Hi! We transferred 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.