Cannot find column XXXX

  • Thread starter Thread starter krygim
  • Start date Start date
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!
 
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.
 
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...
 
Back
Top