Post reply

Logging in Access to SQL Server 2005

  • August 3, 2007 at 2:07 pm

    #70587

    Hi all,

    We found an expression to login Microsoft Access to SQL Server 2005, but are unable to figure out where to insert it to actually make the connection. Does anyone have any experience in this?

    Thanks,

    Taylor

  • August 4, 2007 at 4:38 am

    #724697

    Taylor,

    What expression do you have to do this? I would imagine it is somthing along the lines of

    sp_addlinkedserver

    @server='AccessDb',

    @srvproduct='Acess',

    @provider='Microsoft.Jet.OLEDB.4.0',

    @datasrc='C:\nwind.mdb'

    If so you just put this in a query window (updaing the parameters for your database) in SSMS or QA and run it. You then need to add a login to the access database:

    EXEC sp_addlinkedsrvlogin 'AccessDB', false, 'sa', 'Admin', NULL

    You can then run a query against your server:

    SELECT * FROM AccessDB..categories

    should return everything in the categories table in your SQL Server.

    http://www.sqlservercentral.com/columnists/ksonti/anintroductiontolinkedservers.asp is an introduction to linked servers and there are plenty of resources available via google discussing exactly what you want. If you get stuck again post here and we can try and help.

    HTH

    James

    --
    James Moore
    Red Gate Software Ltd

  • August 4, 2007 at 8:08 am

    #724702

    James,

    Thanks, that should work great.  I'll check out that link too.

     

    Taylor

  • August 7, 2007 at 9:20 am

    #725190

    Are you attempting to connect

         A) an Access application (mdb or adp) to a SQL 2005 server?

    or

         B) a SQL 2005 server to an access mdb database file?

     

     

    [font="Arial"]Clifton G. Collins III[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply