• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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
Datasnapshots
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
Help
0
smelliesavage
Asked:
smelliesavage
  • 2
  • 2
1 Solution
 
Rakesh JaiminiCommented:
check permissions of new database with the old one
there might be some extra users and roles are configured on old DB
0
 
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?
0
 
Rakesh JaiminiCommented:
Hi
please check following link
http://office.microsoft.com/en-us/access/HP052731031033.aspx
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
0
 
smelliesavageAuthor Commented:
thanks for your help! Problem sorted
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now