July 21, 2011 at 7:12 am
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
July 21, 2011 at 7:16 am
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. SelburgJuly 21, 2011 at 8:55 am
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