Access 2007 ADP file talking to SQL Server 2005 tables

[Guest]

We have migrated our SQL table from SQL 2000 to SQL 2005 and as a result had
to migrate our ADP files from Access 2003 to Access 2007.

In Access 2007 when we resize a column to show the data and then close the
table we are asked if we want to save the changes. When we reply "Yes" we
are then presented with the error message "Object is invalid. Extended
properties are not permitted on '....', or the object does not exist."

In researching it appears to be that SQL 2005 no longer has the
sysproperties table.

What is the solution to this problem as management is getting a little upset
about this?

 

[Pat Hartman \(MVP\)]

You should not be opening datasheets to work with the data. Create forms in
datasheet view.

 

[Guest]

That sounds easy to do - totally change the way the company has been working
for the past 6 years.

We're talking simple tables that it would take more work and training to
make the forms than it would be worth.

Dan

 

[Sylvain Lafontaine]

Then keep Access 2003 or keep the management high on stress.

 

[Pat Hartman \(MVP\)]

The wizard will build forms automatically. It probably won't take more than
15 seconds each. Since you want them to be in datasheet view, there is no
"pretty-printing" to do. Just use what the wizard generated. It then
becomes a minor procedural change to open a form rather than a table.

 

[Guest]

The wizard will build forms automatically. It probably won't take more than
15 seconds each. Since you want them to be in datasheet view, there is no
"pretty-printing" to do. Just use what the wizard generated. It then
becomes a minor procedural change to open a form rather than a table.

I get the same error, either editing the table directly, of through a form.
I found this KB article here:
http://support.microsoft.com/kb/837937

That's the type of behavior I'm getting, but hitting "Refresh All" doesn't
help at all.

We're using AD security to connect the accdp file, and in Management Studio
to connect directly to the DB. When we use management studio, we can update
the rows just fine. But Access throws this "Record has been changed by
another user" error.

Suggestions?

 

[Guest]

I get the same error, either editing the table directly, of through a form.
I found this KB article here:
http://support.microsoft.com/kb/837937

That's the type of behavior I'm getting, but hitting "Refresh All" doesn't
help at all.

It turns out the error has nothing to with direct table editing vs forms,
refreshing the recordset, or otherwise. It had to do with a BIT column that
was accepting NULL values.

If we added a record in Access that did not explicitly give a value for one
of the two BIT columns, it would be assigned NULL.

Once a row had a NULL value for a BIT column, Access would refuse to edit it
(either directly or through a form), giving an error window that read:

"This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to make
the changes."

This sounds like an Access 2007 bug. At any rate, the solution was to give
a default value for the BIT column. We updated any existing NULL values in
the BIT columns (which enabled Access to edit those rows), and changed the
definition to NOT NULL, to prevent the error from coming up again.

Hope this is helpful to anyone else experiencing this error.

-Josh

 

[Guest]

Sorry, I got mixed up on which thread I was posting to. This problem and
solution I presented is different than the problem the OP (Dan) was
discussing.

-Josh