By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,810 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,810 IT Pros & Developers. It's quick & easy.

ADP Applications

P: n/a
Is it possible to create an .ADP application (in Access 2003) without
having to connect SQL Server but using directly the tables and queries
inside the .ADP file?

Thanks

Sep 8 '06 #1
Share this Question
Share on Google+
26 Replies


P: n/a
No.

There are no tables/queries/sprocs/views etc in an ADP file, they only exist
in the SQLS DB.

You MUST connect.

BTW ADP's are not the recommended approach anymore as they have been
deprecated (thrown on the scrapheap).
--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

<ga***********@libero.itwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Is it possible to create an .ADP application (in Access 2003) without
having to connect SQL Server but using directly the tables and queries
inside the .ADP file?

Thanks

Sep 8 '06 #2

P: n/a
Hi Craig,

Do you know why ADPs have been deprecated? (I just fell in love with them!)

Or, any links you could provide would be appreciated.

Thanks Craig,
"Craig Alexander Morrison" <ca*@microsoft.newsgroups.public.comwrote in
message news:45******@212.67.96.135...
No.

There are no tables/queries/sprocs/views etc in an ADP file, they only
exist in the SQLS DB.

You MUST connect.

BTW ADP's are not the recommended approach anymore as they have been
deprecated (thrown on the scrapheap).
--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

<ga***********@libero.itwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>Is it possible to create an .ADP application (in Access 2003) without
having to connect SQL Server but using directly the tables and queries
inside the .ADP file?

Thanks


Sep 8 '06 #3

P: n/a
ADPs have been replaced by .Net. Too many issues with ADPs. ADPs are
just not robust/flexible enough for heavy enterprise usage (my personal
experience).

Reliable usage of Access is only guaranteed at the desktop level. It is
only a desktop application. For enterprise level/server based
operations .Net is the recommended platform.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 8 '06 #4

P: n/a
Isn't that like apples and oranges though? Isn't saying "ADP is replaced by
..net" like saying "My bicycle was replaced by my car". No need to deprecate
the bicycle it's still useful. I think that ADPs fill a gap that is needed,
allowing a quick and easy interface for the small to medium business with
added convenience of being able to scale up when / if the time comes. I
have done a little searching, but the only thing I could find is
contradictory to the deprecation idea at
http://msdn.microsoft.com/chats/tran...ce_052002.aspx

"A: Access is an incredibly important product and will continue to play a
significant role. I work on the Access team and I want to believe that we
are building on the great success of Access in the future. Keep in mind;
although Jet is a deprecated feature of MDAC, it still serves important user
scenarios that SQL Server for the desktop doesn't address. We have no plans
to discontinue MDB based features in Access. With that said, ADP projects
are a great way to develop against SQL Server in a connected environment. We
have added features to ADPs that bring the SQL Server experience closer to
what you had in Jet. We will continue to support future releases of SQL
Server engines with ADPs."

If ADPs are in fact "on the scrap heap", could someone send me a link that
would explain why?

Thanks!

"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
ADPs have been replaced by .Net. Too many issues with ADPs. ADPs are
just not robust/flexible enough for heavy enterprise usage (my personal
experience).

Reliable usage of Access is only guaranteed at the desktop level. It is
only a desktop application. For enterprise level/server based
operations .Net is the recommended platform.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 8 '06 #5

P: n/a
Oops, I just noticed the date of the post, but still, I'm trying to find
something that answers the deprecation issue.
"Rico" <me@you.comwrote in message news:ZjjMg.517871$Mn5.3257@pd7tw3no...
Isn't that like apples and oranges though? Isn't saying "ADP is replaced
by .net" like saying "My bicycle was replaced by my car". No need to
deprecate the bicycle it's still useful. I think that ADPs fill a gap
that is needed, allowing a quick and easy interface for the small to
medium business with added convenience of being able to scale up when / if
the time comes. I have done a little searching, but the only thing I
could find is contradictory to the deprecation idea at
http://msdn.microsoft.com/chats/tran...ce_052002.aspx

"A: Access is an incredibly important product and will continue to play a
significant role. I work on the Access team and I want to believe that we
are building on the great success of Access in the future. Keep in mind;
although Jet is a deprecated feature of MDAC, it still serves important
user scenarios that SQL Server for the desktop doesn't address. We have no
plans to discontinue MDB based features in Access. With that said, ADP
projects are a great way to develop against SQL Server in a connected
environment. We have added features to ADPs that bring the SQL Server
experience closer to what you had in Jet. We will continue to support
future releases of SQL Server engines with ADPs."

If ADPs are in fact "on the scrap heap", could someone send me a link that
would explain why?

Thanks!

"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
>ADPs have been replaced by .Net. Too many issues with ADPs. ADPs are
just not robust/flexible enough for heavy enterprise usage (my personal
experience).

Reliable usage of Access is only guaranteed at the desktop level. It is
only a desktop application. For enterprise level/server based
operations .Net is the recommended platform.

Rich

*** Sent via Developersdex http://www.developersdex.com ***


Sep 8 '06 #6

P: n/a
Think about Access liket his: 15 years or so ago, Access drove the
desktop version of Dbase apps into extinction - almost overnight.
Before Access, Dbase was the rage. 15 years is pretty good longevity
for an application. But computer usage has grown exponentially over the
last 15 years. So rather than have one application that does it all,
desktop and enterprise, Microsoft focusing enterprise level operations
on .Net/sql server and desktop operations remain with Access. Rather
than have one app that is a master of nothing - better to have several
apps that each master a specific thing.

Kind of like Mac is better for graphics than windows - but that's it.
Where windows does 10 times as much stuff as Mac. Sun systems probably
process data better than windows, but that's it. So Access is the king
of desktop operations. That's it. .Net rules for enterprise stuff.
..Net would be overkill for desktop stuff. That is why there is still
Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 8 '06 #7

P: n/a
Hi Rich,

I understand what you're saying, but that wouldn't explain the deprecation
of the ADP. Not all clients have the budget for a .net programmer, yet
still have the basic fundamental needs of an enterprise system. The gap
between the two (minor desktop app and enterprise system) would be pretty
big without ADP.

Rick

"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
Think about Access liket his: 15 years or so ago, Access drove the
desktop version of Dbase apps into extinction - almost overnight.
Before Access, Dbase was the rage. 15 years is pretty good longevity
for an application. But computer usage has grown exponentially over the
last 15 years. So rather than have one application that does it all,
desktop and enterprise, Microsoft focusing enterprise level operations
on .Net/sql server and desktop operations remain with Access. Rather
than have one app that is a master of nothing - better to have several
apps that each master a specific thing.

