Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: MS Access 2007 and SqlServer 2008 express

    I am having a problem connecting an MS Access 2007 ADP to an Sql Server 2008 Express Server.

    I installed SQL Server express 2008 on a machine running windows 7. When I try to connect to the server using an MS Access 2007 ADP. I get an error “Login Failed. Catalog information cannot be retrieved.” I tried connecting using the SA account, Windows Authenication, and with a user that I created that had SysAdmin permissions – nothing works.

    I CAN connect to the server using Sql Server management studio, I can create an odbc connection that works, and I can create an asp.net connection that works using Visual Studio 2008.

    It would seem that Access would be the problem but I can connect to an Sql Server 2008 Express database running on a remote server so I can’t figure out where the problem is.

    I previously had SQL Server 2005 express installed that worked ok. When I upgraded to SQL Server 2008 is the problem started. I have uninstalled and resinstalled Sql Server 2008 Express sever times and I’m still getting the same problem.

    Does anyone have any Idea what the problem could be? Could it be a problem with the registry?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check your protocols. Also, see what gets posted in the errorlog of the server.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    My network protocol is set to default. When I tried to change it to something other than default I got an error message “There server was not found or is inaccessible.

    No errors were logged when I tried to connect with MS Access.
    This is what I did.
    I restarted my computer and tried it again. I opened The management studio and the last log was “Using xpstar.dll version 2009-100-1600 to execute stored procedure xp_enumerrorLogs. This is an information message only, no user action is required.
    I then Opened an MS access project and went to “Server” then “Connection” and entered the server name, used Windows NT Integrated security, and when I clicked on the dropdown list “Select the database on the server” I got an error message “[DBNETLIB[ConnectionOpen(Connect().specified Server not found”. When I clicked the ok button I got another error “Login failed. Catalog information cannot be retrieved.

    I reopened Sql Management studio and went to the Sql Server Logs and there were no additional errors logged. Apparently access couldn’t find the server when I tried to connect so no error log entry was created.
    Any Ideas?
    Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When you say "to something other than default", what do you mean? What was the default and what did you change it to? There are 4 protocols, - TCP/IP, Named Pipes, Shared Memory, and VIA. Shared Memory works only for local connections on the server itself. VIA should only be used if you're using Vendor-provided networking hardware that has the necessary layer for TDS-based communications. Now we're left with TCP/IP and Named Pipes. Which ones are enabled/disabled?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    Thanks for your responses rdjabarov but the problem worekd itself out. When I created an Access project and elected to create a new Sql Server database instead of connecting to an existing database I was able to connect to my sql server and create the database. After I did that I was able to connect to any other databases. I had no idea why but now everything is working just fine.

    Thanks for your interest in helping me with this problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •