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:
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