Problem with Access 2003 ADP on XP Client sql 2005 database

I am connecting an Access 2003 adp project on XP Pro client  to SQL 2005 database on Windows 2008 Server. The SQL 2005 Server has multiple databases.
When I create a new project using an existing data source, it connects fine to the sql server instance but doesn't show all the databases on the flip down 'select the database on server'
I am using sql authentication (sa). The database I am trying to access is not the master one.
I have access 2003 installed on the 2008 server where the SQL server lives, and when I do exactly the same thing, it shows all the databases. I have created a working adp which runs fine on the server, but when I copy it to the PC it doesn't work.
Any help would be really appreciated - I have tried using an ODBC connection both with SQL Server and with Native Client on the Xp Pro box and have the same issue (doesn't show all databases).
I have Studio management Express installed which shows under the databases section:
System database
EEMDB-Live  (my database)

The process I have gone through is to create new adp in Access 2003 (on the server)
select the correct SQL Server instance (there are several as I have sophos Pure messaging and created a new SQL instance for our database)
select the correct database (EEMDB-Live) which shows when doing this on the server, but not on the client PC
No firewall on the client PCs
Who is Participating?
Rakesh JaiminiCommented:
please check following link
it says
  If you are connecting to a named instance of a SQL Server 2000 or SQL Server 2005 server, your Access project client computer must have Microsoft Data Access Components (MDAC) version 2.71 or later installed. You can install MDAC 2.71 by installing Microsoft SQL Server 2000 Personal Desktop Edition from the \MSDE2000 folder on the Microsoft Office 2003 CD-ROM.
i think this is your problem's solution if I got you right
Rakesh JaiminiCommented:
check permissions of new database with the old one
there might be some extra users and roles are configured on old DB
smelliesavageAuthor Commented:
Thanks for the response - it didn't solve the problem but helped me think out of the box - happy to give the points if you can help me with the this.
I have found that the reason why it can't see the databases is because the access frontend adp will only see the default instance of SQL 2005 and can't see the other instances (i.e. for me - default server instance was OEM01,  Sophos instance was OEM01\SOPHOS, my instance was OEM01\ENGAGESRVR)  When I moved the databse from my instance back to the default, the client PC can now see it, however I am trying to relink the forms in the adp front end and don't have enough vb to get me going - can you point me in the right direction?
smelliesavageAuthor Commented:
thanks for your help! Problem sorted
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.