Cannot open table in ADP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot open one specific SQL Server 2000 table in an Access 2003 project;
it fails with the message "Could not find column 'abc'" followed by "Cannot
open the table in datasheet view". The table once had this column and I
assume that I deleted this column from within Access instead of the
Enterprise Manager. It looks af if SQL Server still has a reference to this
deleted column in the table. I can open the table in Access and modfiy the
design.
Any suggestions how to fix this (minor) problem?
TIA, Norbert Meiss
 
Did you try to close/reopen the ADP project or the Refresh command from the
View menu when the database window displays the list of tables? Maybe a
Compact&Repair?

Try creating an empty project and import everything into it.
 
I did already everything - decompile, reconnect, compact/repair, even
creating a new adp, but nothing works. I could probably clone the table in
SQL server and rename it?
 
Any lookup field that you would have added to the table from Access?

These lookup fields are particular to Access (ADP) and are stored as
extended properties on the SQL-Server. However, SQL-Server has no real
knowledge about them.

Another possibility would be that you may have used an unknown field type to
Access (for example, a bigint) or some sort of permission problems.

What happens if you try to open the table from a brand new ADP project,
without anything else?
 
Norbert,

You may have a trigger or index referencing the missing column. In
Enterprise Manger select the table then right click, then All Tasks then
manage Indexes and then Triggers and this should show you any that might
reference the deleted column.

HTH

Bill
 
Bill,

sounded like a good idea in the first place - but I checked trigger and
indexes and even dropped the only trigger which didn't referece the deleted
column.

Regards, Norbert
 
I don't use lookup fields, so that's not likely. I also can exclude unknown
field types. Permission could be an issue since I added a new dbo last year -
however I cannot see any connection, because I dont grant individual rights
 
Are you sure that this is a table, not a View?

Any expression (or computed) column?
 
Yes, it's a table.
No, no expressions or calculated fields.

I have scripted the table:
CREATE TABLE [dbo].[OP_OrderPos] (
[POS_Key] [int] IDENTITY (1, 1) NOT NULL ,
[ORD_Key] [int] NOT NULL ,
[ART_Key] [int] NOT NULL ,
[VAT_Key] [int] NOT NULL ,
[POS_No] [smallint] NOT NULL ,
[POS_Count] [decimal](9, 3) NOT NULL ,
[POS_Text] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_fOrder] [smallint] NOT NULL ,
[POS_fNoForecast] [smallint] NOT NULL ,
[POS_fLocked] [smallint] NOT NULL ,
[POS_fOptional] [smallint] NOT NULL ,
[POS_mUnitPrice] [decimal](14, 2) NOT NULL ,
[POS_pDiscount] [decimal](5, 4) NOT NULL ,
[POS_Count_Pur] [decimal](9, 3) NOT NULL ,
[POS_mSubCon] [decimal](14, 2) NOT NULL ,
[POS_OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_SubCon_Text] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POS_pComm1] [decimal](5, 2) NOT NULL ,
[POS_pComm2] [decimal](5, 2) NOT NULL ,
[POS_mComm1] [decimal](9, 2) NOT NULL ,
[POS_mComm2] [decimal](9, 2) NOT NULL ,
[RowInserted] [datetime] NULL
) ON [PRIMARY]
 
No primary key or unique index?

No problem here.

Are you sure that the name of the column that you have removed was "abc"?
Some name like "no" or "count" can be troublesome.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Norbert Meiss said:
Yes, it's a table.
No, no expressions or calculated fields.

I have scripted the table:
CREATE TABLE [dbo].[OP_OrderPos] (
[POS_Key] [int] IDENTITY (1, 1) NOT NULL ,
[ORD_Key] [int] NOT NULL ,
[ART_Key] [int] NOT NULL ,
[VAT_Key] [int] NOT NULL ,
[POS_No] [smallint] NOT NULL ,
[POS_Count] [decimal](9, 3) NOT NULL ,
[POS_Text] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_fOrder] [smallint] NOT NULL ,
[POS_fNoForecast] [smallint] NOT NULL ,
[POS_fLocked] [smallint] NOT NULL ,
[POS_fOptional] [smallint] NOT NULL ,
[POS_mUnitPrice] [decimal](14, 2) NOT NULL ,
[POS_pDiscount] [decimal](5, 4) NOT NULL ,
[POS_Count_Pur] [decimal](9, 3) NOT NULL ,
[POS_mSubCon] [decimal](14, 2) NOT NULL ,
[POS_OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_SubCon_Text] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POS_pComm1] [decimal](5, 2) NOT NULL ,
[POS_pComm2] [decimal](5, 2) NOT NULL ,
[POS_mComm1] [decimal](9, 2) NOT NULL ,
[POS_mComm2] [decimal](9, 2) NOT NULL ,
[RowInserted] [datetime] NULL
) ON [PRIMARY]