Kind of like Mac is better for graphics than windows - but that's it.
Where windows does 10 times as much stuff as Mac. Sun systems probably
process data better than windows, but that's it. So Access is the king
of desktop operations. That's it. .Net rules for enterprise stuff.
Net would be overkill for desktop stuff. That is why there is still
Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 8 '06 #8

P: n/a
I was actually going to address that, but decided to let you bring it up
first. Yes, money is always an issue. You are suggesting that the
Access route would be cheaper because you don't have to pay for the high
$ of .Net expertise. Well, you get what you pay for. If your business
is going to grow you will eventually have to bite the bullet. A lot of
my current work has been the migration of legacy Access systems (mostly
using ADPs of all things) to .Net.

If your system has not been in place for too long, I would highly
recommend stepping up to the .Net platform. Eventually, you will have no
choice. I have one project that is this huge ADP thing. very
sophisticated for Access, but having write conflicts all over the place,
and users have to push data for their viewing to the same tables that
other users have to rather than using the disconnected datasets of .Net
to view their data. Very cumbersome (with all due respect to Access).
The old system distinguished datasets by userIDs. Person1 pulls his
dataset using his userID. person2 pulls her dataset to the same table
using her userID. It's not like you can just keep creating viewing
tables all over the place. So .Net came up with the inMemory
disconnected datasets - very efficient.

Plus, .Net has taken the best of Microsoft Office and made it better.
The ReportViewer of .Net is way more flexible than Access or Excel. And
.Net forms have been greatly improved over previous like MFC, VB6,
Access, etc forms. yes, there is a slight learning curve to write .Net
code, but way easier than MFC, and the debugging is unreal - you almost
can't make a mistake because .Net catches everything as you type.

I stay up with Access stuff because lots of people/places still have
lots of legacy Access apps, and the best way to stay up with it is well
- this NG.

My bottom line humber opinion for you is to not put too much energy into
the ADP for enterprise operations, and the mdb should be reserved for
personal use or training only. It is the way of the data beast. It
just keeps growing and you need bigger guns to deal with it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 8 '06 #9

P: n/a
A lot of
my current work has been the migration of legacy Access systems (mostly
using ADPs of all things) to .Net.
Likey these applications were not designed well. ADP's scale very well, and
there
are 1000 user seat system systems out there.
users have to push data for their viewing to the same tables that
other users have to rather than using the disconnected datasets of .Net
to view their data.
That is just a case of poor desing. what was not a view/query used?

The above is not a case at all for disconnned datasets. If the ADP has a
good desing, the above is not a problem at all.
So .Net came up with the inMemory
disconnected datasets - very efficient.
It is not necessary efficient. Especially when you throw in the over head to
open
connection, grab data, then close it. However, it TENDS to be a betters
solution because the web often requites a poor connection, and in this
regards, for cell phones, pda, or internet connections, then the
disconnected model works better. It not necessary more efficient at all, but
just more in line with what new devices like smart phones need. If you wind
up pulling more data to that local recordset, and closing + opening the
connection over an over, you actually are doing a worse job.
.Net has taken the best of Microsoft Office and made it better.
The ReportViewer of .Net is way more flexible than Access or Excel.
That is debatable. Again, different is a better word. The ability to write
VBA
code for each part of reports formatting event is rather impressive. If you
are doing mailing labels, or invoicing, or financial reports, the ms-access
report writer is darn good, further, the sub-report ability means that you
can naturally model the one-to many report data, and not have to write
ONE LINE of code. From my experience, the combitation of code
+ sub-reprots in ms-access is still unbeatble...after 15 years!!!
Net forms have been greatly improved over previous like MFC, VB6,
Access, etc forms.
Well, access forms ran circles around VB6 forms. And, to build grids of
data, you don't have to write code. Here is some screen shots of what I
mean:

http://www.members.shaw.ca/AlbertKal...icles/Grid.htm

I NOT going to knock .net but the productivity and production of
applications in ms-access is still MUCH MUCH higher then is
..net.

Projects that costed $12,000 in ms-access still wind up costing
$30,000 in .net. Applicaton development in .net is still slower,
and much more costly then ms-access.

ADP's are fully supported in the next version, and they do scale to large
numbers of users without any problems.

Of course, most of us developers PREFER using linked tables to sql server,
as they are more flexible, and generally perform just as well as ADP
projects in the hands of an experienced developer anyway..
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Sep 8 '06 #10

P: n/a
Hi Rich,

You're sounding too much like a corporate automaton! (kidding, just giving
you a hard time)

I have nothing but great things to say about .net, I love writing with
VB.net and ADO.net and ASP.net but I think you're missing the point. I do
disagree with the "you get what you pay for" when you atalk about the
difference between .net and ADP for a number of reasons. When Joe's Pet
Store decides he is starting to grow and wants to have a back office system
connected to his single cash register, he can't afford the bill to use SQL
Server and .net when he can use an ADP to build the same solution quicker,
even using some of his existing business logic, forms and reports that are
in his existing Access mdb database. This also prepares Joe to scale up
WHEN THE TIME COMES without much expense, even if it is to a .net platform.
Reason being, the back end is already normalized, already there, already
populated without conversion, the application developer would just have to
create the front end.

For me, I charge my hourly rate no matter whether it's Joe or Western
Pacific National, their money equally good to me. I don't provide less
service because Joe isn't a national fortune 500 company (re: ya get what
you pay for) yet I can provide the solution he needs at an affordable price.
When he's ready to make a move to a larger system, or more web enabled
system, the ramp up is easy, because there is no data conversion required,
just build around the existing SQL database.

Re: "Taking everything from Office and making it better", that's similar to
saying that Lincoln took everything from the Pinto and made it better. I
don't have decent control over a continuous form type object (the data grid
just doesn't do the trick for me). There is a time and place for both, and
they are both different tools for different jobs.

I hope you don't mind me being devils advocate among other things, not
slamming, just making my points. There is still the outstanding question
about "Why would the ADP be decprectated?"

Rick


"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
>I was actually going to address that, but decided to let you bring it up
first. Yes, money is always an issue. You are suggesting that the
Access route would be cheaper because you don't have to pay for the high
$ of .Net expertise. Well, you get what you pay for. If your business
is going to grow you will eventually have to bite the bullet. A lot of
my current work has been the migration of legacy Access systems (mostly
using ADPs of all things) to .Net.

