MartyConnelly
martyconnelly at shaw.ca
Wed Mar 16 17:13:02 CST 2005
There is no connection limit on SQL Express 2005 (new MSDE name) There is a 4 gig limit I think. ADP's will only connect to SQL Express with a lot of limits on creating tables through ADP etc. ADP's will work as before if SQL Express is in initially installed with some sort of backwards compatibilty mode kludge A new EM was released in Jan or Dec for SQL Express. Here are some notes I have collected off the SQL 2005 Beta newsgroups http://communities.microsoft.com/newsgroups/default.asp? icp=sqlserver2005&slcid=us It seems to suggest that ADP is being downgraded after 2003 so I would not rush to use ADP's for future development. One reason is DMO has been totally replaced by SMO. Also for this reason you can't easily run SQL Server 2000 and 2005 on same machine, so don't go installing SQL 2005 Express on machines willy nilly without expecting MSDE to get clobbered. So I would install the Betas on a test development sysytem. There have been a freeware new Beta QA and EM for 2005 from MS released in the last month. Just remember they are still tinkering with the Beta and another release in June. Thinking about Access and SQL Server 2005? Might I suggest: "Plan to plan and test carefully too." http://lab.msdn.microsoft.com/express/sql/ http://www.tegels.org/qara/ There's been an interesting thread on the SQL Server Newsgroups about this topic. A developer was trying to create an ADP to work against express and got this message: 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. Mary Chipman from Microsoft posted this response: You will not be able to use any of the designers with SQLS 2005 databases, whether it's SQL Express or the Developer edition. IOW, you won't be able to create databases, tables, views or any other database objects from an ADP. The only support that is envisioned is that you will be able to connect an Access front-end to a SQLS 2005 back end if it is running in SQLS 2000 compatibility mode, so your forms, reports and other local Access objects should still run. There is no service pack or quick fix being planned as far as I know because of the amount of work it would entail. If you stop to think about it, it's pretty hard to see how accomodating new Yukon features like CLR assemblies and complex data types in the ADP designers could be achieved without a complete rewrite. That said, with Access 2003, I was able to connect up to an instance of SQL 2005 (not in 2000 compatibility mode) and work with data with SQL2000 compatible data types. I was also able to stick XML into an XML-typed (but not strongly-typed) column and have it work as expected. The bottom line here seems to be that ADPs aren't worth investing new work into today if you plan to go to SQL Server 2005 with them. However, my limited testing of Access 2003 as the Frontend and SQL Server 2005 as backend using linked tables seems to be okay. Time will tell, of course. microsoft.private.sqlserver2005.dataaccess http://communities.microsoft.com/newsgroups/default.asp? icp=sqlserver2005&slcid=us To amplify what Kent said, there are no plans for supporting designing SQL Server objects using Access ADPs either now or in the future. You can use Access to connect to a SQL Server 2005 database in 2000 compatibility mode, but there is no support for new 2005 functionality being planned. It is recommended that you use the client tools in SQL Server for creating new SQL Server objects. There is also support in Whidbey for creating SQL Server objects. posted on Friday, August 20, 2004 2:03 PM Feedback # re: Thinking about Access and SQL Server 2005? 8/22/2004 12:26 PM Rick Heiges This is good info! I have connected and used several tools with warning messages stating that it probably will not work from Access 2003. From some other threads in the newsgroups, it sounds like ADPs are being hung out to dry. I have mixed feelings about this. ADPs would allow someone with a bunch of Access programming background to leverage that knowledge to work with a SQL Server backend, but there are lots of reasons to not do this too. Access 2000 and SQL Server 2005: Just say no! If memory serves me right, I've talked before about the lack of support that SQL Server 2005 will provide for Access 2000 and my advice has been just say no! The primary reason for this is that the internals of SQL Server 2005 will be considerable different: just consider the XML type, for example. Access 2000 was geared toward SQL Server 7. There's been reports of some features of Access 2000 just not working as well as they should with SQL Server 2000. But I feel that Mary Chipman put it best recently in one of the SQL Server 2005 newsgroups. ...the Access upsizing wizard will only work with SQL Server 2000 editions. There are no plans at this time to rewrite it to work with SQLS 2005. You will also not have the capability to create or modify SQLS 2005 database objects in an Access project (ADP), only to use it as a front-end connected to a "finished" SQLS 2005 database running in SQLS 2000 compatibility mode. Microsoft Moving Away from ADPs in Access Kent Tegels quotes Mary Chipman (who wrote the book) in the SSXE newsgroup as saying that Microsoft are now recommending moving away from ADP based solutions. It looks like this may be an experiment that has not been completely successful which leaves those of us who have implemented these solutions with an interesting support problem going forward. Here is the quote: However, for new application development, ADPs aren't looking so promising, especially if you are thinking in the Yukon timeframe. A couple of problematical issues are complex data types and CLR assemblies. Tackling these head-on in the ADP UI graphical tools in the next version of Access is a daunting challenge, to say the least. ...and... FWIW, the Access team has moved away from recommending ADPs as a front-end to SQLS apps over the last year or so, based on several public talks given by team members at industry conferences. If you are contemplating new development with Access as a FE to a SQLS BE, you'll likely be ahead of the game with an efficiently-designed MDB/linked table solution rather than an ADP. FAQ: How to connect to SQL Express from "downlevel clients"(Access 2003, VS 2003, VB 6, etc(basically anything that is not using .Net 2.0 or the new SQL Native Client)) http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx Martin Reid wrote: > Have a look at the new version > > http://www.microsoft.com/sql/express/ > > > Martin > > > > ----- Original Message ----- From: "Stuart McLachlan" > <stuart at lexacorp.com.pg> > To: "Access Developers discussion and problemsolving" > <accessd at databaseadvisors.com> > Sent: Wednesday, March 16, 2005 1:47 AM > Subject: RE: [AccessD] Access FE & MSDE BE? > > > On 15 Mar 2005 at 19:26, Dan Waters wrote: > >> Is this correct? >> >> The 25 limit is not based on the number of people logged on, but on the >> number of client computers concurrently connecting to the MSDE BE. >> More can >> be logged on. >> > OK, I've just done it bit more searching. > > JC and I are both out of date with our 5. It's 8 in MSDE2000 > > http://msdn.microsoft.com/library/default.asp?url=/library/en- > us/dnmsde2kwrk/html/msde2000webapp.asp > <quote> > MSDE 2000 employs what is known as a concurrent Workload Governor. The > effect of the governor is to slow certain operations down by stalling > user > connections for a few milliseconds whenever there are more than eight > concurrent operations. Some system-generated events in the database > engine > count against this eight-operation limit, so the governor may kick in > even > when your application code requests fewer than eight operations. The > key is > concurrent operations, such as executing a query. This is not the same as > concurrent users. > </quote> > > The supposed 25 limit (which appears to be an approximation of how many > users before the slowdown becomes noticeable) comes from a number of > different places including: > http://www.microsoft.com/hk/sql/evaluation/overview/default.mspx > <quote> > SQL Server 2000 Desktop Engine (MSDE 2000) is a free, redistributable > version of SQL Server. Third-party software developers can include it in > applications they build that use SQL Server to store data. MSDE is an > ideal > solution for: > Client applications that require an embedded database. > Basic websites that serve up to 25 concurrent users. > </quote> > > and http://www.microsoft.com/sql/msde/default.asp > <quote> > Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, > redistributable version of SQL Server that's ideal for client > applications > that require an embedded database, new developers learning how to build > data-driven applications, and Web sites serving up to 25 concurrent > users. > </quote> > -- Marty Connelly Victoria, B.C. Canada