SQL Server tables in Access 2007 are read-only via ADP !

[Allen_N]

I imported a number of Access 2003 MDBs and ADPs when we upgraded to Office
2007, and I never stop running into problems with the new version. The latest
one is that I find I can't edit fields in a SQL-Server (2005) table using an
ADP front end. This worked fine before the "upgrade" !

Are there some sneaky settings somewhere that need changing?

Thanks!

 

[Paul Shapiro]

I'm using an Access 2007 adp with SQL Server 2005 and am able to edit
normally. Have you installed Office 2007 SP1 and the post-SP1 Access
hotfixes? Does your SQL Server table have a primary key? Have you set the
folder containing the adp as a Trusted Location in Access?

 

[Allen_N]

Hi Paul,

I have confirmed that Office is up to date via Windows Update.

The 1st column in the table is of type "int", does not allow nulls, has
"Identity" = 'Yes', and has "Identity Seed" = 1, "Identity Increment = 1".
I'm assuming that all that makes it Primary Key in SQL Server; am I right?

Also, I found that I can edit other tables in the ADP, so there must be
something unusual about the one in question.

Thanks for your advice!

 

[Norman Yuan]

Hi Paul,

I have confirmed that Office is up to date via Windows Update.

The 1st column in the table is of type "int", does not allow nulls, has
"Identity" = 'Yes', and has "Identity Seed" = 1, "Identity Increment =
1".
I'm assuming that all that makes it Primary Key in SQL Server; am I right?

No, all of these do not make a column a primary key automatically. You need
to specifically make it as primary key column, if it should be one.

When importing MDB table into SQL Server database, it is common the primary
key is lost/not imported, that makes the table not editable.

 

[Allen_N]

Thanks, Norman.

I just noticed the "Primary Key" thingo on the Design View ribbon and
clicked it. My table is now editable.

(Woo-hoo!)

Thanks again, guys!

-- Al

 

[Guest]

Unknown at this time. Will look into it.