Access XP and Sql Svr 2000 field rename problem

[gem1204]

Hello everyone,
I hope I’m in the right forum for the question I’m asking.


I’m using MS Access XP with Sql Server 2000 in an Access ADP. I am still designing my Sql Server database and recently I deleted field called “EmpStatus” from my database. When I tried to open the table I got a message that the field “EmpStatus” could not be found. I tried refreshing the table names and reconnecting to the database but neither of those options work and I couldn’t open the table. I finally had to add the field “EmpStatus” back to the table before the table would open.

I experimented with this problem and found that if you change a field name or delete a field in a table either by using enterprise manager or the Access UI, that table will no longer open in an Access project. I even tried creating a new ADP using the same sql server database and that table still l wouldn’t open.

Can anyone explain to me why this is happening, what I doing wrong , or how to get around this problem?

I’m using Access XP and Sql Server 2000

Thanks
GEM1204

 

[BrettStah]

One suggestion is to not use ADP files. Rather, you can use MDB files and link to the SQL Server tables.

 

[gem1204]

Thanks for the suggestion. I have actually done that using ODBC, but I wanted to use the ADP route. There are so many things available in the adp as far as forms, reports, and the ability to create and modiy table, views, sp's and functions. The main drawback is that in an adp all of the sql server objects are exposed just as they would in an mdb. I know you can hide the database window or take other actions to prevent their viewing, but any user with a little knowledge can get around that process.

I have done one other project using an ADP but with Access 2003 and SQL Server 2000 . I don't remember having the problem with the field alterations. I don't know if this is a problem with Access XP - I still would like to know what I'm doing wrong or if this is just a bug with Access XP.

Thanks again for the quick response

 

[BrettStah]

All I know is that what I've read over time, and my conclusion is that ADP files are not the way to go... Microsoft doesn't recommend their use either. Because of that, I don't make any ADP projects any more.