SQL Table Management problem through ADP

[ZRexRider]

Hi,

I have an MS Access Data Project connected to my SQL Server database.
While in Access (Table View) I deleted a column that I no longer
needed. No problems/no errors.

Now if I double-click that table to view the data I get the following
errors:

"Cannot find column 'dtmActualDate'."

I click OK button on the above message and I get:

"Microsoft Office Access can't open the table in datasheet view."

I've run "Compact and Repair Project.."
I've "refreshed" the connection.
I can open the table in design view w/o error.

I don't want to put that column back in just to make Access happy.
Any ideas?

Thanks

 

[Norman Yuan]

What happens if you close the ADP, then open it again and open the table?

 

[ZRexRider]

Closing and re-opening has no effect. I the column back in the table -
then Access was happy again. I went into SQL Server Enterprise Manager
and deleted the column then re-opened the ADP and Access is unhappy
again.

It's almost as if the MS Access ADP has cached/stored it's own copy of
the SQL schema and doesn't repair it when changes are made to SQL.

Even if I change the connection to another DB then change it back to
the one I'm working with it still complains.

The only solution that I find that works (pain in the butt) - I have to
create a new ADP and import all the forms, code modules, and macros
from the broken one.

 

[Norman Yuan]

Interesting. I have been using Access2K/XP/2003 ADP with SQL Server7.0/2000
(MSDE1.0/2000) as mangement tool/front-end exclusively since year 2000, and
never had that kind of thing (Although I have Enterprise Manager, I always
use ADP to design/maintain database, just used to it).

Which version Access do you use? Have you tried to uninatall and reinstall
Access?

 

[Sylvain Lafontaine]

By "refreshing" the connection, do you mean using the function Refresh (F5)
from the View menu?

S. L.

 

[Vadim Rapp]

Z> I have an MS Access Data Project connected to my SQL Server database.
Z> While in Access (Table View) I deleted a column that I no longer
Z> needed. No problems/no errors.

Z> Now if I double-click that table to view the data I get the following
Z> errors:

Z> "Cannot find column 'dtmActualDate'."

Try to figure out if the problem is related to the databse, or to the adp.

1. try to open the table in enterprise manager.

2. create another adp connecting to the same database. If the problem
persists, then it's something in the database. If not, then it's in ADP, and
the easiest solution is to import all forms/reports/modules to the new adp.

If it's in the database, one thing it might be is extended properties of the
table. When you open the table for design in Access, you have tab "Lookup",
so you can show a column as combobox with lookup in another column. If
another column has lookup for the deleted column, this might be the reason.
I tried this specific scenario, but the error hit only when I tried to open
the dropdown, unlike your situation.But it might be something similar. Check
those Lookup tabs for all columns.

Also I would run Profiler on the database while opening the table in Access,
to see what's going on.

Also try opening the table as another user - http://dbforums.com/archive/42/2002/08/4/486931
says it can matter.


Vadim

 

[Samuele]

Sorry for my english, i'm an italian guy.
i think you can try this.

maybe your table was ordered for the deleted column.
So you had to open the table (with the column to delete) and re-ordered the
table for another column, save the layout of the table and then delete the
column.

Let me know.

Semmi

 

[ZRexRider]

Semmi,

Your English was fine and you solved my problem! Thank You.

- I opened the table in Access
- Sorted by another column
- closed the table (agreeing to save design)
- opened table in design view and deleted the ''dtmActualDate' column.
- Save design.
- Opened the table again and no problems!

I guess Access makes me lazy. I would have expected it to be smart
enough to delete associated sorts and filters when I delete a column or
at least tell me that it won't allow me to delete the column.


Thank you very much

 

[ZRexRider]

Also Note:

I just created a new .MDB and created a test table where I added some
fields and sorted by a field.

Went back and deleted the field that the table was sorted by.
Opened table again with no problems.

Must be just an ADP problem.

 

[NICO_bruneta]

Hi