Sylvain Lafontaine said:
Are you sure that this is a table, not a View?

Any expression (or computed) column?
Click to expand...
Click to expand...
 
I have defined POS_Key as PK - I missed to check the option "Script primary
keys". However while checking the scripting options I found and checked the
option "include extended properties". The script shows a reference to the
deleted column DBI_Key (no, it wasn't abc):
exec sp_addextendedproperty N'MS_OrderBy', N'OP_OrderPos.DBI_Key DESC',
N'user', N'dbo', N'table', N'OP_OrderPos'
where 'DBI_Key' is the offending column. I might come from having sorted the
table in Access and saved it. Question now is how to get rid of this extended
property?


Sylvain Lafontaine said:
No primary key or unique index?

No problem here.

Are you sure that the name of the column that you have removed was "abc"?
Some name like "no" or "count" can be troublesome.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Norbert Meiss said:
Yes, it's a table.
No, no expressions or calculated fields.

I have scripted the table:
CREATE TABLE [dbo].[OP_OrderPos] (
[POS_Key] [int] IDENTITY (1, 1) NOT NULL ,
[ORD_Key] [int] NOT NULL ,
[ART_Key] [int] NOT NULL ,
[VAT_Key] [int] NOT NULL ,
[POS_No] [smallint] NOT NULL ,
[POS_Count] [decimal](9, 3) NOT NULL ,
[POS_Text] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_fOrder] [smallint] NOT NULL ,
[POS_fNoForecast] [smallint] NOT NULL ,
[POS_fLocked] [smallint] NOT NULL ,
[POS_fOptional] [smallint] NOT NULL ,
[POS_mUnitPrice] [decimal](14, 2) NOT NULL ,
[POS_pDiscount] [decimal](5, 4) NOT NULL ,
[POS_Count_Pur] [decimal](9, 3) NOT NULL ,
[POS_mSubCon] [decimal](14, 2) NOT NULL ,
[POS_OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_SubCon_Text] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POS_pComm1] [decimal](5, 2) NOT NULL ,
[POS_pComm2] [decimal](5, 2) NOT NULL ,
[POS_mComm1] [decimal](9, 2) NOT NULL ,
[POS_mComm2] [decimal](9, 2) NOT NULL ,
[RowInserted] [datetime] NULL
) ON [PRIMARY]

Sylvain Lafontaine said:
Are you sure that this is a table, not a View?

Any expression (or computed) column?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I don't use lookup fields, so that's not likely. I also can exclude
unknown
field types. Permission could be an issue since I added a new dbo last
year -
however I cannot see any connection, because I dont grant individual
rights
on objects (at least not in tihis database).
Today I tried to create a completely new ADP and the same happens.

:

Any lookup field that you would have added to the table from Access?

These lookup fields are particular to Access (ADP) and are stored as
extended properties on the SQL-Server. However, SQL-Server has no
real
knowledge about them.

Another possibility would be that you may have used an unknown field
type
to
Access (for example, a bigint) or some sort of permission problems.

What happens if you try to open the table from a brand new ADP
project,
without anything else?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I did already everything - decompile, reconnect, compact/repair, even
creating a new adp, but nothing works. I could probably clone the
table
in
SQL server and rename it?

:

Did you try to close/reopen the ADP project or the Refresh command
from
the
View menu when the database window displays the list of tables?
Maybe
a
Compact&Repair?

Try creating an empty project and import everything into it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I cannot open one specific SQL Server 2000 table in an Access 2003
project;
it fails with the message "Could not find column 'abc'" followed
by
"Cannot
open the table in datasheet view". The table once had this column
and I
assume that I deleted this column from within Access instead of
the
Enterprise Manager. It looks af if SQL Server still has a
reference
to
this
deleted column in the table. I can open the table in Access and
modfiy
the
design.
Any suggestions how to fix this (minor) problem?
TIA, Norbert Meiss
Click to expand...
Click to expand...
Click to expand...
 
Norbert,



I can actually help here from



http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro02/html/sql02a10.asp

The following is listed.

To delete an extended property, you pass the property name and object
identifier to sp_dropextendedproperty. For example, here's the T-SQL to
delete the Caption property from the id column in the table named MyTable:

sp_dropextendedproperty 'Caption', 'user', 'dbo',
'table', 'MyTable', 'column', 'id'
Bill