If your system has not been in place for too long, I would highly
recommend stepping up to the .Net platform. Eventually, you will have no
choice. I have one project that is this huge ADP thing. very
sophisticated for Access, but having write conflicts all over the place,
and users have to push data for their viewing to the same tables that
other users have to rather than using the disconnected datasets of .Net
to view their data. Very cumbersome (with all due respect to Access).
The old system distinguished datasets by userIDs. Person1 pulls his
dataset using his userID. person2 pulls her dataset to the same table
using her userID. It's not like you can just keep creating viewing
tables all over the place. So .Net came up with the inMemory
disconnected datasets - very efficient.

Plus, .Net has taken the best of Microsoft Office and made it better.
The ReportViewer of .Net is way more flexible than Access or Excel. And
Net forms have been greatly improved over previous like MFC, VB6,
Access, etc forms. yes, there is a slight learning curve to write .Net
code, but way easier than MFC, and the debugging is unreal - you almost
can't make a mistake because .Net catches everything as you type.

I stay up with Access stuff because lots of people/places still have
lots of legacy Access apps, and the best way to stay up with it is well
- this NG.

My bottom line humber opinion for you is to not put too much energy into
the ADP for enterprise operations, and the mdb should be reserved for
personal use or training only. It is the way of the data beast. It
just keeps growing and you need bigger guns to deal with it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 8 '06 #11

P: n/a
On Fri, 8 Sep 2006 16:15:00 +0100, "Craig Alexander Morrison"
<ca*@microsoft.newsgroups.public.comwrote:

Deprecated?
As in "you can't do ADP in the forthcoming Access 2007 anymore"?
Not so. ADP is fully supported in A2007.

Please reveal your source of information.

-Tom.

>No.

There are no tables/queries/sprocs/views etc in an ADP file, they only exist
in the SQLS DB.

You MUST connect.

BTW ADP's are not the recommended approach anymore as they have been
deprecated (thrown on the scrapheap).
Sep 9 '06 #12

P: n/a
Rico wrote:
I hope you don't mind me being devils advocate among other things, not
slamming, just making my points. There is still the outstanding question
about "Why would the ADP be decprectated?"
Because they can expose data to the user in uncontrolled situations.
The recommended solution is to create Application Roles.
Application Roles in Access are so bizarre and their operation so
arcane that they range from rendering an application useless down to
making its development super expensive.
IMO MS did not wish or were unable to correct these problems. Trashing
ADPs is a cheap alternative.

I still develop ADPs for my personal use, but I do not create a
persistent connection. I set form and report recordsets individually
using plain old vanilla ADO. I do so because ADPs have capabilities
that MDBs of the same version do not have.

Regardless ADPs are going, going, gone.

I have experimented with .Net. I have yet to achieve the state where
..Net applications are doing what I want them to do in the way I want
them to do it. I have never liked Big Brother Development and I don't
like it now. So I am unlikely to embrace .Net any time soon, (Ditto for
Access 2007).

I will continue to explore alternatives. Right now I'm using (gasp!)
ASP and ADO and HTML and HTAs. Of course, reporting is a major problem.
Crystal Reports can help with this, I believe.

Sep 9 '06 #13

P: n/a
ADPs are slightly hidden away in A2007.

Deprecate means depreciate, belittle, disapprove.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

"Tom van Stiphout" <no*************@cox.netwrote in message
news:re********************************@4ax.com...
On Fri, 8 Sep 2006 16:15:00 +0100, "Craig Alexander Morrison"
<ca*@microsoft.newsgroups.public.comwrote:

Deprecated?
As in "you can't do ADP in the forthcoming Access 2007 anymore"?
Not so. ADP is fully supported in A2007.

Please reveal your source of information.

-Tom.

>>No.

There are no tables/queries/sprocs/views etc in an ADP file, they only
exist
in the SQLS DB.

You MUST connect.

BTW ADP's are not the recommended approach anymore as they have been
deprecated (thrown on the scrapheap).

Sep 9 '06 #14

P: n/a
Rico wrote:
Hi Craig,

Do you know why ADPs have been deprecated? (I just fell in love with
them!)
Or, any links you could provide would be appreciated.
ADPs have NOT been depracated. They were just never a great idea and to the
extent that they could have been a good idea there are too many problems with
them that MS has apparently decided aren't cost-effective to correct.

That being said this conversaton has painted the picture that the choice is ADPs
or dot net. MDBs with SQL Server back ends are superior to both of those IMO.
You get the advantages of a true server back end and still all of the RAD
capabilities of Access.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 9 '06 #15

P: n/a
Just a disclaimer, but what I suggest here is just my opinion based on
experience and observation. Depending on the size of your business, you
can use the ADP. I just wouldn't advise it if you will have lots of
data/lots of users/and lots of data movement. The ADP is OI of an
interface for the not too sophisticated type operations. But anyone who
has done extensive enterprise level developing in Access and extensive
developing in OOP (object oriented programming - i.e. .Net) will tell
you that OOP os really the way to go. Access is faster because it is
simpler, but when you start encountering issues because too many people
are hitting the DB at the same time, then Access won't be so fast.

The deal is that today's enterprise market is not the same market that
Access started in back in the early 90s. Microsoft has been keeping up
with the times to meet the needs of today's enterprise market, and the
focus has shifted away from Access for today's enterprise market. This
doesn't mean that Access is no longer a great product. It just means
that the purpose/objective of Access has changed - it is not an
enterprise level application (anymore). How you use it is your call. I
wish you well, and it is always a tough decision when you have these
kinds of options which way to go. I just happen to have had better luck
with .Net instead of Access for today's enterprise market. I could site
example after example, but I am not looking to get into a
dispute/discussion over this.

Well, I will site one example where .Net is clearly the best choice: I
create a temp table with the test data below. The object in this
example is to evaluate if the sum of the amounts of types 'b' and 'c'
add up to the amount of type 'a' for each user. If yes, then add a 'y'
to the "Matching" column, if they don't add up to the amount for type
'a' then add 'n' in the "Matching" column

create table #tt(userid char(10), type char(1), amount int, matching
char(1))

Insert Into #tt
select 'user1', 'a', 100, null union
select 'user1', 'b', 40, null union
select 'user1', 'c', 60, null union
select 'user2', 'a', 100, null union
select 'user2', 'b', 20, null union
select 'user2', 'c', 80, null union
select 'user3', 'a', 100, null union
select 'user3', 'b', 20, null union
select 'user3', 'c', 70, null

The result set needs to look like this

Userid type amount matching
------------------------------
user1 a 100 Y
user1 b 40 Y
user1 c 60 Y
user2 a 100 Y
user2 b 20 Y
user2 c 80 Y
user3 a 100 N
user3 b 20 N
user3 c 70 N

