Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 20th, 2007, 09:07 PM
Registered User
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using linked SQL servers data in Access

Hi everybody,

I have two sql servers on different computers. One is SQL 2000 on the computer 1 and other is SQL 2005 on computer 2. On SQL 2000 we have our working database and we are planning to migrate to SQL 2005. On the computer 2 in the SQL 2005 environment I've added linked server using sp_addlinkedserver. Now in SQL 2005 I can access SQL 2000 data. And on SQL 2005 I have some testing database I'd like to work with.

The problem is that we are using Access as a Front End. When Access tries to call stored procedure that in turn contain calls to linked server tables _sometimes_ there is the following error:
"An existing connection was forcibly closed by remote host" or
OLE DB provider 'SQLNCLI' for linked server returned message "Communication link fail" or just "ODBC error".
We could resolve this problem going to SQL 2005 and opening this stored procedure (with the call to linked table) directly from the server. After that Access is working for some time. Then we receive our errors again.
Does anybody have any suggestions?

Old June 21st, 2007, 05:12 PM
Registered User
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Just had another problem with link server, but managed to successfully decide it.
Will describe it here, may be it'll help somebody.
In the SQL server there is a view, that uses data from the linked server. Then in the stored procedure we use this view for updating some information in the database. The update statement tries to change data in the linked table. This update statement was producing an error. Instead of update statement I've just created a cursor and made changes in the cycle and this works.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked Servers anothervbaddict SQL Server 2000 5 November 30th, 2007 07:31 AM
LINKED Servers. WebLadyBug SQL Server 2005 1 April 9th, 2007 01:27 AM
Linked Servers msrnivas .NET Web Services 1 January 8th, 2005 09:27 AM
linked servers msrnivas General .NET 1 November 26th, 2004 01:19 AM
linked servers bukky Classic ASP Databases 1 March 6th, 2004 06:31 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.