Norbert Meiss said:
I have defined POS_Key as PK - I missed to check the option "Script primary
keys". However while checking the scripting options I found and checked
the
option "include extended properties". The script shows a reference to the
deleted column DBI_Key (no, it wasn't abc):
exec sp_addextendedproperty N'MS_OrderBy', N'OP_OrderPos.DBI_Key DESC',
N'user', N'dbo', N'table', N'OP_OrderPos'
where 'DBI_Key' is the offending column. I might come from having sorted
the
table in Access and saved it. Question now is how to get rid of this
extended
property?


Sylvain Lafontaine said:
No primary key or unique index?

No problem here.

Are you sure that the name of the column that you have removed was "abc"?
Some name like "no" or "count" can be troublesome.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Norbert Meiss said:
Yes, it's a table.
No, no expressions or calculated fields.

I have scripted the table:
CREATE TABLE [dbo].[OP_OrderPos] (
[POS_Key] [int] IDENTITY (1, 1) NOT NULL ,
[ORD_Key] [int] NOT NULL ,
[ART_Key] [int] NOT NULL ,
[VAT_Key] [int] NOT NULL ,
[POS_No] [smallint] NOT NULL ,
[POS_Count] [decimal](9, 3) NOT NULL ,
[POS_Text] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_fOrder] [smallint] NOT NULL ,
[POS_fNoForecast] [smallint] NOT NULL ,
[POS_fLocked] [smallint] NOT NULL ,
[POS_fOptional] [smallint] NOT NULL ,
[POS_mUnitPrice] [decimal](14, 2) NOT NULL ,
[POS_pDiscount] [decimal](5, 4) NOT NULL ,
[POS_Count_Pur] [decimal](9, 3) NOT NULL ,
[POS_mSubCon] [decimal](14, 2) NOT NULL ,
[POS_OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[POS_SubCon_Text] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POS_pComm1] [decimal](5, 2) NOT NULL ,
[POS_pComm2] [decimal](5, 2) NOT NULL ,
[POS_mComm1] [decimal](9, 2) NOT NULL ,
[POS_mComm2] [decimal](9, 2) NOT NULL ,
[RowInserted] [datetime] NULL
) ON [PRIMARY]

:

Are you sure that this is a table, not a View?

Any expression (or computed) column?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I don't use lookup fields, so that's not likely. I also can exclude
unknown
field types. Permission could be an issue since I added a new dbo
last
year -
however I cannot see any connection, because I dont grant individual
rights
on objects (at least not in tihis database).
Today I tried to create a completely new ADP and the same happens.

:

Any lookup field that you would have added to the table from
Access?

These lookup fields are particular to Access (ADP) and are stored
as
extended properties on the SQL-Server. However, SQL-Server has no
real
knowledge about them.

Another possibility would be that you may have used an unknown
field
type
to
Access (for example, a bigint) or some sort of permission problems.

What happens if you try to open the table from a brand new ADP
project,
without anything else?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I did already everything - decompile, reconnect, compact/repair,
even
creating a new adp, but nothing works. I could probably clone the
table
in
SQL server and rename it?

:

Did you try to close/reopen the ADP project or the Refresh
command
from
the
View menu when the database window displays the list of tables?
Maybe
a
Compact&Repair?

Try creating an empty project and import everything into it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


in
message
I cannot open one specific SQL Server 2000 table in an Access
2003
project;
it fails with the message "Could not find column 'abc'"
followed
by
"Cannot
open the table in datasheet view". The table once had this
column
and I
assume that I deleted this column from within Access instead
of
the
Enterprise Manager. It looks af if SQL Server still has a
reference
to
this
deleted column in the table. I can open the table in Access
and
modfiy
the
design.
Any suggestions how to fix this (minor) problem?
TIA, Norbert Meiss
Click to expand...
Click to expand...
Click to expand...
 
Thanks Bill,

that did the trick. I could drop the extended property and voila - the table
again can be opened in Access.

Regards, Norbert

Bill Patten said:
Norbert,



I can actually help here from



http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro02/html/sql02a10.asp

The following is listed.

To delete an extended property, you pass the property name and object
identifier to sp_dropextendedproperty. For example, here's the T-SQL to
delete the Caption property from the id column in the table named MyTable:

sp_dropextendedproperty 'Caption', 'user', 'dbo',
'table', 'MyTable', 'column', 'id'
Bill