for Users 1 and 2 the amounts of 'b' and 'c' add up to the amount of
'a'. But for user3 the sum of 'b' and 'c' do not add up to 'a'. Here
is the Tsql that generates this result (which this particular Tsql is
not supported by Access Jet sql in mdbs:

select userid, type, amount,
(select case
sum(case type when 'a' then amount else 0 end)
when
sum(case type when 'b' then amount when 'c' then amount else 0 end)
then 'Y' Else 'N'
end
from #tt a
where a.userid = b.userid
group by userid) 'Matching'
from #tt b

Ycan add this Tsql to an ADP query (which is just a Sql server view),
but that would be static. What if you want to add parameters? The
example above is a bare bones example. Regular ADO can run this query
with params, but you have to write the results to a table on the disk.
Here is where .Net comes in - ADO.Net can write the resultset to a
dataset in memory. This is much more efficient and flexible than the
latter options. Plus, viewing the results in a .Net datagridview blows
the doors off of an Access form (with all due respect. My point is that
the real focus of Microsoft for enterprise development has shifted from
Access to .Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 9 '06 #16

P: n/a
Rich P <rp*****@aol.comwrote in news:4502e05d$0$34069$815e3792
@news.qwest.net:
Ycan add this Tsql to an ADP query (which is just a Sql server view),
but that would be static. What if you want to add parameters? The
example above is a bare bones example. Regular ADO can run this query
with params, but you have to write the results to a table on the disk.
Here is where .Net comes in - ADO.Net can write the resultset to a
dataset in memory. This is much more efficient and flexible than the
latter options.
I, Lyle, think that you, Rich, should write "I, Rich, have to" rather than
"you, Lyle and others, have to".

--
Lyle Fairfield
Sep 9 '06 #17

P: n/a
Just out of curiosity, how long have you been in the business? ..and have
you ever worked as an independant contractor?

Ricl

"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
Just a disclaimer, but what I suggest here is just my opinion based on
experience and observation. Depending on the size of your business, you
can use the ADP. I just wouldn't advise it if you will have lots of
data/lots of users/and lots of data movement. The ADP is OI of an
interface for the not too sophisticated type operations. But anyone who
has done extensive enterprise level developing in Access and extensive
developing in OOP (object oriented programming - i.e. .Net) will tell
you that OOP os really the way to go. Access is faster because it is
simpler, but when you start encountering issues because too many people
are hitting the DB at the same time, then Access won't be so fast.

The deal is that today's enterprise market is not the same market that
Access started in back in the early 90s. Microsoft has been keeping up
with the times to meet the needs of today's enterprise market, and the
focus has shifted away from Access for today's enterprise market. This
doesn't mean that Access is no longer a great product. It just means
that the purpose/objective of Access has changed - it is not an
enterprise level application (anymore). How you use it is your call. I
wish you well, and it is always a tough decision when you have these
kinds of options which way to go. I just happen to have had better luck
with .Net instead of Access for today's enterprise market. I could site
example after example, but I am not looking to get into a
dispute/discussion over this.

Well, I will site one example where .Net is clearly the best choice: I
create a temp table with the test data below. The object in this
example is to evaluate if the sum of the amounts of types 'b' and 'c'
add up to the amount of type 'a' for each user. If yes, then add a 'y'
to the "Matching" column, if they don't add up to the amount for type
'a' then add 'n' in the "Matching" column

create table #tt(userid char(10), type char(1), amount int, matching
char(1))

Insert Into #tt
select 'user1', 'a', 100, null union
select 'user1', 'b', 40, null union
select 'user1', 'c', 60, null union
select 'user2', 'a', 100, null union
select 'user2', 'b', 20, null union
select 'user2', 'c', 80, null union
select 'user3', 'a', 100, null union
select 'user3', 'b', 20, null union
select 'user3', 'c', 70, null

The result set needs to look like this

Userid type amount matching
------------------------------
user1 a 100 Y
user1 b 40 Y
user1 c 60 Y
user2 a 100 Y
user2 b 20 Y
user2 c 80 Y
user3 a 100 N
user3 b 20 N
user3 c 70 N

for Users 1 and 2 the amounts of 'b' and 'c' add up to the amount of
'a'. But for user3 the sum of 'b' and 'c' do not add up to 'a'. Here
is the Tsql that generates this result (which this particular Tsql is
not supported by Access Jet sql in mdbs:

select userid, type, amount,
(select case
sum(case type when 'a' then amount else 0 end)
when
sum(case type when 'b' then amount when 'c' then amount else 0 end)
then 'Y' Else 'N'
end
from #tt a
where a.userid = b.userid
group by userid) 'Matching'
from #tt b

Ycan add this Tsql to an ADP query (which is just a Sql server view),
but that would be static. What if you want to add parameters? The
example above is a bare bones example. Regular ADO can run this query
with params, but you have to write the results to a table on the disk.
Here is where .Net comes in - ADO.Net can write the resultset to a
dataset in memory. This is much more efficient and flexible than the
latter options. Plus, viewing the results in a .Net datagridview blows
the doors off of an Access form (with all due respect. My point is that
the real focus of Microsoft for enterprise development has shifted from
Access to .Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 9 '06 #18

P: n/a
Regular ADO can run this query
with params, but you have to write the results to a table on the disk.
You do? Why not just use pass-through for the sql, and have the results
returned into a reocrdset?

You will wind up with a reocrdset, and that can be assed to a lsitbox (which
is multi-column), or even a sub-form to display the results as a grid...

I don't see why a temp table is needed here once the initial table is
created as above?

Anyway, that not such a great example, as just throwing the raw sql to sql
server (pass-through), and returning a reocrdset should do the trick..

There is NO question that .net brings many advantages, and I would not stand
here and try and ague as such. I guess the issue is at what point, what
size, and what investment of money makes the .net choice a sensible choice
over a application built in ms-access. ms-access is a tool tuned to
building data entry and data edit forms. So, for accounting systems,
payroll systems, purchase orders, reservation systems etc, ms-access is an
IDEAL choice. The production of code and forms is STILL faster then .net.

You would not write a pac-man game in ms-access, but you certainly would in
vb.net

..net is a "general" development system that lets you build a games, or build
a web service. Both of these things are not appropriate in ms-access. So,
..net is designed to do a LOT of things that many applications will not
benefit from.

For most data centric applications, the forms model, and the report model in
ms-access is MUCH LESS work then a .net solution. The .net forms model is
MUCH better then say what old VB6 was. The data binding of .net has taken a
LOT of que's from ms-access,and is a good leap forward. (still not up to the
speed of ms-access however). Unless you talking about a large complex
system, and one that will have more then one developer on the team,
ms-access is usually better choice for business *data* centric applications
built by ONE developer.

There is most certainly some over-lap in the middle sized applications, and
there is MOST certainly a tipping point when .net is a better choice them
ms-access. Issues such as the internet, how data will be shared and used
with other applications, how much of a OO design will benefit the
application. There is a lot of issues that come into play that do make a
good case for .net.

Further, ms-access does allow the creating and using of class objects, and
further, ms-access applications can now consume web services built with .net
via the soap tool kit add in. And, ms-access has received some niece xml
support. Further, ms-access support of xml has been extended to complex data
types for a2007 (this is partly due to increased operability with share
point services. So, It is not exactly like ms-access is being left behind
technology wise here, but, in fact is going along for a very nice ride to
work with all this new stuff...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Sep 10 '06 #19

P: n/a
The think you should think about is that .Net did not come about just
because Microsoft decided they needed to invent something new. It came
about because of Necessity. Yes, there are always workarounds that can
be done in Access that .Net does as far as data processing. But, for
example, I reduced a giant ADP application with hundreds of tables,
procedures, UDFs and tens of thousands of lines of code to just a few
dozen tables, procedures, udfs and only a few thousand lines of code.
It took me about a year. The benefit of this upgrade was that everytime
the business needed to add something or make a change, I didn't have to
wade through tens of thousands of lines of code, and didn't have to deal
with all the dependencies of hundreds of tables, procedures... I was
able to encapsulate the majority of the processing within the .Net
application.

As for my example, it is just an example. The workaround in Access
would be to add a column to the table for each user. But what if you
have 2000 users? That would exceed the column limits of any sql table.
My example is normalized. Of course, if you have 2000 users you could
add a dozen tables. But that is way redundant. In .Net I would just
read the query results into a dataset and display the results in a
datagridview. No tables on the disk.

I am sure that when Access first came out the Dbase people were
stressing because they were going to have learn VBA to use Access. But
the benefit was that Access could produce applications that would take
10 times as long in Dbase and ran way more efficiently. Well, now there
is 10 times as much data to deal with, and Access is where Dbase was 15
years ago. I am sure people are stressing because they are now going to
have to learn .Net to deal effectively with this increased volume of
data and having to deal with data on the web. But the benefit is that
.Net can produce applications that can handle today's volume of data way
more quickly than Access. But the difference between the Dbase/Access
paradigm and the Access/.Net paradigm is that Access was a complete
departure from Dbase. Where .Net is basically an extension of Access
(on the VB side that is). Same object models Just taken to the next
level (the extreme next level).

I still use Access for personal projects like my checkbook, pilot log
book, contacts book. So Access will continue to exist (unlike Dbase)
for personal use and for training - great training tool. I just
wouldn't use it for enterprise development.

As for my experience, I have been doing enterprise level developing for
about 10 years now, and smaller scale developing since the mid 80s. I
started the RDBMS corporate scene with Access2 and VB4 and sql server
6.5. By Access2000 I was stepping up to .Net. At first I could not see
the benefit. But by VS2005, the benefit was quite obvious. VS2005 (.Net
2.0) is to VS2002(.Net 1.0) what Access 20003 was to Access2000 - a
significant improvement - a vast improvement in .Net. Believe me, once
you give VS2005 a try you will see what I mean.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 10 '06 #20

P: n/a
"Rich P" <rp*****@aol.comwrote in message
<45***********************@news.qwest.net>:

I wonder in which world you've found the experience to make some of
these observations ...

Some comments inline:
The think you should think about is that .Net did not come about just
because Microsoft decided they needed to invent something new. It
came about because of Necessity.
Sure, but what has that to do with Access or ADPs? Oh, wait, you mean
Microsoft suddenly understood Access was crap, and decided to invent
..Net to replace it? Come on...
Yes, there are always workarounds
that can be done in Access that .Net does as far as data processing.
But, for example, I reduced a giant ADP application with hundreds of
tables, procedures, UDFs and tens of thousands of lines of code to
just a few dozen tables, procedures, udfs and only a few thousand
lines of code. It took me about a year. The benefit of this upgrade
was that everytime the business needed to add something or make a
change, I didn't have to wade through tens of thousands of lines of
code, and didn't have to deal with all the dependencies of hundreds
of tables, procedures... I was able to encapsulate the majority of
the processing within the .Net application.
This has nothing to do with ADP vs .Net, it's about bad design, and
redesigning. For a good developer, it doesn't matter which tool or
technology is used for the reimplementation, they would simply use
the tool of choice for the organization.

You seem to fancy stuffing all the business logic into the
application. Doing that vs putting it into the database is also an
ongoing discussion, and not everyone would agree that your chosen
design represents good design.

This touches on one of the advantages of ADPs over mdbs, that you can
put more of the business logic on the server.
As for my example, it is just an example. The workaround in Access
would be to add a column to the table for each user. But what if you
have 2000 users? That would exceed the column limits of any sql
table. My example is normalized. Of course, if you have 2000 users
you could add a dozen tables. But that is way redundant. In .Net I
would just read the query results into a dataset and display the
results in a datagridview. No tables on the disk.
If you really believe that to present something in an ADP, where you
can just stuff a query result into a dataset using some .Net
technology, would mean a completely ridiculous denormalization, then
to be *very* diplomatic - I think you are mistaken!

And if I may ask, what on earth made you come to that conclusion?
What's so different between an ADO.Net Dataset and an ADO Recordset
in this context?

And also - what do you mean by "No tables on the disk"? Where does
that come into the equotation? Sure, to have a recordset persistant
between sessions, you could save it to disk, but I don't see the
relevance with regards to your Dataset/denormalization issue.
I am sure that when Access first came out the Dbase people were
stressing because they were going to have learn VBA to use Access.
But the benefit was that Access could produce applications that would
take 10 times as long in Dbase and ran way more efficiently. Well,
now there is 10 times as much data to deal with, and Access is where
Dbase was 15 years ago. I am sure people are stressing because they
are now going to have to learn .Net to deal effectively with this
increased volume of data and having to deal with data on the web.
I fail to see how the amount of data relates to the discussion of
ADP vs .Net, as you don't store the data in the client, neither
should you process all of your data client side. Isn't that what
client server is all about? Processing on the server, showing only
the results on the client?

If amount of data matters for you, then most likely you are doing
something wrong. I don't think it's fair to bash the technology
because of that (though, it is quite common).

Data on the web is a fair point.
But the benefit is that Net can produce applications that can handle
today's volume of data way more quickly than Access.
Eh...

Does an SQL server SP run faster when triggered from a .Net
application vs being triggered from an ADP app?

Do you fill a datagridview, listview or whatnot any faster in any
..Net tools than you do a bound or unbound ADP control?

Is it really true that a n-tier/OO .Net application "handle today's
volume of data way more quicly than Access."? I mean, shuffling
all of those objects back and forth through the layers'n'stuff ...

And why should a client application "handle today's volume of data"
anyway? Shouldn't the server handle this, and only present the
results to the client?
But the
difference between the Dbase/Access paradigm and the Access/.Net
paradigm is that Access was a complete departure from Dbase. Where
.Net is basically an extension of Access (on the VB side that is).
Same object models Just taken to the next level (the extreme next
level).

I still use Access for personal projects like my checkbook, pilot log
book, contacts book. So Access will continue to exist (unlike Dbase)
for personal use and for training - great training tool. I just
wouldn't use it for enterprise development.
But who use Access for Enterprise Level Development?
As for my experience, I have been doing enterprise level developing
for about 10 years now, and smaller scale developing since the mid
80s. I started the RDBMS corporate scene with Access2 and VB4 and
sql server 6.5. By Access2000 I was stepping up to .Net. At first I
could not see the benefit. But by VS2005, the benefit was quite
obvious. VS2005 (.Net 2.0) is to VS2002(.Net 1.0) what Access 20003
was to Access2000 - a significant improvement - a vast improvement in
.Net. Believe me, once you give VS2005 a try you will see what I
mean.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
--
Roy-Vidar
Sep 10 '06 #21

P: n/a
>But who use Access for Enterprise Level Development?
<

I believe the original post of this thread was asking wether to use ADP
for enterprise development or not.

I have used ADPs myself extensively for enterprise developemnt. So I am
basing my comments on my personal experience. With the Advent of
VB2005, it just blows the doors off of anything that is Access related
at the enterprise level. I still love Access and still use it
extensively, but on a much smaller scale now (smaller projects that is).
And, going against my own intution here, I will site one more example
(actually, the same example I posted earlier but from the user
perspective).

You have a query for bringing up lists. The query uses parameters - so
that throws views right out the window because views are static.

OK. Here is my scenario, last year I took over a project that a young
guy fresh out of college started (with no documentation of course - I
guess they don't teach that in college anymore). He did a pretty tight
job (replication, DTS, web stuff, SMTP stuff - hundreds of
SPs/triggers/UDFs... huge system), but started getting tangled up a ways
down into the project and bailed on the company (I think he got a better
opportunity and just left em hanging - didn't want to deal with the mess
he was getting into). I was following his lead (shame on me) at first.
He had to pull these lists that compared lists agains other lists, so he
was generating one list, writing it to a table and then generating
another list - writing that to a table and comparing the 2 lists. You
get multiple users needing to do this simultaneously, he had them
writing to the same tables and was separating their pulls by the user's
userID. The process used several SPs and udfs. It was a few hundred
lines of sql code. Well, it got the job done. It just wasn't very
efficient. I simplified the process down to 5 - 10 lines of sql code
(per list) which I write inLine in a VB2005 app and doesn't write
anything to a table. I could write an SP and return a resultset to the
ADP. But there are already hundreds of SPs in this particular Sql
Server DB. So I write the 5 - 10 lines of code inline in a .Net app and
read the results into a datagridview. This is way simpler than having
to wade through hundreds of SPs.

I write a lot of sql inline because it is just way simpler - and with
the advent of data Adapters in .Net the dataAdapter takes care of a lot
of stuff like concurrency issues, and apostrophe's (that is very
cool)... Plus, the datagridview is like a table view in Access - where
you can stretch a table view in Access and see additional colums where
if that table were in an Access form you have to scroll the form. In
.Net you can stretch the form and the datagridview stretches with the
form (if you set the dock property of the datagridview to Fill). So why
clutter your Sql Server DB with a bunch of redundant tables and SPs
(udfs)... My discussion refers to thousands of users/clients here. I
still deal with a few smaller operations where the ADP gets the job done
just fine. But if these smaller operations decide to grow - .Net is way
more extensible than an ADP.

My point is that .Net has taken Access to the Next level. I started my
microsoft career with Access and still respect Access, but VS2005 has
really simplified RDBMS development at the enterprise level (of course
it would be overkill for a personal checkbook application). Not to
mention that client apps in VB2005 can do way more things than a similar
app in Access because you now have the horsepower of OOP. OOP delegates
really make a differenct in the extensibility of an application. VB2005
retains the Rapid Application Development (RAD) of original VB (VBA) but
with the horsepower of OOP. VB2005 can now do everything that you could
only do in C++/C# - just way faster. Time is money. You might pay a
little more for a .Net guys than an Access guy, but the .Net guy will
build a very sophisticated app much faster than the Access guy - not
that a .Net guy is any smarter - its just improved tools.

Please don't interpret me as bashing Access. I started in Access. I am
just sharing my personal experience where Access is best suited over
.Net. My claim for credibility of what I say to be real is that I am
still gainfully employed doing what I do - which is a solutions provider
(more so than developing apps for sale). I don't do apps for sale. I
just provide solutions applications.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 11 '06 #22

P: n/a
Hey there,

The "Original Post" said nothing about Enterprise applications, you were the
one that bought that to the party. I decided to stop beating a dead horse,
because you're talking alot but not listening. Your comparisons are all wet
to the point where I find it hard to believe you have the experience that
you say you do. I'm not saying you don't have that experience, just that
it's hard to believe. Microsoft built .net to take VB / C# / C++ / ASP to
the next level, not Access. Your examples continue to show bad design and
not reasons why ADP should be deprecated (as stated by the second poster).
If I want to dig in the garden, I use a shovel, not a back hoe; different
tools for different jobs.

Rick

"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
But who use Access for Enterprise Level Development?
<

I believe the original post of this thread was asking wether to use ADP
for enterprise development or not.

I have used ADPs myself extensively for enterprise developemnt. So I am
basing my comments on my personal experience. With the Advent of
VB2005, it just blows the doors off of anything that is Access related
at the enterprise level. I still love Access and still use it
extensively, but on a much smaller scale now (smaller projects that is).
And, going against my own intution here, I will site one more example
(actually, the same example I posted earlier but from the user
perspective).

You have a query for bringing up lists. The query uses parameters - so
that throws views right out the window because views are static.

OK. Here is my scenario, last year I took over a project that a young
guy fresh out of college started (with no documentation of course - I
guess they don't teach that in college anymore). He did a pretty tight
job (replication, DTS, web stuff, SMTP stuff - hundreds of
SPs/triggers/UDFs... huge system), but started getting tangled up a ways
down into the project and bailed on the company (I think he got a better
opportunity and just left em hanging - didn't want to deal with the mess
he was getting into). I was following his lead (shame on me) at first.
He had to pull these lists that compared lists agains other lists, so he
was generating one list, writing it to a table and then generating
another list - writing that to a table and comparing the 2 lists. You
get multiple users needing to do this simultaneously, he had them
writing to the same tables and was separating their pulls by the user's
userID. The process used several SPs and udfs. It was a few hundred
lines of sql code. Well, it got the job done. It just wasn't very
efficient. I simplified the process down to 5 - 10 lines of sql code
(per list) which I write inLine in a VB2005 app and doesn't write
anything to a table. I could write an SP and return a resultset to the
ADP. But there are already hundreds of SPs in this particular Sql
Server DB. So I write the 5 - 10 lines of code inline in a .Net app and
read the results into a datagridview. This is way simpler than having
to wade through hundreds of SPs.

I write a lot of sql inline because it is just way simpler - and with
the advent of data Adapters in .Net the dataAdapter takes care of a lot
of stuff like concurrency issues, and apostrophe's (that is very
cool)... Plus, the datagridview is like a table view in Access - where
you can stretch a table view in Access and see additional colums where
if that table were in an Access form you have to scroll the form. In
Net you can stretch the form and the datagridview stretches with the
form (if you set the dock property of the datagridview to Fill). So why
clutter your Sql Server DB with a bunch of redundant tables and SPs
(udfs)... My discussion refers to thousands of users/clients here. I
still deal with a few smaller operations where the ADP gets the job done
just fine. But if these smaller operations decide to grow - .Net is way
more extensible than an ADP.

My point is that .Net has taken Access to the Next level. I started my
microsoft career with Access and still respect Access, but VS2005 has
really simplified RDBMS development at the enterprise level (of course
it would be overkill for a personal checkbook application). Not to
mention that client apps in VB2005 can do way more things than a similar
app in Access because you now have the horsepower of OOP. OOP delegates
really make a differenct in the extensibility of an application. VB2005
retains the Rapid Application Development (RAD) of original VB (VBA) but
with the horsepower of OOP. VB2005 can now do everything that you could
only do in C++/C# - just way faster. Time is money. You might pay a
little more for a .Net guys than an Access guy, but the .Net guy will
build a very sophisticated app much faster than the Access guy - not
that a .Net guy is any smarter - its just improved tools.

Please don't interpret me as bashing Access. I started in Access. I am
just sharing my personal experience where Access is best suited over
Net. My claim for credibility of what I say to be real is that I am
still gainfully employed doing what I do - which is a solutions provider
(more so than developing apps for sale). I don't do apps for sale. I
just provide solutions applications.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '06 #23

P: n/a
Right. You asked about deprecation. Well, large businesses which are
migrating from mainframes to windows based systems are using OOP front
ends for their DB systsms. ADPs will not suit their needs - banking
industry, supermarkets,.... ADPs will work probably for small
businesses, but why not go for the OOP front end? So micorosft isn't
focusing their energy on Access/ADPs for that sort of thing. Thus, ADPs
are being deprecated. I was just sharing my views on why ADPs are being
deprecated - I didn't even mention all of the issues and errors that I
have had to deal with on ADPs and why I stepped it up to .Net. You are
own on how you want to go.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 11 '06 #24

P: n/a
"Rich P" <rp*****@aol.comwrote in message
<45***********************@news.qwest.net>:
>But who use Access for Enterprise Level Development?
<

I believe the original post of this thread was asking wether to use
ADP for enterprise development or not.
Nope - it was just this:
"Is it possible to create an .ADP application (in Access 2003) without
having to connect SQL Server but using directly the tables and queries
inside the .ADP file?"

You added the "Enterprice Development" and the bad design. BTW, here's
a site where there's sometimes a bit of discussion on "Enterprice
Development" http://thedailywtf.com/ShowForum.aspx?ForumID=12
I have used ADPs myself extensively for enterprise developemnt. So I
am basing my comments on my personal experience. With the Advent of
VB2005, it just blows the doors off of anything that is Access
related at the enterprise level. I still love Access and still use
it extensively, but on a much smaller scale now (smaller projects
that is). And, going against my own intution here, I will site one
more example (actually, the same example I posted earlier but from
the user perspective).

You have a query for bringing up lists. The query uses parameters -
so that throws views right out the window because views are static.
Eh - heard of SPs? Last time I checked, you could pass parameters to
those ...
OK. Here is my scenario, last year I took over a project that a
young guy fresh out of college started (with no documentation of
course - I guess they don't teach that in college anymore). He did a
pretty tight job (replication, DTS, web stuff, SMTP stuff - hundreds
of SPs/triggers/UDFs... huge system), but started getting tangled up
a ways down into the project and bailed on the company (I think he
got a better opportunity and just left em hanging - didn't want to
deal with the mess he was getting into). I was following his lead
(shame on me) at first. He had to pull these lists that compared
lists agains other lists, so he was generating one list, writing it
to a table and then generating another list - writing that to a table
and comparing the 2 lists. You get multiple users needing to do this
simultaneously, he had them writing to the same tables and was
separating their pulls by the user's userID. The process used
several SPs and udfs. It was a few hundred lines of sql code. Well,
it got the job done. It just wasn't very efficient. I simplified
the process down to 5 - 10 lines of sql code (per list) which I write
inLine in a VB2005 app and doesn't write anything to a table. I
could write an SP and return a resultset to the ADP. But there are
already hundreds of SPs in this particular Sql Server DB. So I write
the 5 - 10 lines of code inline in a .Net app and read the results
into a datagridview. This is way simpler than having to wade through
hundreds of SPs.
I'm sorry, what is the point of this?

You are comparing a badly designed database, a badly designed attempt
at server side processing, which coincidently has an ADP client,
against client side, dynamic/inline SQL approach in a .Net developed
client.

This doesn't even qualify for the description of comparing apples and
oranges, and of course has nothing to do with ADP vs .Net.

BTW - from what I read, Access developers are often ridiculed for our
usage of inline/dynamic SQL by other type of developers - but it seems
you are advocating those same methods - spreading out inline SQL all
around the classes - as sound practice in OO/n-tier/.Net.
I write a lot of sql inline because it is just way simpler - and with
the advent of data Adapters in .Net the dataAdapter takes care of a
lot of stuff like concurrency issues, and apostrophe's (that is very
cool)... Plus, the datagridview is like a table view in Access -
where you can stretch a table view in Access and see additional
colums where if that table were in an Access form you have to scroll
the form. In Net you can stretch the form and the datagridview
stretches with the form (if you set the dock property of the
datagridview to Fill). So why clutter your Sql Server DB with a
bunch of redundant tables and SPs (udfs)...
The only people who clutter the SQL server (or any database) with a
bunch of redundant tables, are people who don't know what they are
talking about.

As for cluttering the database with SPs - gee - I'll just leave it
with what seems to have become my standard reply to your nonsense -
of course it has nothing to do with which client tool one chooses
(ADP vs .Net) ...

[snipped more nonsense]

--
Roy-Vidar
Sep 11 '06 #25

P: n/a
Rich P wrote:
The think you should think about is that .Net did not come about just
because Microsoft decided they needed to invent something new. It came
about because of Necessity.
That's right, hon. Paying the rent, putting food on the table, and having
clothes on their backs fall into the category of *necessity.* .Net came
about because Microsoft needs revenues to pay for necessities (and keep stock
holders happy), and customers who previously purchased existing products
aren't going to increase Microsoft's revenues unless there's a new, improved
version of the product they already own that fills their needs better or a
new product that fills their needs. New product = New $$$.
I reduced a giant ADP application with hundreds of tables,
procedures, UDFs and tens of thousands of lines of code to just a few
dozen tables, procedures, udfs and only a few thousand lines of code.
You and I both know that there are only three reasons for reducing a database
system so dramatically:

1) The database designer wasn't yet competent in relational database design
and ignored the normalization rules when he built it; or
2) The business rules have changed drastically, and much of the previously
stored data is no longer needed in the new database system; or
3) The data processing is done with temporary data sets that don't need to
be stored in temporary tables, which the previous database system employed.

