Why can't I make design changes on client PC's

[ianataqsl]

I have an SQL2000 Db with an access.adp front end which works fine on the server, but on client PC's I can't open tables in design view or run stored procedures (other than under VBA control with a currentproject.connection). On the client PC's the procedures appear in the database window with a ';1' postfix.

Client PC's can add, alter and delete data in the tables and under VBA programme control the server-side stored procedures function normally.

I have assigned dbo pemissions to guest users - but without any effect.

Any suggestions would be most welcome.

ianataqsl

 

[cmmrfrds]

Does the client PC have all the libraries, service packs etc.. up to the same versions as the development PC.

 

[ianataqsl]

Thanks for your reply cmmrfrds

The client PC's are connecting directly to the SQL2000 server via the Access.adp, the clients therefore should not need any of the SQL 'bits' - should they? other than mdac v 2.6 or higher ?

However, the original SQL Db was developed on SQL7 and then moved onto an SQL2000 server. When this was done the Database Compatibility level was lowered to 70.

The really weird feature of this problem, which I just do not understand, is that under programme control using an ADO [currentproject.connection] (i.e. the same connection as the application) it all works fine, but under Access control server objects are unavailable (although visible). To illustrate this, a stored procedure is used as a data source for a combo box on a form, when the form opens it fails to retrieve the recordset. But, if the combo box Row source is set on form open event, it does retrieve the recordset.

I have tried 2 different servers and had the same result.

My next step (I think) will be to test a connection to SQL2K using Access2003 on the hunch that the problem lies with Access2000

 

[ianataqsl]

For anyone following this thread - the problem was with the Access2000 connection to SQL2000 running under Windows 2000 Server + SBS (but still don't know why).

A client PC with just Access2000 loaded replicated the problem (unable to open server objects in design view or run procedures).

When Access2003 was installed all worked fine in 2003, but problems persisted in 2000.

Ian

Thanks for your contribution and perhaps this may be of use to someone else in the future.