Access Project connecting to multiple SQL Servers Greg from FL 05.07.2006 12:39:01 Good morning. We are having an issue and would like to see what our solution could be. We have an Access 2003 Project (.adp) connected to a SQL 2000 Server and all works great. We are adding an additional link to a SQL 2005 server. When the views are run individually, they work fine. However, when we try to create view from those linked servers it times out. We have changed the timeout to 0 and it never responds. So we’ve tried using the SQL 2005 server as the primary server for an Access 2003 Project (.adp) and then linking the SQL 2000 server and it works fine. However, same thing, when creating a query off of the two views, this time we get errors like: 1. This version of Microsoft Office Access doesn’t support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft Office Update Web site for the latest information and downloads. Your design changes will not be saved. 2. You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected. You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools. The version of Access 2003 specifically is: Microsoft ® Office Access 2003 (11.6566.6568) SP2 Any assistance you can provide will greatly assist us with our project. Thank you.

Re: Access Project connecting to multiple SQL Servers "Norman Yuan" <NotReal[ at ]NotReal.not> 05.07.2006 14:15:05 That is the fact you have to live with: Acccess2003 or earlier version cannot be used to create server objects (SPs, Views, Tables...) on SQL Server 2005. There seems MS has no plan to fix this in a future service pack since ADP is on its way of phasing out. I do not know if upcoming Access2007 allows creating server object on SQL Server 2005 or not (it still support ADP format). So, to use ADP with SQL Server2005, you have to find other way to create/modify server objects outside your ADP app. "Greg from FL" <Greg from FL[ at ]discussions.microsoft.com> wrote in message news:5A0D9F31-F159-4297-B0BE-E4B197716633[ at ]microsoft.com... [Quoted Text] > Good morning. We are having an issue and would like to see what our > solution > could be. We have an Access 2003 Project (.adp) connected to a SQL 2000 > Server and all works great. We are adding an additional link to a SQL > 2005 > server. When the views are run individually, they work fine. However, > when > we try to create view from those linked servers it times out. We have > changed the timeout to 0 and it never responds. So we've tried using the > SQL > 2005 server as the primary server for an Access 2003 Project (.adp) and > then > linking the SQL 2000 server and it works fine. However, same thing, when > creating a query off of the two views, this time we get errors like: > > 1. This version of Microsoft Office Access doesn't support design changes > with the version of Microsoft SQL Server your Access project is connected > to. > See the Microsoft Office Update Web site for the latest information and > downloads. Your design changes will not be saved. > 2. You have connected to a version of SQL Server later than SQL Server > 2000. The version of Visual Studio or Access that you are using was > released > before the version of SQL Server to which you are connected. For this > reason, you might encounter problems. Please check with Microsoft to see > if > there is a service pack that you should apply to Visual Studio or Office > in > order to get support for the version of SQL Server to which you are > connected. You can continue but any new object types might not be > enumerated, and it will not be possible to save any objects or database > diagrams that you create using the Visual Database Tools. > > The version of Access 2003 specifically is: Microsoft ® Office Access > 2003 > (11.6566.6568) SP2 > > Any assistance you can provide will greatly assist us with our project. > Thank you. >

Re: Access Project connecting to multiple SQL Servers "Gil Lapid Shafriri" <gilsh[ at ]microsoft.com> 08.07.2006 07:44:47 Access2007 will support creating server objects on SQL Server 2005. Also I don't think the statement that ADP is phasing out is accurate. There is a lot of confusion around this but the bottom line is that it will be supported going forward. Gil Lapid Shafriri Program Manager SQL Server Migration Assistant Microsoft http://www.microsoft.com/sql/solutions/migration/default.mspx "Norman Yuan" <NotReal[ at ]NotReal.not> wrote in message news:OxEDm0DoGHA.4172[ at ]TK2MSFTNGP03.phx.gbl... [Quoted Text] > That is the fact you have to live with: Acccess2003 or earlier version > cannot be used to create server objects (SPs, Views, Tables...) on SQL > Server 2005. There seems MS has no plan to fix this in a future service > pack since ADP is on its way of phasing out. I do not know if upcoming > Access2007 allows creating server object on SQL Server 2005 or not (it > still support ADP format). > > So, to use ADP with SQL Server2005, you have to find other way to > create/modify server objects outside your ADP app. > > "Greg from FL" <Greg from FL[ at ]discussions.microsoft.com> wrote in message > news:5A0D9F31-F159-4297-B0BE-E4B197716633[ at ]microsoft.com... >> Good morning. We are having an issue and would like to see what our >> solution >> could be. We have an Access 2003 Project (.adp) connected to a SQL 2000 >> Server and all works great. We are adding an additional link to a SQL >> 2005 >> server. When the views are run individually, they work fine. However, >> when >> we try to create view from those linked servers it times out. We have >> changed the timeout to 0 and it never responds. So we've tried using the >> SQL >> 2005 server as the primary server for an Access 2003 Project (.adp) and >> then >> linking the SQL 2000 server and it works fine. However, same thing, when >> creating a query off of the two views, this time we get errors like: >> >> 1. This version of Microsoft Office Access doesn't support design >> changes >> with the version of Microsoft SQL Server your Access project is connected >> to. >> See the Microsoft Office Update Web site for the latest information and >> downloads. Your design changes will not be saved. >> 2. You have connected to a version of SQL Server later than SQL Server >> 2000. The version of Visual Studio or Access that you are using was >> released >> before the version of SQL Server to which you are connected. For this >> reason, you might encounter problems. Please check with Microsoft to >> see if >> there is a service pack that you should apply to Visual Studio or Office >> in >> order to get support for the version of SQL Server to which you are >> connected. You can continue but any new object types might not be >> enumerated, and it will not be possible to save any objects or database >> diagrams that you create using the Visual Database Tools. >> >> The version of Access 2003 specifically is: Microsoft ® Office Access >> 2003 >> (11.6566.6568) SP2 >> >> Any assistance you can provide will greatly assist us with our project. >> Thank you. >> > >