And we both know that reason #1 is way, way more prevalent than the other two
reasons for reducing tables, stored procedures, and code in a revised
database system. When the revised database system becomes like 10% of the
original in number of tables, views and stored procedures, it's because a
competent database designer came along and designed it *the right way,* not
because 90% of the data processing is now done with temporary data sets in
the .Net code using the client's RAM.
I was
able to encapsulate the majority of the processing within the .Net
application.
If you're replacing tables on the database server with data sets in memory,
then the larger the data set, and the more data sets in memory, and the
slower the network, the slower the data processing is going to be. When the
client computer runs out of RAM, virtual memory is used, and that's usually a
whole lot slower than a database engine's disk I/O operations, not to mention
that pulling data across the network from the database server before doing
the data processing on the client can decrease speed, too.

Suddenly sounds like the complaints about Access when it pulls high volumes
of data across the network: it's S-O-O-O S-L-O-O-O-W!
As for my example, it is just an example. The workaround in Access
would be to add a column to the table for each user. But what if you
have 2000 users?
Perish the thought, hon! An experienced database designer would never
entertain such an idea, because he knows the rules for normalization. If the
users need to be identified in the table, then one column would be added for
the user ID, and one row would be added for each unique user, whose User ID
is stored in that column. That would be 2,000 records, not 2,000 columns.
My example is normalized.
Hon, I hate to break the news to you, but no, it isn't. If it were
normalized, it would consist of two tables like the following:

