Problem with Access 2003 ADP on XP Client sql 2005 database

Posted on 2010-01-08
Last Modified: 2012-05-08
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
Question by:smelliesavage
    LVL 10

    Expert Comment

    by:Rakesh Jaimini
    check permissions of new database with the old one
    there might be some extra users and roles are configured on old DB

    Author Comment

    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?
    LVL 10

    Accepted Solution

    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

    Author Closing Comment

    thanks for your help! Problem sorted

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now