Cannot find column XXXX

K

krygim

I am used to use MS Access to view a SQL Server database table. One day
after I have modified the table structure and deleted a column XXXX outside
MS Access in a tailor-made program, when I tried to open the table in MS
Access, I got the following message.



Cannot find column XXXX

Microsoft Office Access can't open the table in Datasheet view



How can I get rid of the message?



Thanks in advance!
 
C

Chris2

krygim said:
I am used to use MS Access to view a SQL Server database table. One day
after I have modified the table structure and deleted a column XXXX outside
MS Access in a tailor-made program, when I tried to open the table in MS
Access, I got the following message.



Cannot find column XXXX

Microsoft Office Access can't open the table in Datasheet view



How can I get rid of the message?



Thanks in advance!
Click to expand...

krygim,

1) I create a new table in SQL Server 2005 Express (very
abbreviated).

CREATE Table AccessLinkColDeletionTest
(col1 int
,col2 int
)

INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)
INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)
INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)

SELECT * FROM AccessLinkColDeletionTest


2) Then, I go into MS Access 2000, and create a linked table to it
(ODBC). If I double click on the linked table, it displays the
data.

3) In SQL Server 2005 Express, I then delete one of the columns in
the table:

ALTER TABLE AccessLinkColDeletionTest
DROP COLUMN col2

SELECT * FROM AccessLinkColDeletionTest


4) In MS Access 2000, I then double click on the linked table. I
get an ODBC error, and then I get a second error: "Microsoft Access
can't open the table in Datasheet view."


Solutions:

1) Delete and re-create the linked table in MS Access.

2) Don't delete columns in tables in another database that are the
target of linked tables.


Sincerely,

Chris O.
 
K

krygim

Chris

Thanks for your tips.

Km

Chris2 said:
krygim,

1) I create a new table in SQL Server 2005 Express (very
abbreviated).

CREATE Table AccessLinkColDeletionTest
(col1 int
,col2 int
)

INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)
INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)
INSERT INTO AccessLinkColDeletionTest
VALUES (1, 1)

SELECT * FROM AccessLinkColDeletionTest


2) Then, I go into MS Access 2000, and create a linked table to it
(ODBC). If I double click on the linked table, it displays the
data.

3) In SQL Server 2005 Express, I then delete one of the columns in
the table:

ALTER TABLE AccessLinkColDeletionTest
DROP COLUMN col2

SELECT * FROM AccessLinkColDeletionTest


4) In MS Access 2000, I then double click on the linked table. I
get an ODBC error, and then I get a second error: "Microsoft Access
can't open the table in Datasheet view."


Solutions:

1) Delete and re-create the linked table in MS Access.

2) Don't delete columns in tables in another database that are the
target of linked tables.


Sincerely,

Chris O.
Click to expand...
 
Top