Norbert Meiss said:
I have defined POS_Key as PK - I missed to check the option "Script primary
keys". However while checking the scripting options I found and checked
the
option "include extended properties". The script shows a reference to the
deleted column DBI_Key (no, it wasn't abc):
exec sp_addextendedproperty N'MS_OrderBy', N'OP_OrderPos.DBI_Key DESC',
N'user', N'dbo', N'table', N'OP_OrderPos'
where 'DBI_Key' is the offending column. I might come from having sorted
the
table in Access and saved it. Question now is how to get rid of this
extended
property?


Sylvain Lafontaine said:
No primary key or unique index?

No problem here.

Are you sure that the name of the column that you have removed was "abc"?
Some name like "no" or "count" can be troublesome.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Yes, it's a table.
No, no expressions or calculated fields.

I have scripted the table:
CREATE TABLE [dbo].[OP_OrderPos] (
[POS_Key] [int] IDENTITY (1, 1) NOT NULL ,
[ORD_Key] [int] NOT NULL ,
[ART_Key] [int] NOT NULL ,
[VAT_Key] [int] NOT NULL ,
[POS_No] [smallint] NOT NULL ,
[POS_Count] [decimal](9, 3) NOT NULL ,
[POS_Text] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POS_fOrder] [smallint] NOT NULL ,
[POS_fNoForecast] [smallint] NOT NULL ,
[POS_fLocked] [smallint] NOT NULL ,
[POS_fOptional] [smallint] NOT NULL ,
[POS_mUnitPrice] [decimal](14, 2) NOT NULL ,
[POS_pDiscount] [decimal](5, 4) NOT NULL ,
[POS_Count_Pur] [decimal](9, 3) NOT NULL ,
[POS_mSubCon] [decimal](14, 2) NOT NULL ,
[POS_OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[POS_SubCon_Text] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[POS_pComm1] [decimal](5, 2) NOT NULL ,
[POS_pComm2] [decimal](5, 2) NOT NULL ,
[POS_mComm1] [decimal](9, 2) NOT NULL ,
[POS_mComm2] [decimal](9, 2) NOT NULL ,
[RowInserted] [datetime] NULL
) ON [PRIMARY]

:

Are you sure that this is a table, not a View?

Any expression (or computed) column?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I don't use lookup fields, so that's not likely. I also can exclude
unknown
field types. Permission could be an issue since I added a new dbo
last
year -
however I cannot see any connection, because I dont grant individual
rights
on objects (at least not in tihis database).
Today I tried to create a completely new ADP and the same happens.

:

Any lookup field that you would have added to the table from
Access?

These lookup fields are particular to Access (ADP) and are stored
as
extended properties on the SQL-Server. However, SQL-Server has no
real
knowledge about them.

Another possibility would be that you may have used an unknown
field
type
to
Access (for example, a bigint) or some sort of permission problems.

What happens if you try to open the table from a brand new ADP
project,
without anything else?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message
I did already everything - decompile, reconnect, compact/repair,
even
creating a new adp, but nothing works. I could probably clone the
table
in
SQL server and rename it?

:

Did you try to close/reopen the ADP project or the Refresh
command
from
the
View menu when the database window displays the list of tables?
Maybe
a
Compact&Repair?

Try creating an empty project and import everything into it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


in
message
I cannot open one specific SQL Server 2000 table in an Access
2003
project;
it fails with the message "Could not find column 'abc'"
followed
by
"Cannot
open the table in datasheet view". The table once had this
column
and I
assume that I deleted this column from within Access instead
of
the
Enterprise Manager. It looks af if SQL Server still has a
reference
to
this
deleted column in the table. I can open the table in Access
and
modfiy
the
design.
Any suggestions how to fix this (minor) problem?
TIA, Norbert Meiss
Click to expand...
Click to expand...
Click to expand...
 
I see that you've resolved the problem. But for future reference, one of
the most common causes of this in an ADP is having saved a table that was
sorted or filtered on a column that's been deleted. When you try to open
it, you will get the error you mentioned. You can resolve the problem the
way you did, by removing all extended properties, or you can simply open the
table in design view and check the property sheet for any Order By or Filter
statements. Delete those, save the design, and your datasheet view should
open.


Rob
 
Thanks Rob,
good to know. For me this looks like a minor bug. If Access allows to sort
or filter and subsequently to delete a column, it also should handle the
consequences.

Regards, Norbert
 
yes, I bitched up a storm about this when I was working at Microsoft..
but they found it easier to fire me for speaking the truth than to fix
a couple simple bugs like this.

I insisted that they had usability problems; and somehow it would make
sense to have some sort of functionality to 'clean out extended
properties'

I mean-- make it easy enough for end users to maintain it if need be;
make it tools, database utilities, extended properties... and give it a
pretty UI-- all BUILT IN to access.

but no. MS couldn't afford to admit it was a problem.

DENIAL ISNT JUST A RIVER IN EGYPT.

MS is a bunch of drunk blind frat boys; driving down the autobahn while
taking beer-bongs.

-Aaron
ADP Nationalist
 
Back
Top