[AccessD] Errors when connecting to SQL 2005 as BE

Well this is MS recommendation as they are talking about phasing out
ADP's, whether this happens probably depends on customer clamour.
It does require a bit more involved knowledge of  SQL like ADO , roles and
 SP's, You will also probably have to understand the changes to SQL
DMO and DTS packages in 2005. You will need your own development copy
of SQL Server. Using an mdb will probably increase your development time.
You might get away with doing the backend development in an
Access 2007 ADP and then accessing through through a 2003 mdb.
Haven't done it though. But it would give you two different perspectives.


Dan Waters wrote:

>Marty,
>
>What are your thoughts on using Access 2003 mdb as a FE, with SQL 2005
>Server (Full or Express) as the BE, and SQL Server Management Studio (Full
>or Express) as the GUI for SQL?  
>
>I have only worked with Management Studio (Express) a little, but it sure
>looks like a good tool for managing SQL Server.
>
>Dan Waters
> 
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
>Sent: Tuesday, October 17, 2006 12:16 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Errors when connecting to SQL 2005 as BE
>
>I think you have 3 choices.
>
>1. Create your ADP with Access 2007
>
>2. Install SQL Server 2005 or SQL Express in SQL 2000 degraded mode
>    and use Access 2003 ADP. You may not be able to run both normal version
>    and degraded on the same server, I would have to check if this is 
>now allowed.
>    There were problems with SQL-SMO in beta's.
>   
>3. Use the new version of SQL Server 2005 EM to make design backend changes
>with Access 2003 ADP. If using SQL Express before you install the new EM
>SQL Server Management Studio Express,  you must pre-install MSXML 6.0.
>The full install of SQL Server 2005 comes with it's own Management Studio.
>
>It might be easier to develop with Access 2003 against SQL 2000
>and then upgrade your final backend to SQL 2005.  I don't know
>the ramifications of doing this. To get a some of SQL 2005 newer innovations
>you may have to use accdb format which is the upgraded version of mdb.
>
>See for explanation and differences if using SQL Express with the Advanced
>Reporting Services etc. version. This site also has SQL BOL downloads. As
>well as latest SQL Express downloads.
>
>http://msdn.microsoft.com/vstudio/express/sql/download/
>
>Gowey Mike W wrote:
>
>  
>
>>Hi All,
>>
>>I have just started to play with changing one of my Microsoft Access
>>2003 Projects so that it connects to our new SQL Server 2005.  It
>>connects fine to 2005 but when I attempt to design or change my
>>exsisting views I get the following errors.  Does anyone know what type
>>of patches or software I need to install in order to make it work?  I am
>>currently running Office 2003 SP2.  I went to the web site but I don't
>>really see anything that helps.
>>
>>"Your version of Equipment Inventory 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."
>>
>>"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."
>>
>>
>>TIA,
>>
>>Mike
>> 
>>
>>    
>>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada