SQL & Access. Problem deleting a column

[Jimmy211]

I'm wondering if anyone has had this problem before and what the cause might be.

I'm using MS Access as the front end for an SQL database. When I open a table up in design mode and delete a column, save the table, then try to open it in datasheet mode I get an error telling me that Access couldn't find the column I just deleted. I can only open the table if I put a column back in the table with the same name as the one I deleted. It doesn't even have to be the same data type, Access just wants to know there is a column with the same name as the one deleted.

Anyone know why it's doing this? Thanks in advance.

 

[RickSpr]

Did you refer to the column in a Validation Rule, perhaps?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[cmmrfrds]

Are these odbc linked tables? Normally for linked tables you get a message when going into design mode that you cannot modify the table.

 

[Jimmy211]

The tables are connected to SQL and I can modify and save other properties of the tables, so I don't think its an ODBC problem.

As for being a validation rule problem, that is quite possible. In checking through my other tables that reference the table I'm trying to delete a column from I did find a reference to the column. I've searched through my tables and forms for reference to the column, but everything seems ok. Is there an easier way to check validation rules other than searching through all the tables and forms individually?

Thanks,

Jimmy

 

[RickSpr]

I wouldn't think that validation rules in forms would cause a problem when you open the datasheet, only when you open the form.

As far as validation rules in other tables, they're not allowed to reference this table, as far as I know, so you shouldn't have to check them. You should only have to check the validation rule for the column itself, and for the table as a whole.

However, I'm not very familiar with SQL Server or ODBC, so perhaps I'm wrong.

You might also want to look for a reference in a stored procedure (or a trigger?) that runs when the table is opened, if that's possible. Again, I don't know much about SQL Server, so this suggestion may not make sense to those who do.

Oh, another thing just occurred to me. You must have dropped the column in the SQL Server database, but did you also delete and recreate the link? It may be that there is information about the columns stored in the link, and that's what's causing your problem.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Jimmy211]

Thank you, I found it. In the Diagrams under the properties for the table, there was a tab called 'Data' that was telling the table to order by the field I wanted to delete. I told it to order by another field and then deleted the column and now I can see the data in datasheet mode.

Thanks again for your help.

Jimmy