Access2007 &SQL server 2008 - problems

  • Thread starter Thread starter Helena
  • Start date Start date
H

Helena

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 said:
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 said:
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.
Click to expand...
.
Click to expand...
 
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)
 
On Tue, 1 Dec 2009 02:03:13 -0800, Helena

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
 
Back
Top