Access2007 &SQL server 2008 - problems
Helena
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.
Alex Dybenko
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" <Hel...@discussions.microsoft.com> wrote in message
news:DB8CF2A3-60E6-4DE6...@microsoft.com...
Helena
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:
> .
>
Russell Fields
discussion will get better results.)
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" <Hel...@discussions.microsoft.com> wrote in message
news:DB8CF2A3-60E6-4DE6...@microsoft.com...
Sylvain Lafontaine
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" <Hel...@discussions.microsoft.com> wrote in message
news:DB8CF2A3-60E6-4DE6...@microsoft.com...
Tom van Stiphout
<Hel...@discussions.microsoft.com> 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