1) tblUserContributions
UserID
AmtType (Never use reserved words like "Type," or you'll be fixing bugs)
Amount

2) tblUserTargets
UserID
Amount

Your example creates a column in the table with a calculated value
("Matching"), which shouldn't be stored in the table, especially since it
doesn't depend just on the primary key, but also depends on values found
elsewhere: in other records.

Your example also uses AmtType 'a' as if it were the same attribute as the
other AmtTypes of 'b' and 'c,' when AmtType 'a' is *always* going to be the
total, or target, or goal that the other AmtTypes need to add up to. Even
though they both have a user ID and an amount assigned, these are apples and
oranges, and don't belong in the same normalized table.

Your example also assigns the "Matching" value per record, as opposed to per
user, making these repeats redundant data, even if it's in a view, not a
table, which is still inefficient and can compromise data integrity.

As you realize by now, your example wasn't an adequate example of what you
were trying to describe, nor was it a fair example of what you're capable of.
I'm sure you can provide a better example of what your skills are capable of
when you have the time later.
I am sure that when Access first came out the Dbase people were
stressing because they were going to have learn VBA to use Access.
Learning a new programming language isn't that hard when you already know
some others. And VBA isn't hard, since it's designed for new users to get a
new Office application working quickly without a lot of know-how about
computers or programming. Developers stress far more about what work needs
to be redone because the present solution is going to be incompatible with
the new system. And they stress about impossible deadlines.
But the benefit is that
Net can produce applications that can handle today's volume of data way
more quickly than Access.
The database engine should be doing the work, not the front end application.
Since both .Net applications and Access can connect to the same database
engines, are you saying that .Net applications can pull large volumes of data
across the network faster than an Access front end can, or that a .Net
application displays that data on the screen faster than an Access front end
can?
Where .Net is basically an extension of Access
(on the VB side that is).
Hon, the tail doesn't wag the dog. If .Net were an *extension of Access,*
then Access could use .Net, particularly the CLR. It can't. Instead, .Net
is an improvement/replacement on Visual Studio and COM, and it's intended to
compete with the Java software development market. Access is a totally
separate tool for building front ends to data stores (which, in the case of
Jet, can be stored in the same file for single user applications).
As for my experience, I have been doing enterprise level developing for
about 10 years now, and smaller scale developing since the mid 80s. I
started the RDBMS corporate scene with Access2 and VB4 and sql server
6.5.
Which just goes to show that even experienced software developers aren't
immune from making mistakes when they occasionally step into the shoes of a
data architect. Doing data architecture *the right way* isn't as easy as it
looks, hon, but we get better with practice.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 11 '06 #26

P: n/a
.......you haven't left your cubicle since you got out of University have
you. ;) I'll try not to post again...carry on.
"Rich P" <rp*****@aol.comwrote in message
news:45***********************@news.qwest.net...
Right. You asked about deprecation. Well, large businesses which are
migrating from mainframes to windows based systems are using OOP front
ends for their DB systsms. ADPs will not suit their needs - banking
industry, supermarkets,.... ADPs will work probably for small
businesses, but why not go for the OOP front end? So micorosft isn't
focusing their energy on Access/ADPs for that sort of thing. Thus, ADPs
are being deprecated. I was just sharing my views on why ADPs are being
deprecated - I didn't even mention all of the issues and errors that I
have had to deal with on ADPs and why I stepped it up to .Net. You are
own on how you want to go.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Sep 11 '06 #27

This discussion thread is closed

Replies have been disabled for this discussion.