[AccessD] Access FE & MSDE BE?

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






More information about the AccessD mailing list