Ended up solving this myself.

I found an Me client that actually did work, the only one out of 11! I
compared the DSN settings on all pages of the setup procedure and found that
a certain tick box wasn't available on the Me clients that I could not get to
connect to SQL server. From this I deduced that the software had to be
slightly different.

So I found the lastest MDAC 2.8 update, downloaded it and installed and Me
clients so treated suddenly started connecting to SQL server. If anyone sees
this I found the download at...

http://www.microsoft.com/downloads/d...DisplayLang=en

Good luck to anyone who sees this and thinks it is worth trying.

"tricky300" wrote:

> Hi,
>
> Thanks to anyone who can help.
>
> We are in the process of renewing all our old Win Me clients, but this
> cannot be done overnight and we have many old Me clients still attached to
> the domain. Unfortunatley we suffered a server (SBS 2003 premium) crash and
> rebuilt the server with SQL 2000 server. Before the server was rebuilt the Me
> clients all had a data source that allowed us to use Access to query a
> database. Since the rebuild the old data sources do not work and neither can
> we create a new data source, when we get to the authentication method,
> regardless of whether we choose NT user or SQL the SQL server cannot be
> contacted.
>
> The only differences between the old and new servers are:-
> the old server had no name for the sql server whereas the new server is
> called SQLSERVER
> the old server was sp3a and the new server is sp4
>
> The server name that we were using successfully in the data source for the
> old server was AA01. The new server has excatly the same server name (AA01)
> but neither AA01 nor AA01\SQLSERVER will give a connection. Neither will
> substituting the server ip address (192.168.16.2) for AA01