Linked Server ACCDB file

  • I'm having a great deal of trouble creating a linked server to my ACCDB (Access 2007) database. I'm using SS2k5 on a new MS Server 2008. I've installed the Ace provider, and even Office 2007 with Access. I can open the file using Access 2007. I just can't link the file as a server. I thought it was an authentication issue, but I moved it to my D: drive on the server, and nothing got better. SSIS can use the DB. Here's the code I'm using to create the Linked Server. The code works, but the server won't connect (error 7303).

    DECLARE

    @LinkedServer nVARCHAR(50),

    @PathFile nVARCHAR(250)

    SET @LinkedServer = 'CPAS_BE'

    SET @PathFile= 'D:\Backend\Composite_be.ACCDB'

    IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @LinkedServer)

    EXEC master.dbo.sp_dropserver @server=@LinkedServer, @droplogins='droplogins'

    EXEC master.dbo.sp_addlinkedserver @server = @LinkedServer, @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=@PathFile

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'collation compatible', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'data access', @optvalue=N'true'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'rpc', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'rpc out', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'connect timeout', @optvalue=N'0'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'collation name', @optvalue =null

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'query timeout', @optvalue=N'0'

    EXEC master.dbo.sp_serveroption @server=@LinkedServer, @optname=N'use remote collation', @optvalue=N'true'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @LinkedServer, @locallogin = NULL , @useself =N'False'

    Jim

  • I know this is NOT addressing your problem, but in case you didn't already know about this nifty tool from MS. If you're just planning on synching them once in a while and it's not a "live" connection. This might be a good tool for you.

    SQL Migration Assistant For Access

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • I used SSMA to create the SS2k5 database. I do refresh it every week or so as I go about modifying queries and forms in the Front End to be more SS friendly (I'm on refresh 34...), but I'd like to be able to test with data that's nearly up to date (a few hours old) as I approach the "cutover".

    Jim

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

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