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

Disadvantage of Access as front-end

P: n/a
Hello,

We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick
Nov 13 '05 #1
Share this Question
Share on Google+
49 Replies


P: n/a
I'm somthing of an Access advocate, and I think Access can do what you want,
but with that many forms, queries, etc., you might be better off with a small
team of really good developers using something like Java, C#, Smalltalk,
Delphi, or something along those linse.

Basically, with that many objects, even though it'll take longer to design
your prototype objects, it'll be easier to use inheritance and automatic cose
generation to reduce the cost and increase the consistency, putting out the
number of objects you have in mind.

A note on reports. I'm a bit biased, but I don't know if anything does
reports as well as Access does. Since Access exposes an object model via COM,
perhaps, it would make sense to use Access as the UI, but generate the Access
front-end database and all objects using automation from some other language
such as C#, Python, or Ruby. Thus, your "source" code is not in Access even
though the run-time product is an Access front-end. C# is nice in this case,
because it makes COM integration pretty easy, but Python and Ruby make it
easier to do the text parsing, templates, etc., to generate the VBA cod the
Access app will use at run-time.

Another nice thing about the code generation approach is that you can modify
the generator code later to use the same templates to generate Web interfaces
in ASP.NET, Struts, or Zope.

Have a look at "Code Generation In Action" by Jack Herrington.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Hello,

We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick


Nov 13 '05 #2

P: n/a
Just curious. Are you using DAO/ODBC or ADP?

Dealing with Access over network is much easier for me now - and faster -
with the use of Windows Terminal Services. With 40 users, I suggest it,
more of that if ever you are using DAO/ODBC (although it is hard to
believel you would do so with 5GB of data to share).


"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:cX*********************@news20.bellglobal.com ...
Hello,

We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Other that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick

Nov 13 '05 #3

P: n/a
Sorry for all the typos.

On Fri, 16 Jul 2004 15:32:34 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
I'm somthing of an Access advocate, and I think Access can do what you want,
but with that many forms, queries, etc., you might be better off with a small

....
Nov 13 '05 #4

P: n/a
Of course, JET won't handle 5GB of data, so I assume you just mean to have the
SQL Server on the same machine with TS, and give each user their own copy of
the Access front-end on the TS, right? Some hassles in that case would be
printing issues and exporting data to the user's local drive.

Also, that means the load on the TS affecting database performance. Perhaps,
if the TS server was a separate box connected to the SQL Server over a GB
Ethernet connection?

On Fri, 16 Jul 2004 11:55:07 -0400, "Saintor" <sa******@REMOVETHIShotmail.com>
wrote:
Just curious. Are you using DAO/ODBC or ADP?

Dealing with Access over network is much easier for me now - and faster -
with the use of Windows Terminal Services. With 40 users, I suggest it,
more of that if ever you are using DAO/ODBC (although it is hard to
believel you would do so with 5GB of data to share).


"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:cX*********************@news20.bellglobal.co m...
Hello,

We are in the process of examining our current main application. We have

to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around

200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end?

Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick


Nov 13 '05 #5

P: n/a
I assume that 5GB is with all the 200 tables.

Where I worked before in 2002, I bought 25 licenses of Win2K TS (some of
them were external sites - warehouses & sales office - over internet) and it
was superb. Seamless. Hassles were a minimum with everyone having his
profile of course. I was using DAO, and my applications were MUCH quicker,
data having not to go on the network. No more complaint about slow
application.

I feel that TS is the band-aid that Access needed to compete (up to a low
limitation) with the ASP/NET world. It has a price, though, at 70-80$ per
seat and a server to boot. 'Still well worths it.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:i9********************************@4ax.com...
Of course, JET won't handle 5GB of data, so I assume you just mean to have the SQL Server on the same machine with TS, and give each user their own copy of the Access front-end on the TS, right? Some hassles in that case would be
printing issues and exporting data to the user's local drive.

Also, that means the load on the TS affecting database performance. Perhaps, if the TS server was a separate box connected to the SQL Server over a GB
Ethernet connection?

On Fri, 16 Jul 2004 11:55:07 -0400, "Saintor" <sa******@REMOVETHIShotmail.com> wrote:
Just curious. Are you using DAO/ODBC or ADP?

Dealing with Access over network is much easier for me now - and faster -
with the use of Windows Terminal Services. With 40 users, I suggest it,
more of that if ever you are using DAO/ODBC (although it is hard to
believel you would do so with 5GB of data to share).


"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:cX*********************@news20.bellglobal.co m...
Hello,

We are in the process of examining our current main application. We have
to
do some major changes and, in the process, are questionning/validating
the use of MS Access as front-end. The application is relatively big: around200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of

data (SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end?

Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick

Nov 13 '05 #6

P: n/a
Steve,

Can you expand on this? Have you done this? Has anyone else? Does a code
generation package already exist that does this? It sounds like an
interesting concept. I'll check out the reference to Herrington.

Thanks
Ruben

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:aq********************************@4ax.com...
I'm somthing of an Access advocate, and I think Access can do what you want, but with that many forms, queries, etc., you might be better off with a small team of really good developers using something like Java, C#, Smalltalk,
Delphi, or something along those linse.

Basically, with that many objects, even though it'll take longer to design
your prototype objects, it'll be easier to use inheritance and automatic cose generation to reduce the cost and increase the consistency, putting out the number of objects you have in mind.

A note on reports. I'm a bit biased, but I don't know if anything does
reports as well as Access does. Since Access exposes an object model via COM, perhaps, it would make sense to use Access as the UI, but generate the Access front-end database and all objects using automation from some other language such as C#, Python, or Ruby. Thus, your "source" code is not in Access even though the run-time product is an Access front-end. C# is nice in this case, because it makes COM integration pretty easy, but Python and Ruby make it
easier to do the text parsing, templates, etc., to generate the VBA cod the Access app will use at run-time.

Another nice thing about the code generation approach is that you can modify the generator code later to use the same templates to generate Web interfaces in ASP.NET, Struts, or Zope.

Have a look at "Code Generation In Action" by Jack Herrington.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Hello,

We are in the process of examining our current main application. We have todo some major changes and, in the process, are questionning/validating theuse of MS Access as front-end. The application is relatively big: around 200tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Otherthat it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick

Nov 13 '05 #7

P: n/a
Sorry, I should have added; has anyone done this specifically for Access?

Thanks
Ruben

"R Baumann" <ry**@9yahoo.com> wrote in message
news:FM********************@megapath.net...
Steve,

Can you expand on this? Have you done this? Has anyone else? Does a code generation package already exist that does this? It sounds like an
interesting concept. I'll check out the reference to Herrington.

Thanks
Ruben

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:aq********************************@4ax.com...
I'm somthing of an Access advocate, and I think Access can do what you want,
but with that many forms, queries, etc., you might be better off with a

small
team of really good developers using something like Java, C#, Smalltalk,
Delphi, or something along those linse.

Basically, with that many objects, even though it'll take longer to design
your prototype objects, it'll be easier to use inheritance and automatic

cose
generation to reduce the cost and increase the consistency, putting out

the
number of objects you have in mind.

A note on reports. I'm a bit biased, but I don't know if anything does
reports as well as Access does. Since Access exposes an object model via COM,
perhaps, it would make sense to use Access as the UI, but generate the Access
front-end database and all objects using automation from some other

language
such as C#, Python, or Ruby. Thus, your "source" code is not in Access

even
though the run-time product is an Access front-end. C# is nice in this

case,
because it makes COM integration pretty easy, but Python and Ruby make

it easier to do the text parsing, templates, etc., to generate the VBA cod

the
Access app will use at run-time.

Another nice thing about the code generation approach is that you can

modify
the generator code later to use the same templates to generate Web

interfaces
in ASP.NET, Struts, or Zope.

Have a look at "Code Generation In Action" by Jack Herrington.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com> wrote:
Hello,

We are in the process of examining our current main application. We have todo some major changes and, in the process, are questionning/validating theuse of MS Access as front-end. The application is relatively big:
around
200tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Otherthat it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur

databaseapplication of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick


Nov 13 '05 #8

P: n/a
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in
news:cr****************@wagner.videotron.net:
I feel that TS is the band-aid that Access needed to compete (up
to a low limitation) with the ASP/NET world. It has a price,
though, at 70-80$ per seat and a server to boot. 'Still well
worths it.


Back when I evaluated it for a client (1998), it was $900/seat for a
5-license pack, not counting installation (i.e., re-installing the
OS on the NT server) or infrastructure (at the time, it was cheaper
and easier to provide dialup access (we already had a 4-modem dialin
server that could handle up to 8) than it was to do it over the
Internet, with the firewall administration costs plus the cost of a
full-time connection; back then, the client was paying several
hundred per month for 64KB!).

And, of course, that was before all the Citrix technology had been
licensed by MS, so you had to buy the Metaframe extensions as well
to get something usable over dialup.

Under the current cost basis for TS, I wouldn't have been doing
replication, either.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9

P: n/a
RE/
I'm wondering what are the disadvantages of using Access as front-end?


Access is all I do, so to me Access is my hammer and everything in the world
looks like a nail.

Having said that, I can think of a few reasons not to use same:

- New releases of Office can put your app out of business if installed without
considering that your app is in place.

- From the outside, MS Access apps look to me like objects are interdependent.
Weird stuff can happen to a production app. I don't know enough to discuss it
intelligently, but I do know from experience that you can change screen "A",
never touch anything else, and screen "B" can get weird on you. It's not a
common occurrance, but it does happen.

- MS Access does not lend itself to automated testing tools. Something about a
screen only revealing the ActiveControl as a real control and everything else
being a bitmap....dunno the specifics...just that the few tools I've tried have
required me to program them at the window-coordinate level which doesn't work
for real testing.

- Access doesn't lend itself to parceling out little sections of functionality
to different programmers. It can be done if somebody acts as the coordinator
and is careful...but it doesn't work with the few code management systems that
I'm familiar with.

- You wouldn't want a Wall Street Journal article to say that your corporation
was managing 10 billion dollars worth of securities with an MS Access
application....even though it would be a bum rap because you'd be just be using
Access as a front-end development tool and Oracle/Sybase/SQL Server as the back
end...but to people who don't know, it would sound bad.

- It's harder to hide source code. Unless you choose to burden the user with
MS Access's security, a user can alter your app on-the-fly.
On the other hand, is there another game in town that lets you get an app up and
running in 1/3 to 1/5th the manhours it would take using VB6?

--
PeteCresswell
Nov 13 '05 #10

P: n/a
On Fri, 16 Jul 2004 23:50:50 GMT, "(Pete Cresswell)" <x@y.z> wrote:
RE/
I'm wondering what are the disadvantages of using Access as front-end?
Access is all I do, so to me Access is my hammer and everything in the world
looks like a nail.

Having said that, I can think of a few reasons not to use same:

- New releases of Office can put your app out of business if installed without
considering that your app is in place.

- From the outside, MS Access apps look to me like objects are interdependent.
Weird stuff can happen to a production app. I don't know enough to discuss it
intelligently, but I do know from experience that you can change screen "A",
never touch anything else, and screen "B" can get weird on you. It's not a
common occurrance, but it does happen.

- MS Access does not lend itself to automated testing tools. Something about a
screen only revealing the ActiveControl as a real control and everything else
being a bitmap....dunno the specifics...just that the few tools I've tried have
required me to program them at the window-coordinate level which doesn't work
for real testing.


What you say is true, however I just saw a demonstration at PAUG in which
someone had managed to have great success doing automated UI testing with
Access. He did not try to access the UI physically, but instead, used the
object model. he figured out some neat tricks like the fact that the even
handlers can be made public, so the test harness can call them, and you can
force control events to fire by using the Text property to set their values,
then moving the focus away from them. he used a pre-populated copy of the
database as raw data for test input.

Message boxes were tricky, and he used SendKeys which sometimes worked. I
figured out a trick for some of those, though. You can define your own public
function called MsgBox, and it will override the build-in method for same
(only for VB calls to MsgBox, of course). To allow your test harness to
display real message boxes with that in place, call VBA.MsgBox.
- Access doesn't lend itself to parceling out little sections of functionality
to different programmers. It can be done if somebody acts as the coordinator
and is careful...but it doesn't work with the few code management systems that
I'm familiar with.

- You wouldn't want a Wall Street Journal article to say that your corporation
was managing 10 billion dollars worth of securities with an MS Access
application....even though it would be a bum rap because you'd be just be using
Access as a front-end development tool and Oracle/Sybase/SQL Server as the back
end...but to people who don't know, it would sound bad.

- It's harder to hide source code. Unless you choose to burden the user with
MS Access's security, a user can alter your app on-the-fly.
It's also harder to expose source "code" since much of what makes the
application behave the way it does is not stored in the code modules. For
instance, instead of something like an Import statement, references to
external libraries are entered into a References collection that saves with
the database. Also, method calls can be buried in event handlers for form or
menu events, and sometimes doing so is less messy than the code it takes to
not hide them there.
On the other hand, is there another game in town that lets you get an app up and
running in 1/3 to 1/5th the manhours it would take using VB6?


Well, that depends on how many forms, how much duplication, etc., and of
course, how and whether automated code generation is used. I don't think I;d
ever start a project in VB6 again (VBA - yes, because it's embedded in the
Office apps) now that the .NET language and tools are so nice.
Nov 13 '05 #11

P: n/a
I've done bits of it - enough to know it can work well. Actually, it's
probably easier to generate an Access app than to generate an app in most
other platforms because you have access to an object model that makes it
simple to just open a new form in design mode, add a control to the form's
controls collection, set its size, position, etc., save the form with name
"xyz", etc. You don't have to build your own library first to help you do
those sorts of things, before you can start cranking out forms.

On Fri, 16 Jul 2004 10:56:05 -0700, "R Baumann" <ry**@9yahoo.com> wrote:
Sorry, I should have added; has anyone done this specifically for Access?

Thanks
Ruben

"R Baumann" <ry**@9yahoo.com> wrote in message
news:FM********************@megapath.net...
Steve,

Can you expand on this? Have you done this? Has anyone else? Does a

code
generation package already exist that does this? It sounds like an
interesting concept. I'll check out the reference to Herrington.

Thanks
Ruben

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:aq********************************@4ax.com...
> I'm somthing of an Access advocate, and I think Access can do what you

want,
> but with that many forms, queries, etc., you might be better off with a

small
> team of really good developers using something like Java, C#, Smalltalk,
> Delphi, or something along those linse.
>
> Basically, with that many objects, even though it'll take longer todesign > your prototype objects, it'll be easier to use inheritance and automatic

cose
> generation to reduce the cost and increase the consistency, putting out

the
> number of objects you have in mind.
>
> A note on reports. I'm a bit biased, but I don't know if anything does
> reports as well as Access does. Since Access exposes an object modelvia
COM,
> perhaps, it would make sense to use Access as the UI, but generate the

Access
> front-end database and all objects using automation from some other

language
> such as C#, Python, or Ruby. Thus, your "source" code is not in Access

even
> though the run-time product is an Access front-end. C# is nice in this

case,
> because it makes COM integration pretty easy, but Python and Ruby make

it > easier to do the text parsing, templates, etc., to generate the VBA cod

the
> Access app will use at run-time.
>
> Another nice thing about the code generation approach is that you can

modify
> the generator code later to use the same templates to generate Web

interfaces
> in ASP.NET, Struts, or Zope.
>
> Have a look at "Code Generation In Action" by Jack Herrington.
>
> On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon"<no****@nowhere.com> > wrote:
>
> >Hello,
> >
> >We are in the process of examining our current main application. Wehave
to
> >do some major changes and, in the process, are questionning/validating

the
> >use of MS Access as front-end. The application is relatively big:

around
200
> >tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of

data
> >(SQL Server 2000), 40 users.
> >
> >I'm wondering what are the disadvantages of using Access as front-end?

Other
> >that it's not accessible with a browser. Is there any execution speed
> >question involved? Do we have any "realistic" alternatives regarding
> >development speed? What others are using to build client-serveur

database > >application of that size, not sold to anybody, in permanent evolution?
> >
> >It's almost philosophy! All feedback are welcome.
> >
> >Yannick
> >
>



Nov 13 '05 #12

P: n/a
RE/
He did not try to access the UI physically, but instead, used the
object model. he figured out some neat tricks like the fact that the even
handlers can be made public, so the test harness can call them, and you can
force control events to fire by using the Text property to set their values,
then moving the focus away from them. he used a pre-populated copy of the
database as raw data for test input.


I did this several years ago - although probably not at as sophicated a level as
he did. It did work. The problem is that this isn't something you can turn
over to a QA department to maintain. They like to do their own tests with their
own tools.
--
PeteCresswell
Nov 13 '05 #13

P: n/a
RE/
I don't think I;d
ever start a project in VB6 again (VBA - yes, because it's embedded in the
Office apps) now that the .NET language and tools are so nice.


Do you have any feeling yet for the manhours needed for an app in MS Access vs
VB.Net/Crystal?

One of the things I'd like to do once my current gig dries up is to develop a
RAD prototype in .NET and then try to shoehorn one or two of my existing Access
apps into it and see how the manhours stack up.
--
PeteCresswell
Nov 13 '05 #14

P: n/a
On Sat, 17 Jul 2004 14:42:31 GMT, "(Pete Cresswell)" <x@y.z> wrote:
RE/
I don't think I;d
ever start a project in VB6 again (VBA - yes, because it's embedded in the
Office apps) now that the .NET language and tools are so nice.


Do you have any feeling yet for the manhours needed for an app in MS Access vs
VB.Net/Crystal?

One of the things I'd like to do once my current gig dries up is to develop a
RAD prototype in .NET and then try to shoehorn one or two of my existing Access
apps into it and see how the manhours stack up.


It would take some experimenting, but given Access' lack of facilities for
factoring out duplication of design content, I'd be willing to assert that the
larger the project, the more time will be saved by using something else with
better facilities in that area.

That's where my thinking aims toward automation, though. It might be easier
on many projects to create report templates in Access, then make code (in VBA,
..NET, or anything with OLE capability) to automate generation of report
variations than to use some complicated monstrosity like Crystal. The same
would be true for forms, but to a lesser degree. Continuous forms and
flexible Combo boxes are nice, but other than that, other GUI models such as
Windows Forms / VS.NET are doing quite well, and have better built-in
facitiies for run-time customization and factoring out duplicated design
attributes.

Nov 13 '05 #15

P: n/a
On Fri, 16 Jul 2004 23:50:50 GMT, "(Pete Cresswell)" <x@y.z> wrote:

<SNIP>
- You wouldn't want a Wall Street Journal article to say that your corporation
was managing 10 billion dollars worth of securities with an MS Access
application....even though it would be a bum rap because you'd be just be using
Access as a front-end development tool and Oracle/Sybase/SQL Server as the back
end...but to people who don't know, it would sound bad.


Access is used as a front-end for a portfolio/accounting system called
AdvisorWare for large financial firms, and administrators. One of the
top hedge funds in this country uses AdvisorWare (running over 5
billion in investments), a long with many more (over 75 per the
vendor).

And, at over $300,000 for the software, it is not for small companies.

Steven
Nov 13 '05 #16

P: n/a
On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon"
<no****@nowhere.com> wrote:
Hello,

We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

Since your using SQL, the back-end is not an issue.
I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Performance really depends on the nature of your software. If your
front-end is performing a lot of calculations/financial analysis,
Access VBA may not be the appropriate language. But most database
applications are not that calculation intensive.

Another issue is how easy is it to maintain the current Access code
base. What has been the quality of the previous Access developers?

Given the number of reports and forms, going to another environment
(e.g. VB/C#.Net) is going to take a long-time, without adding any
functionallity at all.

Another option to consider is, depending on the nature of the major
changes, provide some access to the SQL Server using other
technologiest (e.g. provide for some reports over the Web).

Steven



Yannick


Nov 13 '05 #17

P: n/a
Thanks to everybody who took time to reply to my post. It's most
appreciated.

Reading your replies, and particularly this one, I realize I'll have to do a
lot of homework to evaluate efficiently what our options are!

When Steve says: "with a small team of really good developers". A big part
of the problem is here. We are located in a relatively northern, small
Canadian town and good developers are not running in the streets...

Again, thanks all to take time to share your knowledge. I'll later read more
attentively all your post.

Yannick

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:aq********************************@4ax.com...
I'm somthing of an Access advocate, and I think Access can do what you want, but with that many forms, queries, etc., you might be better off with a small team of really good developers using something like Java, C#, Smalltalk,
Delphi, or something along those linse.

Basically, with that many objects, even though it'll take longer to design
your prototype objects, it'll be easier to use inheritance and automatic cose generation to reduce the cost and increase the consistency, putting out the number of objects you have in mind.

A note on reports. I'm a bit biased, but I don't know if anything does
reports as well as Access does. Since Access exposes an object model via COM, perhaps, it would make sense to use Access as the UI, but generate the Access front-end database and all objects using automation from some other language such as C#, Python, or Ruby. Thus, your "source" code is not in Access even though the run-time product is an Access front-end. C# is nice in this case, because it makes COM integration pretty easy, but Python and Ruby make it
easier to do the text parsing, templates, etc., to generate the VBA cod the Access app will use at run-time.

Another nice thing about the code generation approach is that you can modify the generator code later to use the same templates to generate Web interfaces in ASP.NET, Struts, or Zope.

Have a look at "Code Generation In Action" by Jack Herrington.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Hello,

We are in the process of examining our current main application. We have todo some major changes and, in the process, are questionning/validating theuse of MS Access as front-end. The application is relatively big: around 200tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Otherthat it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick

Nov 13 '05 #18

P: n/a
On Tue, 20 Jul 2004 09:52:34 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Thanks to everybody who took time to reply to my post. It's most
appreciated.

Reading your replies, and particularly this one, I realize I'll have to do a
lot of homework to evaluate efficiently what our options are!

When Steve says: "with a small team of really good developers". A big part
of the problem is here. We are located in a relatively northern, small
Canadian town and good developers are not running in the streets...


Well, you might be surprised. By small team, I'm thinking 2 to 3 people, and
really good, well, it's just better to get the best you can because you don't
save money in the long run going with people who don't know databases very
well, don't know how to engineer software well, or don't know how to stay on
track and work in an organized fashion. If you can't get "really good", you
still might do well enough.

Nov 13 '05 #19

P: n/a
"Steve Jorgensen" wrote
I'm somthing of an Access advocate, and
I think Access can do what you want, but
with that many forms, queries, etc., you
might be better off with a small team of
really good developers using something like
Java, C#, Smalltalk, Delphi, or something
along those linse.


An application on which I was a subcontractor, off and on, but most of the
time, over 5 year, was an Access 2.0 (yes, the old 2.0, 16-bit Access)
client to an Informix database. It had in the neighborhood of 1000 objects,
and it wouldn't have been one whit better in any of the languages you
mention -- but implementation would have cost more, and maintenance would
have cost more.

And, at times in its life, the team was not always "really good". Of course,
during the time I worked on it, the teams were "exceptionally good". <GRIN>
But the point is, that even the mess left by some developers who weren't
strong in Access didn't keep it from being a usable and maintainable
database.

I'm not sure on what you base this recommendation, but I would certainly
(though respectfuly) dissent.

Larry Linson
Microsoft Access MVP


Nov 13 '05 #20

P: n/a
On Wed, 21 Jul 2004 03:07:08 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
"Steve Jorgensen" wrote
I'm somthing of an Access advocate, and
I think Access can do what you want, but
with that many forms, queries, etc., you
might be better off with a small team of
really good developers using something like
Java, C#, Smalltalk, Delphi, or something
along those linse.
An application on which I was a subcontractor, off and on, but most of the
time, over 5 year, was an Access 2.0 (yes, the old 2.0, 16-bit Access)
client to an Informix database. It had in the neighborhood of 1000 objects,
and it wouldn't have been one whit better in any of the languages you
mention -- but implementation would have cost more, and maintenance would
have cost more.


Really? I've found that the benefits Access offers tend to diminish with the
size of the app because Access is lacking in features for removing
duplication, finding errors at build-time, etc., that other platforms such as
Java, .NET, and Delphi do have. Consequently, the problem of fixing a bug in
Access that's replicated 20 places in ways that are not easily
search/replaceable comes up a lot, and it is frequently impossible to be sure
a change has no nasty side effects prior to releasing it into production.
I've actually spent a lot of time trying to engineer around such things in the
past coupe of years with only limited success.

I'm not sying these problems are totally insurmountable, but you pretty much
have to do some kind of code generation to get around it. Is that code
generation worth doing for the sake of the cool things Access does do very
well? I think it is, but that's not how most programmers would attack the
problem.
And, at times in its life, the team was not always "really good". Of course,
during the time I worked on it, the teams were "exceptionally good". <GRIN>
But the point is, that even the mess left by some developers who weren't
strong in Access didn't keep it from being a usable and maintainable
database.
I have certainly seen other projects where this was not the case, and I'm
embarrassed to say that some of them were my doing, when I was, in fact,
strong in Access, but had not developed the emphasis on simplicity and
neatness I now strive for.
I'm not sure on what you base this recommendation, but I would certainly
(though respectfuly) dissent.


I didn't say Access wasn't suitable for the job, just that for a job that
size, Access has some deficiencies that can overcome its strengths. For
instance, when error handling requirements cause you to unbind all your
editable forms, how much benefit are you getting from Access' facility with
bound forms? An, then datasheet views and continuous forms are uneditable, so
you end up choosing whether to force the user to do another click to open
another form or use something like a listview control, etc. Now, is this
better than starting with VB? Well, yes because of the report, but you see
where I'm going with this.

Recently, I got a contract with a company that wanted to use Access as a
front-end for the data driving their Web site. The data management UI was not
exposed to the public, so they thought they would go with a "Chevy" UI rather
than a "Cadillac" UI. Nevertheless, it wasn't long before they absolutely
required friendlier error messages for errors that occurred while editing data
through bound forms.

I had already used a zillion continuous forms, so I was in a pickle. It
turned out to be sort of possible to catch the errors by using event handlers
to cancel the updates, and do them in VB code using the form's RecordsetClone,
but then there were quirks with that that caused forms to behave in strange
ways, redrawing things in the wrong places, etc. I ended up with several
layers deep of work-arounds to that to get a semi-stable system, but they had
their freindly error messages - whew!

Don't get me wrong, I'm still an Access fan, and most of my development is in
Access, but I won't say it has no warts, and I try to be aware of what jobs
Access is more likely to have trouble with.

Nov 13 '05 #21

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8c********************************@4ax.com:
Recently, I got a contract with a company that wanted to use
Access as a front-end for the data driving their Web site. The
data management UI was not exposed to the public, so they thought
they would go with a "Chevy" UI rather than a "Cadillac" UI.
Nevertheless, it wasn't long before they absolutely required
friendlier error messages for errors that occurred while editing
data through bound forms.


As a general rule, I don't allow continuous forms to be editable.

The easiest way to deal with this is to make the continuous form
non-editable, and then have selecting a record in the continuous
form load a record in a second subform (bound or unbound, depending
on whether concurrency is a problem).

Basically, the only editable subforms I use these days are for
things like invoice details, where they are never viewed/edited
except as children of their parent.

I've never seen much in the way of problems, even then. Most of my
problems with continuous forms have to do with unsuitability of the
UI (e.g., when you need combo box rowsources to be changed based on
choices in other fields).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #22

P: n/a
On Wed, 21 Jul 2004 16:46:01 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8c********************************@4ax.com :
Recently, I got a contract with a company that wanted to use
Access as a front-end for the data driving their Web site. The
data management UI was not exposed to the public, so they thought
they would go with a "Chevy" UI rather than a "Cadillac" UI.
Nevertheless, it wasn't long before they absolutely required
friendlier error messages for errors that occurred while editing
data through bound forms.
As a general rule, I don't allow continuous forms to be editable.

The easiest way to deal with this is to make the continuous form
non-editable, and then have selecting a record in the continuous
form load a record in a second subform (bound or unbound, depending
on whether concurrency is a problem).

Basically, the only editable subforms I use these days are for
things like invoice details, where they are never viewed/edited
except as children of their parent.


I actually had quite a few of these cases. To keep related data together and
easily accessible in a highly normalized schema, I actually has to have main
forms with tabs, each tab having some fields and one or more subforms, and
some of the subforms acting as masters to other subforms on the same page
(e.g. a hotel main page with a tab for rates, a subform for room categories, a
subform for seasons, and a subform showing the category-season m-m records
with rates.)
I've never seen much in the way of problems, even then. Most of my
problems with continuous forms have to do with unsuitability of the
UI (e.g., when you need combo box rowsources to be changed based on
choices in other fields).


Here, the problem was that the back-end was on a server, and the
server-generated error messages for things like trying to delete a record with
non-cascade-delete relations were not human-friendly. The client expected the
error message to actually be helpful - imagine that. Thus, the saga began.
If I'd had to create edit forms for each continuous subform, the user would
have a very discontinuous experience trying to edit this data, and I would
have had to build vastly more unbound forms.

So, does that actually end up being less work or less programming than using
listviews? After all, I can make an unbound listview editable, and if I'm
doing that, what was the reason for doing the project in Access? Sure, there
are lots of layout things and types of controls I can't put in a listview, but
I'm on balance, the listview method would actually be the cleaner solution in
this situation.

Nov 13 '05 #23

P: n/a
"Steve Jorgensen" wrote in
Really? I've found that the benefits Access
offers tend to diminish with the size of the app
because Access is lacking in features for removing
duplication,
What kind of "duplication"?
finding errors at build-time, etc.,
As there was no "build-time" involved with Access, I don't know what you
mean.

The only third-party tools we used for most of the life of that application
were Speed Ferret... that was outstanding for "finding" things whether
buried in Properties or in code.

Toward the end of the applications' life, they got FMS' Total Access suite
for Access 2.0, which we tried (without much success) to use in Y2K analysis
and remediation. It was, unfortunately, near the end of the life of the TA
Suite for Access 2.0 and, though, FMS still sold it, they no longer fixed
bugs.

On the other hand, a colleague was able to get around the problems with that
third-party software with some judiciously done queries.
Consequently, the problem of fixing a bug in
Access that's replicated 20 places in ways
that are not easily search/replaceable comes
up a lot, and it is frequently impossible to be sure
a change has no nasty side effects prior to
releasing it into production.
As I said, Speed Ferret was our tool of choice for search/replace. And I am
not sure what you've encountered, but I don't recall anything that would fit
this description.

I've been in the computer business since 1958, and am not aware of any
software that gives any real comfort that "there are no nasty side effects".
That's just a matter of careful test specs, careful testing, and regression
testing. I have worked with software that had some automated tools available
for regression testing, but even so, it was not nearly as
effective/efficient for developing and deploying apps as I found Access to
be.

I'm not sying these problems are totally
insurmountable, but you pretty much
have to do some kind of code generation
to get around it. Is that code generation
worth doing for the sake of the cool things
Access does do very well? I think it is, but
that's not how most programmers would
attack the problem.
I am at a loss, Steve, to understand what you are saying here. I've often
done some application-specific design-time code... on my last project, I
automated applying consistent error handling/logging (about 500 procedures)
and replacing the wizard-generated error handling (just under 100
procedures)... but, I am not at all sure that has anything to do with what
you are talking about.
. . . For instance, when error handling
requirements cause you to unbind all your
editable forms, how much benefit are you
getting from Access' facility with bound forms?
Well, if you use unbound forms, then obviously you'd be getting no benefit
from Access' facility with bound forms. I'm not sure what kind of error
handling requirements would cause you to unbind all your forms... I haven't
encountered any in using Access since 1993.
An, then datasheet views and continuous forms
are uneditable, so you end up choosing whether
to force the user to do another click to open
another form or use something like a listview
control, etc. Now, is this better than starting with VB? Well,
yes because of the report, but you see
where I'm going with this.
No, I don't see at all. As I have never been faced with any requirements,
error-handling or other, that would cause me to feel compelled to use
unbound forms, I find it difficult to follow.

I use no (none, not any, zip, zilch) datasheet views in developed
applications, so that would not be a concern to me. And, since I would be
unlikely to do an Access application with all unbound forms, what you
described would not apply. Most of the applications that I have worked on,
whether mine or inherited, had NO unbound forms other than switchboards and
selection forms. That one that was "tainted" by weak developers had a
couple, both of which I had to go maintain, not because there was a change,
but just because the original developer left some thing/things out when
re-implementing Access' features.
. . . Nevertheless, it wasn't long before they
absolutely required friendlier error messages
for errors that occurred while editing data
through bound forms.
Sorry, Steve... I still don't understand what kind of error handling you
mean. I typically (unless constrained otherwise by the customer) include my
own error handling... rather standard... with my own error messages, and
sometimes logging. I haven't had any complaints about my error messages
being "unfriendly". Are you omitting OnError GoTo to rely on the default
error handling?
. . . Access, but I won't say it has no warts,
And, neither would I, nor any experienced Access-hand here, I'm sure. But,
for single-user, modest-audience multiuser, and even rather large audience
client-server database applications, I haven't found anything that comes
close.

It's not for the humungous enterprise, intergalactic application, and that
is so clear that it really is not a subject for discussion.
and I try to be aware of what jobs
Access is more likely to have trouble with.


In my experience and observation, that would be enterprise applications,
web-based applications, and non-database applications, and a teeny-tiny
subset of database stuff that included heavy-duty calculations or a customer
who insisted on particular interface features (none of my customers ever
have). And, I'd never suggest, nor begin development with Access for any of
those.

For the kind of "normal business database applications" that I've been doing
since 1993, though, even large ones, there's nothing that comes close.

Larry Linson

Nov 13 '05 #24

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2g********************************@4ax.com:
the problem was that the back-end was on a server, and the
server-generated error messages for things like trying to delete a
record with non-cascade-delete relations were not human-friendly.


I never use or allow the builtin deletion routines. I always have a
command button for deletion that allows me to control the order of
events, so in the case of your deletion of a record selected in a
continuous form, it would check for child records, produce a
suitable deletion confirmation message, and, if the user answered
the confirmation in the affirmative, then, in a transaction, delete
the child record(s), then the parent records, and then requery the
subform and reposition the current position (if appropriate).

Isn't that the standard way to do this?

Why, then, would you be running into such problems?

Access's default behaviors work just great in the scenarios modelled
in the Solutions database. But I move beyond standard behavior
almost always in certain kind of very basic tasks, such as deletion.

Actually, the big fear for me about using default form behavior is
that the confirm message may never appear, so I simply set
AllowDeletions OFF then write my own deletion code, as described
above. There are lots of places in Access where I bypass built-in
functionality for a more suitable method, yet, it's still not even
close to being as hard as the alternatives.

Seems to me that you've gotten yourself into this position by
attempting to use too much of Access's built-in functionality. At
least, that's the only way I can see that the child deletions issue
could be involved, since if you're coding the deletion yourself
(instead of using the builtin UI with AllowDeletions turned ON), you
would either have anticipated the child record deletion problem, or
could very easily put it into your code once you discovered it was a
problem.

Or, perhaps you rejected a DELETE command button for some reason?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #25

P: n/a
On Wed, 21 Jul 2004 23:20:52 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2g********************************@4ax.com :
the problem was that the back-end was on a server, and the
server-generated error messages for things like trying to delete a
record with non-cascade-delete relations were not human-friendly.
I never use or allow the builtin deletion routines. I always have a
command button for deletion that allows me to control the order of
events, so in the case of your deletion of a record selected in a
continuous form, it would check for child records, produce a
suitable deletion confirmation message, and, if the user answered
the confirmation in the affirmative, then, in a transaction, delete
the child record(s), then the parent records, and then requery the
subform and reposition the current position (if appropriate).

Isn't that the standard way to do this?


Well, no. It's duplication of logic between the database layer and the code
layer. What if you decide to change a relation to cascade delete, but you
forget to remove the code checking? Now, you have youre code rule thwarting
your data rule. Furthermore, the code you're talking about may have to be
called from multiple places.

Also, in the case I'm referencing, the schema was complex. The delete failure
could easily be due to an indirect relationship rule. The number of possible
permutations was high.

Actually, I did end up using a custom deletion routine, but the routine parsed
the error text to see what table was being complained about, then looked up a
friendly entity name from a table. Deletions, though, were only part of the
problem. There were also database trigger errors on insert/update, etc., and
these could be tied to tables I had bound continuous subforms connected to.
Without trapping every attempted update, and implementing it in code, there
was no way to get sufficient error information from code. Also, it turns out
that with client/server apps, Access is writing to the field objects in the
ODBC recordset before calling BeforeUpdate, so anything the database driver
complains about, the user gets the ODBC error message. I think the Form_Error
event can catch those, but it only gets the error number - the "ODBC Call
Failed" error number. Useful.

....Actually, the big fear for me about using default form behavior is
that the confirm message may never appear, so I simply set
AllowDeletions OFF then write my own deletion code, as described
above. There are lots of places in Access where I bypass built-in
functionality for a more suitable method, yet, it's still not even
close to being as hard as the alternatives.
I dunno. Some of the GUI builders out there are getting pretty decent (if you
don't count reports, of course). VS.NET is pretty nice. Also, the fact that
forms are classes that execute statements to generate controls at run-time
make it really easy to factor out duplicated elements, validation logic, etc.
it's not hard at all to shove a business logic layer in between the database
and the form, something Access forms have a much harder time with.
Seems to me that you've gotten yourself into this position by
attempting to use too much of Access's built-in functionality. At
least, that's the only way I can see that the child deletions issue
could be involved, since if you're coding the deletion yourself
(instead of using the builtin UI with AllowDeletions turned ON), you
would either have anticipated the child record deletion problem, or
could very easily put it into your code once you discovered it was a
problem.
Again, why would I want to duplicate logic like that unless I couldn't avoid
it? That's just asking for maintenance trouble down the road. Since I'm
sometimes working with schemas I don't maintain, this is a particularly thorny
issue. Now, it turns out that for about 3/4 of my clients, I maintain the
schema, and they are somewhat computer literate, having no problem
understanding the raw error messages from Access. There is simply no issue in
those cases.
Or, perhaps you rejected a DELETE command button for some reason?


I'm not averse to a Delete button or trapping the BeforeDeleteConfirm action,
but I'll still want to have thought of how the code will be maintained,
preferably some way that it willb e hard to make a change and not know this
code will be affected. That might involve, and sometimes does involve some
sort of code generation.

I actually prefer to trap BeforeDeleteConfirm over adding a Delete button just
because Access maintains a consistent metaphor and look-and-feel for me. I've
never seen the record selector idiom be a problem for users to grasp. Access
even provides a right-click shortcut for deleting, which users often figure
out faster than the Delete key.
Nov 13 '05 #26

P: n/a
Follow-up reply:

First of all, I don't want anyone to think I'm putting down Access simply
because my posts here are saying it might or might not be a good tool for your
bigger jobs, but here's a link to a document that I think explains the kind of
issues I'm talking about.

This article is specifically about trying to to Extreme Programming in VB, but
1/2 to 2/3 of what it covers is just about how it's hard to do quality,
maintainable software in VB, and what clever techniques you can use to work
around it. Of course, some of those work-arounds are actually harder or
impossible in an Access app with Access forms, others are mostly unnecessary
because of what Access can do for you. In general, I think it communicates
the idea.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Hello,

We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick


Nov 13 '05 #27

P: n/a
Steve,

Did I miss something or you just forgot to include the link?

Yannick

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:s4********************************@4ax.com...
Follow-up reply:

First of all, I don't want anyone to think I'm putting down Access simply
because my posts here are saying it might or might not be a good tool for your bigger jobs, but here's a link to a document that I think explains the kind of issues I'm talking about.

This article is specifically about trying to to Extreme Programming in VB, but 1/2 to 2/3 of what it covers is just about how it's hard to do quality,
maintainable software in VB, and what clever techniques you can use to work around it. Of course, some of those work-arounds are actually harder or
impossible in an Access app with Access forms, others are mostly unnecessary because of what Access can do for you. In general, I think it communicates the idea.

Nov 13 '05 #28

P: n/a
Oops - here's that link.

http://www.exoftware.com/documents/XPVB_000.pdf

I also meant to say that just I'm saying all this because the question was
about what the disadvantages of Acces are for an application like this. Just
because I'm pointing out what I see as the disadvantages doesn't mean it's a
bad choice. Every tool will have its disadvantages. Access also has many
benefits, and Access very well may be your best bet for this project.

On Thu, 22 Jul 2004 07:27:15 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
Follow-up reply:

First of all, I don't want anyone to think I'm putting down Access simply
because my posts here are saying it might or might not be a good tool for your
bigger jobs, but here's a link to a document that I think explains the kind of
issues I'm talking about.

This article is specifically about trying to to Extreme Programming in VB, but
1/2 to 2/3 of what it covers is just about how it's hard to do quality,
maintainable software in VB, and what clever techniques you can use to work
around it. Of course, some of those work-arounds are actually harder or
impossible in an Access app with Access forms, others are mostly unnecessary
because of what Access can do for you. In general, I think it communicates
the idea.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>
wrote:
Hello,

We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The application is relatively big: around 200
tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data
(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Other
that it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur database
application of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick


Nov 13 '05 #29

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:hp********************************@4ax.com:
On Wed, 21 Jul 2004 23:20:52 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2g********************************@4ax.co m:
the problem was that the back-end was on a server, and the
server-generated error messages for things like trying to delete
a record with non-cascade-delete relations were not
human-friendly.
I never use or allow the builtin deletion routines. I always have
a command button for deletion that allows me to control the order
of events, so in the case of your deletion of a record selected in
a continuous form, it would check for child records, produce a
suitable deletion confirmation message, and, if the user answered
the confirmation in the affirmative, then, in a transaction,
delete the child record(s), then the parent records, and then
requery the subform and reposition the current position (if
appropriate).

Isn't that the standard way to do this?


Well, no. It's duplication of logic between the database layer
and the code layer. . . .


Well, you haven't explained how you are doing the delete -- is it
with a command button and code that checks the error conditions? You
say the problem is with your back end not adequately reporting the
error conditions, right? How, then, is this a problem with *Access*,
and not with your back end db access method?
. . . What if you decide to change a relation to
cascade delete, but you forget to remove the code checking? Now,
you have youre code rule thwarting your data rule. . . .
Thwarting? It's doing in code what would be handled in the database
engine, so I don't see that it's an issue. Sure, it's duplication,
and not necessary, but it's not going to cause problems that I can
think of.

And, indeed, I can't think of a circumstance in which it's likely
that I'd be making that kind of change to a db schema that late in
the implementation of the application. If such a thing were to
change, it would be in the *other* direction, removing cascade
delete, so you'd need to add the code in.
. . . Furthermore,
the code you're talking about may have to be called from multiple
places.
If I had many places where I needed to do this, I could easily
abstract out code to do the job, and call it in one line each time a
deletion is needed.

One line of code in each place where you delete, however many lines
of code in a single sub/function/module that does the dependency
checking and confirmation, etc.

I've never actually implemented it that way, myself (when I have
cascade delete turned off, it usually means there is never a case
when the parent record is allowed to be deleted, ever), but I can't
see it as being difficult.
Also, in the case I'm referencing, the schema was complex. The
delete failure could easily be due to an indirect relationship
rule. The number of possible permutations was high.

Actually, I did end up using a custom deletion routine, but the
routine parsed the error text to see what table was being
complained about, then looked up a friendly entity name from a
table. Deletions, though, were only part of the problem. There
were also database trigger errors on insert/update, etc., and
these could be tied to tables I had bound continuous subforms
connected to. Without trapping every attempted update, and
implementing it in code, there was no way to get sufficient error
information from code. Also, it turns out that with client/server
apps, Access is writing to the field objects in the ODBC recordset
before calling BeforeUpdate, so anything the database driver
complains about, the user gets the ODBC error message. I think
the Form_Error event can catch those, but it only gets the error
number - the "ODBC Call Failed" error number. Useful.
Sounds like a problem in the ODBC driver, not with Access. Is that
your only option for access to this data?

[]
Seems to me that you've gotten yourself into this position by
attempting to use too much of Access's built-in functionality. At
least, that's the only way I can see that the child deletions
issue could be involved, since if you're coding the deletion
yourself (instead of using the builtin UI with AllowDeletions
turned ON), you would either have anticipated the child record
deletion problem, or could very easily put it into your code once
you discovered it was a problem.


Again, why would I want to duplicate logic like that unless I
couldn't avoid it? . . .


Control? If you need better confirmation that default behaviors can
give you, you have to do something different. I don't usually use
default deletion behaviors with simple deletes, even when there are
no child records.
. . . That's just asking for maintenance trouble
down the road. . . .
I don't see the issue as being as great a problem as you describe it
to be.
. . . Since I'm sometimes working with schemas I don't
maintain, this is a particularly thorny issue. Now, it turns out
that for about 3/4 of my clients, I maintain the schema, and they
are somewhat computer literate, having no problem understanding
the raw error messages from Access. There is simply no issue in
those cases.
Well, then, they are smarter than my clients.

They're smarter than me, too, in some cases -- many of Access's raw
error messages make my eyes cross (the write conflict dialog, for
instance).
Or, perhaps you rejected a DELETE command button for some reason?


I'm not averse to a Delete button or trapping the
BeforeDeleteConfirm action, . . .


I'd never depend on that because if SetWarnings is OFF, that event
doesn't fire.
. . . but I'll still want to have thought of
how the code will be maintained, preferably some way that it willb
e hard to make a change and not know this code will be affected.
That might involve, and sometimes does involve some sort of code
generation.
I don't see it, myself. Of course, my schemas follow certain rules
relating PK names to table names (tblTable has PK TableID), so I can
pass a list of tables, the first being the parent, the second being
child tables, and generate code to check for child records, generate
message text for a confirmation dialog, and then do the deletion of
the child records before the parent.

Now, with multiple levels of dependency, I'm not sure which strategy
I'd use. If it was something I encountered many times in an app, I'd
probably want to automate it, perhaps by simply walking the
relationships collection to figure out what relations there were,
which is, certainly, the most definitive way to manage it.

More likely is that I'd not need multiple levels of dependency
except in a few cases, and in those cases, I'd have to have multiple
calls to my outside routine, and some way in that to postpone the
deletions until the end (probably a wrapper function that wraps it
all in one transaction). I'd also need a way to create a single
coherent confirmation message, which would probably be the hardest
part of it all. All that sounds like it belongs in a wrapper class
module.

In any event, I still see no role for code generation, since nothing
in the code I'm talking about would need any specifics for running
in particular contexts -- it would get its needed information either
from naming conventions or from walking the relationships.
I actually prefer to trap BeforeDeleteConfirm over adding a Delete
button just because Access maintains a consistent metaphor and
look-and-feel for me. . . .
SetWarnings can be turned off, and in that case, the event doesn't
fire, so I'd never depend on it. Ever.
. . . I've never seen the record selector idiom
be a problem for users to grasp. Access even provides a
right-click shortcut for deleting, which users often figure out
faster than the Delete key.


I have AllowDeletions turned off, which means the toolbar and
right-click shortcut menus do not have those choices. Occasionally,
I've provided an alternate right-click context menu with DELETE as
one of the options. For the user, there's not much difference, and
it gives me complete control over the deletion process without a
dependency on something that I may not be able to fully control.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #30

P: n/a
That was a good reading.

I understand very well the fact that you're not suggesting me to reject
Access for the developement of our application. I asked for Access
disadvantages in general and more particularly in some conditions and I feel
like you are answering very well that question in a very informative and
unbiased manner. And I appreciate.

Yannick
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:e0********************************@4ax.com...
Oops - here's that link.

http://www.exoftware.com/documents/XPVB_000.pdf

I also meant to say that just I'm saying all this because the question was
about what the disadvantages of Acces are for an application like this. Just because I'm pointing out what I see as the disadvantages doesn't mean it's a bad choice. Every tool will have its disadvantages. Access also has many
benefits, and Access very well may be your best bet for this project.

On Thu, 22 Jul 2004 07:27:15 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
Follow-up reply:

First of all, I don't want anyone to think I'm putting down Access simply
because my posts here are saying it might or might not be a good tool for yourbigger jobs, but here's a link to a document that I think explains the kind ofissues I'm talking about.

This article is specifically about trying to to Extreme Programming in VB, but1/2 to 2/3 of what it covers is just about how it's hard to do quality,
maintainable software in VB, and what clever techniques you can use to workaround it. Of course, some of those work-arounds are actually harder or
impossible in an Access app with Access forms, others are mostly unnecessarybecause of what Access can do for you. In general, I think it communicatesthe idea.

On Fri, 16 Jul 2004 10:52:33 -0400, "Yannick Turgeon" <no****@nowhere.com>wrote:
Hello,

We are in the process of examining our current main application. We have todo some major changes and, in the process, are questionning/validating theuse of MS Access as front-end. The application is relatively big: around 200tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data(SQL Server 2000), 40 users.

I'm wondering what are the disadvantages of using Access as front-end? Otherthat it's not accessible with a browser. Is there any execution speed
question involved? Do we have any "realistic" alternatives regarding
development speed? What others are using to build client-serveur databaseapplication of that size, not sold to anybody, in permanent evolution?

It's almost philosophy! All feedback are welcome.

Yannick

Nov 13 '05 #31

P: n/a
On Thu, 22 Jul 2004 15:47:19 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:hp********************************@4ax.com :
On Wed, 21 Jul 2004 23:20:52 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2g********************************@4ax.com :

the problem was that the back-end was on a server, and the
server-generated error messages for things like trying to delete
a record with non-cascade-delete relations were not
human-friendly.

I never use or allow the builtin deletion routines. I always have
a command button for deletion that allows me to control the order
of events, so in the case of your deletion of a record selected in
a continuous form, it would check for child records, produce a
suitable deletion confirmation message, and, if the user answered
the confirmation in the affirmative, then, in a transaction,
delete the child record(s), then the parent records, and then
requery the subform and reposition the current position (if
appropriate).

Isn't that the standard way to do this?
Well, no. It's duplication of logic between the database layer
and the code layer. . . .


Well, you haven't explained how you are doing the delete -- is it
with a command button and code that checks the error conditions? You
say the problem is with your back end not adequately reporting the
error conditions, right? How, then, is this a problem with *Access*,
and not with your back end db access method?


The error info from the back-end is fine. Access simply provides no way to
read that error information when the update attempt is made via a bound form
and not from VBA code. That means updating data through a bound form is a
problem.
. . . What if you decide to change a relation to
cascade delete, but you forget to remove the code checking? Now,
you have youre code rule thwarting your data rule. . . .


Thwarting? It's doing in code what would be handled in the database
engine, so I don't see that it's an issue. Sure, it's duplication,
and not necessary, but it's not going to cause problems that I can
think of.


What I said was "What if you decide to change a relation to a cascade delete".
Now, if the code is not updated at the same time, the code is enforcing an
obsolete rule that would -not- be enforced by the database engine. The reason
this is an issue is because the front-end logic duplicates the back-end logic,
and the chances for sync problems between the 2 are why duplication should be
avoided.
And, indeed, I can't think of a circumstance in which it's likely
that I'd be making that kind of change to a db schema that late in
the implementation of the application. If such a thing were to
change, it would be in the *other* direction, removing cascade
delete, so you'd need to add the code in.
I definitely have had cases go the other way, and I've had the update come
from a database maintainer that was not me.
. . . Furthermore,
the code you're talking about may have to be called from multiple
places.


If I had many places where I needed to do this, I could easily
abstract out code to do the job, and call it in one line each time a
deletion is needed.


Good. Here's another issue I have with pre-checking relationships, though.
It's duplicating checking the database engine is about to do for you, so it's
adding load to the database access. Also, there's a small chance that the
check will succeed just before the data changes, so that the real update
won't. These a minor issues, I know. I just consider that inelegant when one
could just as well let the database engine do the checking, trap the error,
and parse out what happened. Heck, this way, the code might not even have to
know before hand which relationships could be a problem, just parse out what
they mean, and report the issues in freindly terms.

Of course, as you say, this is all no problem when just deleting because
that's trappable, and you can handle it with whatever fancy code you want.
The problem is with low-level field type rules (including things like deleting
the contents of a required field) and database rules that fire on update, not
on delete.

....I've never actually implemented it that way, myself (when I have
cascade delete turned off, it usually means there is never a case
when the parent record is allowed to be deleted, ever), but I can't
see it as being difficult.
It is a maintenance issue, though, and much more so when the back-end is not
yours, and the front-end may have to be maintained later in the life cycle,
possibly by someone else. That's where having a simple way to eliminate
static rules in the front-end or auto-generate them pays off.
Also, in the case I'm referencing, the schema was complex. The
delete failure could easily be due to an indirect relationship
rule. The number of possible permutations was high.

Actually, I did end up using a custom deletion routine, but the
routine parsed the error text to see what table was being
complained about, then looked up a friendly entity name from a
table. Deletions, though, were only part of the problem. There
were also database trigger errors on insert/update, etc., and
these could be tied to tables I had bound continuous subforms
connected to. Without trapping every attempted update, and
implementing it in code, there was no way to get sufficient error
information from code. Also, it turns out that with client/server
apps, Access is writing to the field objects in the ODBC recordset
before calling BeforeUpdate, so anything the database driver
complains about, the user gets the ODBC error message. I think
the Form_Error event can catch those, but it only gets the error
number - the "ODBC Call Failed" error number. Useful.


Sounds like a problem in the ODBC driver, not with Access. Is that
your only option for access to this data?


No - it's not a problem with the ODBC driver. It's a problem wiht the fact
that Access doesn't give you access to ether the error description or the ODBC
Errors collection when these errors occur outside of VBA code. It can be
solved only by making sure that field and record updates occur -only- from
within VBA code, and this can only be achieved using using awful kludges with
their own serious side effects when bound forms (such as the handy continuous
subform) are used. If all forms are unbound, it's not a problem, but this
defeats much of the reason for using Access.
Seems to me that you've gotten yourself into this position by
attempting to use too much of Access's built-in functionality. At
least, that's the only way I can see that the child deletions
issue could be involved, since if you're coding the deletion
yourself (instead of using the builtin UI with AllowDeletions
turned ON), you would either have anticipated the child record
deletion problem, or could very easily put it into your code once
you discovered it was a problem.


Again, why would I want to duplicate logic like that unless I
couldn't avoid it? . . .


Control? If you need better confirmation that default behaviors can
give you, you have to do something different. I don't usually use
default deletion behaviors with simple deletes, even when there are
no child records.


Yes, I agree, but we shouldn't have to duplicate code to get that control.
Since Access insulates what happens in bound forms from what the code can see,
we end up with no alternative other than the duplication. That's why this is
a down-side of Access for making large apps that are easy to maintain.
. . . That's just asking for maintenance trouble
down the road. . . .


I don't see the issue as being as great a problem as you describe it
to be.
. . . Since I'm sometimes working with schemas I don't
maintain, this is a particularly thorny issue. Now, it turns out
that for about 3/4 of my clients, I maintain the schema, and they
are somewhat computer literate, having no problem understanding
the raw error messages from Access. There is simply no issue in
those cases.


Well, then, they are smarter than my clients.


They are smarter (on technical matters) than most clients, yes. I lucked out
on that one.
They're smarter than me, too, in some cases -- many of Access's raw
error messages make my eyes cross (the write conflict dialog, for
instance).
I'm just talking about database-level error messages such as constraint
violations, etc. Those clients also are not using server back-ends.
Or, perhaps you rejected a DELETE command button for some reason?


I'm not averse to a Delete button or trapping the
BeforeDeleteConfirm action, . . .


I'd never depend on that because if SetWarnings is OFF, that event
doesn't fire.


My code always turns it on to make sure.
. . . but I'll still want to have thought of
how the code will be maintained, preferably some way that it willb
e hard to make a change and not know this code will be affected.
That might involve, and sometimes does involve some sort of code
generation.


I don't see it, myself. Of course, my schemas follow certain rules
relating PK names to table names (tblTable has PK TableID), so I can
pass a list of tables, the first being the parent, the second being
child tables, and generate code to check for child records, generate
message text for a confirmation dialog, and then do the deletion of
the child records before the parent.


Nice. But, I see you agree that code generation is helpful for making Access
apps mroe supportable. If the code generation can be re-run post-release, so
much the better.
Now, with multiple levels of dependency, I'm not sure which strategy
I'd use. If it was something I encountered many times in an app, I'd
probably want to automate it, perhaps by simply walking the
relationships collection to figure out what relations there were,
which is, certainly, the most definitive way to manage it.

More likely is that I'd not need multiple levels of dependency
except in a few cases, and in those cases, I'd have to have multiple
calls to my outside routine, and some way in that to postpone the
deletions until the end (probably a wrapper function that wraps it
all in one transaction). I'd also need a way to create a single
coherent confirmation message, which would probably be the hardest
part of it all. All that sounds like it belongs in a wrapper class
module.
I focused too much on the case of deletion. Deletion can be handled.
In any event, I still see no role for code generation, since nothing
in the code I'm talking about would need any specifics for running
in particular contexts -- it would get its needed information either
from naming conventions or from walking the relationships.


I'm not advocating code generation for handling primarily for handling
deletes, though there's certainly a good role for it there. What I'm talking
about is more an issue of making code that's internally consistent and has a
consistent GUI style, etc. This gets harder on a big application.

For instance, what if you use code to build queries out of templates with
replaceable arguments for table and field names, and you also have "chunk"
templates that can be shared between queries with common pieces. Now, your
code generator can spit out all the queries (as embedded SQL string constants
and/or saved querydefs) on demand, and it can tell you if anything tried to
use a field or table that doesn't exist. Also, the chunks act as a code reuse
mechanism more manageable than saved queries of saved queries, since there's
no static checking to see what depends on what.

Now, take forms. Have a small set of standard template forms, and some code
that can take a list of query names, and generate forms like the template
form, but with additional fields added in a section marked with, say, a
specially named rectangle. Presto - consistent look and feel. Now, let's
say, the generator can also find which fields require high-level validation
and which function names to call to do that high-level validation, and
auto-generate the event calls from the form.

That's what I'm really talking about with code generation. That, and things
like inserting and updating standard error handling blocks in the code (so the
templates only need to be maintained in one place), and things along those
lines.
I actually prefer to trap BeforeDeleteConfirm over adding a Delete
button just because Access maintains a consistent metaphor and
look-and-feel for me. . . .


SetWarnings can be turned off, and in that case, the event doesn't
fire, so I'd never depend on it. Ever.


I just keep checking it, and turning it back on if it gets turned off. I got
in this habit after spending way too long getting consistent shortcut menus
onto all the forms for a client who insisted on right-click delete and also
needed complex delete logic (I guess code generation could help there, too).
I've never had a problem using the BeforeDeleteConfirm event.
....
Nov 13 '05 #32

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:n7********************************@4ax.com:
On Wed, 21 Jul 2004 18:23:17 GMT, "Larry Linson"
<bo*****@localhost.not> wrote:
"Steve Jorgensen" wrote in
> Really? I've found that the benefits Access
> offers tend to diminish with the size of the app
> because Access is lacking in features for removing
> duplication,
What kind of "duplication"?


In most apps, there are many similar forms, many similar subforms
used in different places, many controls needing similar event
handling, etc. Access does not provide a way to keep these desing
elements in one place where they can be maintained easily and
consistently across an application. Consequently, changes in these
areas are expensive and error prone, so either they don't get done
when they might be beneficial, or they're implemented with
problems.


Well, that depends on how you design your code. You can perfectly
easily design code for Access that is usable from multiple contexts
and maintainable in a single place in your application. It's how I
treat any procedure that is performed from more than one location --
it gets moved to a public module and generalized to handle the
conditions under which I know it will be called. As new conditions
arise, that single block of code is revised and then retested in all
the circumstances in which it is called.

How would any other development platform be any different?

You can duplicate code on any development platform.

You can choose to modularize and share code on Access, perhaps not
as easily as on some other development platforms that are engineered
with that in mind, but it certainly isn't very hard to do -- I've
been doing it from the point at which I became an advanced enough
Access programmer to understand the issues (i.e., a lot of the
"easy" ways Access provides to do things, such as RunCommand, can't
be used, since they are too context-specific).
> finding errors at build-time, etc.,


As there was no "build-time" involved with Access, I don't know
what you mean.


Well, that's exactly what I mean. There's no time when a static
analysis of all the application components is done to make sure
they're at least internally consistent. Static checking doesn't
solve all problems, obviously, but without it, there are pretty
much always problems. For instance, have you ever added Option
Explicit to a module written by someone who doesn't know to turn
it on, and not had a compile error? That's a bug waiting to be
found and painstakingly debugged if no one ever tries turning on
Option Explicit and letting the compiler find it. I even get
bitten by this in my own code when I work on a new system, and
have not yet remembered to turn on Require Explitit Variable
Declaration. Almost always, by the time I see that Option
Explicit is missing, there's a variable name typo or something
that I hadn't seen.


The first time I sit down at any Access system where I'm going to be
debugging code, I change the module font, Option Explicit, the
exlusive use of Code for events, and the most intrusive code
validation options (I turn off notification of code errors and
depend on color/formatting to tell me when a line doesn't compile --
the dialog is way too intrusive for me). The only time I wouldn't do
this was if I were sitting at another Access programmer's PC -- I
have no qualms about doing it at a user's workstation, since they'll
never be programming Access (my users, that is).
In Access, we have similar problems that are not corrected by a
simple option Explicit. . . .
Option Explicit is a problem created by VB's long history, and I see
no reason why the option to leave it out should not simply be
removed. Everyone who has ever given it any thought agrees it causes
more problems than it solves.

But it's not going to change.

And I haven't had any issues with it, myself -- I don't generally
create new modules (either standalone or in forms/reports) at any PC
but my own, or on a PC at a client where I program regularly, which
will already be set to use my programming defaults.
. . . We have everything from DoCmd.OpenForm
"frmWhoKnowsIfIExist" to a function call from a shortcut menu
passing a reference to
[txtWhoKnowsIfIStillExistAndHaveTheSameName] to a function.
There's no way to add a code layer between the UI and the database
layer, aside from unbinding almost everything or using automated
RecordSource properties. Without that shared code layer there are
multiple possible points of failure for a bad table or field
reference. Also, there are only 2 places to put any validation
code, the table itself, or on every form that interacts with a
particular data element.
That's partly a function of what back end you're using, no? In a
server back end, you've got triggers for certain kinds of things
that with a Jet back end, you're forced to put in your forms.

One good practice is to try to have a single table's data editable
in as few contexts as necessary, and where possible, to re-use a
single form in multiple contexts. This is related to my general rule
of non-editable continuous forms -- I'll likely pop up a standalone
detail form from the continuous form for editing, and that single
detail form will be used from *all* contexts where it is relevant to
edit the record.

Why do I make these rules for myself?

To make my apps more maintainable.

Sometimes it means that this multipurpose detail form is quite
complex in the way it formats itself for different contexts (certain
fields may be hidden or disabled when called from certain contexts,
for instance). But that is easier to maintain than multiple
instances of the same form.

[]
> . . . For instance, when error handling
> requirements cause you to unbind all your
> editable forms, how much benefit are you
> getting from Access' facility with bound forms?


Well, if you use unbound forms, then obviously you'd be getting no
benefit from Access' facility with bound forms. I'm not sure what
kind of error handling requirements would cause you to unbind all
your forms... I haven't encountered any in using Access since
1993.


Well, a recent job, for instance. They required error messages
that actually explained to the user what was wrong with what they
just did. Access bound forms have many problems in this regard.
For one thing, the buit-in checking always fires before the custom
checking, so you don't get a chance to check control data yourself
if Access already knows there's something wrong with it.


That's simply not true. You just have to use the BeforeUpdate event
of the control to do it, rather than attempting to do it in the
record's BeforeUpdate. To me, that's the right place to do it -- you
inform the user of the error as soon as they make it. The project
I've been working on this week has involved just that -- perfecting
a single piece of code that I use to test values in the BeforeUpdate
event of 6 controls on a form (the data is collected from meters
that never go down, so I load the previous record's data and compare
it; if the new entry is lower than the old entry, I inform the
user). The BeforeUpdate event works fine for this.

Of course, I'm using a Jet back end. Maybe with a server back end,
things work differently. I don't know.
Furthermore, if there's a problem at the database level during
save, there's no way to programmatically get the text of the error
to analyze it, and tell the user what's going on in more
comprehensible terms.


Isn't that a problem with the back end you're using (or the driver
-- an ADO driver may be able to get you more info than ODBC), rather
than a problem with Access? That is, are there tools that *could*
get you the information you want?
> And, then datasheet views and continuous forms
> are uneditable, so you end up choosing whether
> to force the user to do another click to open
> another form or use something like a listview
> control, etc.

> Now, is this better than starting with VB? Well,
> yes because of the report, but you see
> where I'm going with this.


No, I don't see at all. As I have never been faced with any
requirements, error-handling or other, that would cause me to feel
compelled to use unbound forms, I find it difficult to follow.


Well, my issue with it is the lack of a sufficient intercept
between Access and the user. The Fomr_Error event would be a
great way to handl it if it provided any information besides an
error number that's rarely useful by itself.


The way I read what you've written about this, all your problems are
with insufficient exposure of the error messages from your back end.

That is *not* an Access problem.
I use no (none, not any, zip, zilch) datasheet views in developed
applications, so that would not be a concern to me. And, since I
would be unlikely to do an Access application with all unbound
forms, what you described would not apply. Most of the
applications that I have worked on, whether mine or inherited, had
NO unbound forms other than switchboards and selection forms. That
one that was "tainted" by weak developers had a couple, both of
which I had to go maintain, not because there was a change, but
just because the original developer left some thing/things out
when re-implementing Access' features.


None of my comments so far really make a distinction between
continuous forms and datasheet views, so this is another topic
but...

I used to avoid datasheet views like the plague until I recently
had a client that demanded them, and got mad at me for
implementing forms that were not in datasheet view. I've since
come to appreciate the power datasheets give to the user with
fairly little effort on the part of the programmer. There are
work-arounds to many of the seeming deficiencies of datasheets.
One good trick is to put a datasheet view in as a subform of an
unbound master form, and add code to resize the subform along with
the master form. You can put any kind of fancy controls you want
on the main form, and you can use sub-subforms to implement
sub-datasheets which are another really nice abstraction.


I've made exactly the same migration in my opinion of datasheets as
you, Steve, hitting on the exact same solution for many of the
"problems" of them (which for me were just that I couldn't have any
of my own controls in the header/footer, which you can get around by
embedding the datasheet in an unbound parent form).

[]
For the kind of "normal business database applications" that I've
been doing since 1993, though, even large ones, there's nothing
that comes close.


And again, I'm pretty much with you, but I know you've also had to
tell the customer at times that certain capabilities were too
expensive to implement with the Access infrastructure in place,
and others took longer than they would have in another platform.
That's not Access bashing, per se, since the same would be true
with any tool you use, but the point is that the tool should be
the one that is best aligned with the particular problem. The
larger the problem, the more care must be taken in this regard.
If Access is chosen for a complex system, some high-level
techniques may have to be brought to bear to implement everything
and keep the cost of changes manageable.


I still don't really see this as a case of "outgrowing" Access. It's
more a matter of having chosen the wrong tools on the front end (and
in your case, it sounds like the back end is the source of many of
the problems, not Access).

Naturally, you can't always forecast everything that's going to be a
problem until you dig into it. But my bet is that you'll be less
willing to go with the Access/db combination in the project that you
are frustrated about if you encounter another potential app with the
same pairing of front end/back end. That's reasonable.

But I still think it's less a deficiency in Access than it is a
deficiency in ODBC in general, and, perhaps, in the ODBC drivers for
the particular back end you're using (I'm assuming you're not using
SQL Server or you'd use ADO). There was a justification for the
creation of ADO as a next-generation successor to ODBC precisely
because ODBC was lacking a lot of things that were nowadays
supported by most server database engines. But if you're stuck with
the older technology because no one has written a better driver, is
that really the fault of Access? It may be reality, it may be a
major problem, but it seems to me that you can't say that Access is
lacking when the problem is being caused by poor information
reporting in something completely outside of Access.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #33

P: n/a
"Steve Jorgensen" wrote
What I said was "What if you decide
to change a relation to a cascade delete".
Now, if the code is not updated at the
same time, the code is enforcing an
obsolete rule that would -not- be enforced
by the database engine.
Perhaps, Steve, you work in a corporate team environment where "things
sometimes fall through the cracks". But, even in such environments, it is
the responsibility of the developer to understand the effects of changes and
accomodate them. Further, it is _someone's_ responsibility to test changes
before they are released to the user, so it is not _they_ who receive the
surprises from developers who haven't met their responsibilities.

The reason this is an issue is because the
front-end logic duplicates the back-end logic,
and the chances for sync problems between
the 2 are why duplication should be
avoided.
Perhaps, in a team environment, someone might make such a change to the
server DB and the UI developers not be informed. If so, that is a flaw in
the development environment, not in the tools nor the approach. In all the
team environments in which I have worked, there were procedures and
guidelines to prevent the very kind of "overlooked effects" that you
describe.
I definitely have had cases go the other
way, and I've had the update come
from a database maintainer that was
not me.


You can't fix all the defects in the working arrangement by using different
tools, languages, and code.

I can see why you'd mention "code generation" in the VS.NET environment.
Unlike classic VB and Access, it is very code intensive... not the
"point-and-click-your-way-to-a-friendly-UI-and-sprinkle-just-enough-code-beh
ind-it-to-make-it-work-smoothly". It's an evironment where the
originators/architects thought that "glitz and glitter" were necessary parts
of business applications, so you'd need "visual inheritance" and need to be
able to modify the controls, etc.. The "Whidbey" beta seems to be, maybe,
2/3 of the way to the hype and promise of the original announcement. Perhaps
the next "Orcas" release will get them most of the way there, and it will be
as useful, or nearly as useful, as classic VB and classic Access for the
modest business database applications for which both were so well suited.

Hang in there, and maybe those new releases will have some of the code
generation that is needed with those products. Then again, given the
history, they may be third-party products at a(n unconscionable) price.

Best regards,

Larry
Nov 13 '05 #34

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:e0********************************@4ax.com:
Oops - here's that link.

http://www.exoftware.com/documents/XPVB_000.pdf

I also meant to say that just I'm saying all this because the
question was about what the disadvantages of Acces are for an
application like this. Just because I'm pointing out what I see
as the disadvantages doesn't mean it's a bad choice. Every tool
will have its disadvantages. Access also has many benefits, and
Access very well may be your best bet for this project.


Interesting article, but I'd sure like to see the same thing done by
people who were more sympathetic to VB, more experienced with it.

And, of course, I just think the ideas of extreme programming are
vastly overrated. It's not the methods, but the goals those methods
are designed to meet that matter, and those goals can be met through
different means.

That is, just because XP can't be applied doesn't mean you can't
produce good, reliable programs easily.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #35

P: n/a
"Steve Jorgensen" wrote

David seems to have addressed very well most of the points to which I'd have
replied (except "datasheet" view, and I'll write that off as my personal
prejudice, but note that quite a large number of developers share it), so
I'll just take this near-the-end paragraph and respond.
. . . For one thing, multi-UI apps or apps
with complex business logic. For these
applications, you want a business logic layer,
and Access is happiest talking directly to the
database, thank you very much. So, the only
way Access can share business logic with
another app is to either code all the business
logic as stored procedures (no thanks - I've
tried that), or to use a code generator to
build large parts of your Access app - a fine
solution, but you have to know what you're
doing.


I've been involved with applications that had multiple front-ends or clients
accessing the same back end (mostly server DBs, but occasionally an
Access-Jet multiuser). Because it was the different roles of the users, and
the different requirements of what they'd be working on that dictated
separate UIs, there just wasn't a lot of shared logic -- occasionally, we
did use a library (MDA, MDE) for shared stuff.

I'm still just at a total loss to understand your insistence on "code
generation" here. What kind of code generation tools is it that you are
talking about? VS.NET, AFAIK, is more code-intensive than Access or classic
VB, and more complex, with a steeper learning curve (witness Kathleen
Dollard's guest editorial recently, about saving the hobbyist programmer,
but in which she admitted that the continued learning curve was a problem
for most professional developers as well -- one of her suggestions was that
code generators need to be conceived and implemented, but I saw no
recommendations to use existing code generators).

I am not aware of any useful code generators that could be used to build a
large part of an Access application. Could you clarify?

We always avoided stored procedures (at the customers' request -- easy
around here to find competent Access folk on the contract market, not so
easy to find competent server folk, and if you did, they often didn't know
Access so couldn't work on anything but the server DB). But that would be
merging business logic in the data access layer, anyway, wouldn't it, and
perhaps equally as much a violation of multi-tier?

All of the Access applications/clients on which I have worked have done just
fine without a separate "business logic layer"... in my view, that is
primarily a tier that is needed when you are doing enterprise or web-based
applications when the UI/presentation layer is a browser.

As to having to tell some customer that "X is difficult with Access, so we
can't do it" -- that wouldn't come up in discussions of a new application,
because in such a case, I/we would recommend appropriate technology. If they
have an existing application, I/we would, of course, tell the truth; there
are many ways, though, to "do unusual things" from Access: COM Automation;
shell, Function calls to libraries done in other languages, API calls, etc.
If employing some of those wouldn't allow us to accomplish the customer's
goals, then, yes, they'd have a decision to make.

The key here is "use appropriate technology" to solve the problem at hand.
If I were to recommend that a customer needed an enterprise distributed
application to address their needs, it's unlikely that I would be involved
in _implementation_ of that solution. When it comes to implementation,
"Access is what I do", but it's not "all I know".

And, your final statement applies, IMNSHO, to every developer of computer
software: you have to know what you're doing. Unfortunately, history, even
recent history, is full of failed projects demonstrating that not every
architect, designer, developer, and manager actually does know what they are
doing.

Larry Linson
Nov 13 '05 #36

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:8i********************************@4ax.com:
On Thu, 22 Jul 2004 15:47:19 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:hp********************************@4ax.co m:
On Wed, 21 Jul 2004 23:20:52 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:2g********************************@4ax.co m:

> the problem was that the back-end was on a server, and the
> server-generated error messages for things like trying to
> delete a record with non-cascade-delete relations were not
> human-friendly.

I never use or allow the builtin deletion routines. I always
have a command button for deletion that allows me to control the
order of events, so in the case of your deletion of a record
selected in a continuous form, it would check for child records,
produce a suitable deletion confirmation message, and, if the
user answered the confirmation in the affirmative, then, in a
transaction, delete the child record(s), then the parent
records, and then requery the subform and reposition the current
position (if appropriate).

Isn't that the standard way to do this?

Well, no. It's duplication of logic between the database layer
and the code layer. . . .
Well, you haven't explained how you are doing the delete -- is it
with a command button and code that checks the error conditions?
You say the problem is with your back end not adequately reporting
the error conditions, right? How, then, is this a problem with
*Access*, and not with your back end db access method?


The error info from the back-end is fine. Access simply provides
no way to read that error information when the update attempt is
made via a bound form and not from VBA code. That means updating
data through a bound form is a problem.


Hold on -- I thought the problem was with deletions, not updates?
I'm talking about what amount to unbound deletes, so why would bound
matter?

I think you're mixing two subjects here in your reply, and that
makes it hard for me to follow.
. . . What if you decide to change a relation to
cascade delete, but you forget to remove the code checking?
Now, you have youre code rule thwarting your data rule. . . .


Thwarting? It's doing in code what would be handled in the
database engine, so I don't see that it's an issue. Sure, it's
duplication, and not necessary, but it's not going to cause
problems that I can think of.


What I said was "What if you decide to change a relation to a
cascade delete". Now, if the code is not updated at the same time,
the code is enforcing an obsolete rule that would -not- be
enforced by the database engine. The reason this is an issue is
because the front-end logic duplicates the back-end logic, and the
chances for sync problems between the 2 are why duplication should
be avoided.


Well, first off, the only thing that matters in terms of
functionality is the confirmation message to the end user. Seems to
me you'd want that whether or not cascading deletions were one or
off, since the end user needs to be reminded of the implications of
a deletion.

Secondly, if you want it to skip the child deletions when cascade
deletions are on, then set up your code so calls to it are easy. For
instance, if your code was written to take parent table and a list
of child tables, you could have a flag for whether or not to
predelete the children. If you have cascade delete on in the final
code, then change one argument when calling it from TRUE to FALSE.

These are all code design issues, and seem pretty straightforward to
me, though I really doubt I'd give it much thought if cascade
deletes were turned on. What about the code would be inferior to
letting the database engine cascade it? The number of hits on the
database? Locks? Transaction failure?
And, indeed, I can't think of a circumstance in which it's likely
that I'd be making that kind of change to a db schema that late in
the implementation of the application. If such a thing were to
change, it would be in the *other* direction, removing cascade
delete, so you'd need to add the code in.


I definitely have had cases go the other way, and I've had the
update come from a database maintainer that was not me.


Sounds pretty frigging easy to preplan for in a fashion that won't
require rewriting any code, just altering calls to that code.
. . . Furthermore,
the code you're talking about may have to be called from
multiple places.


If I had many places where I needed to do this, I could easily
abstract out code to do the job, and call it in one line each time
a deletion is needed.


Good. Here's another issue I have with pre-checking
relationships, though. It's duplicating checking the database
engine is about to do for you, so it's adding load to the database
access. Also, there's a small chance that the check will succeed
just before the data changes, so that the real update won't.
These a minor issues, I know. I just consider that inelegant when
one could just as well let the database engine do the checking,
trap the error, and parse out what happened. Heck, this way, the
code might not even have to know before hand which relationships
could be a problem, just parse out what they mean, and report the
issues in freindly terms.

Of course, as you say, this is all no problem when just deleting
because that's trappable, and you can handle it with whatever
fancy code you want. The problem is with low-level field type
rules (including things like deleting the contents of a required
field) and database rules that fire on update, not on delete.


Er, use the BeforeUpdate event for this. Unless Access deals
differently with non-Jet data sources than with Jet, it works fine.
I've been working with it this past week, fine tuning validation
code for an app.
...
I've never actually implemented it that way, myself (when I have
cascade delete turned off, it usually means there is never a case
when the parent record is allowed to be deleted, ever), but I
can't see it as being difficult.
It is a maintenance issue, though, and much more so when the
back-end is not yours, and the front-end may have to be maintained
later in the life cycle, possibly by someone else. That's where
having a simple way to eliminate static rules in the front-end or
auto-generate them pays off.


Is it possible to get to the relationships in your back end via DAO?
Or through some other method? If it is, then you can write code that
will do what you need. It would be complicated code, code that I'd
want help writing (and that I'm sure others in this newsgroup would
be happy to help with -- hint, hint), but it is certainly
discoverable in code, without needing to build any rules into your
user interface.
Also, in the case I'm referencing, the schema was complex. The
delete failure could easily be due to an indirect relationship
rule. The number of possible permutations was high.

Actually, I did end up using a custom deletion routine, but the
routine parsed the error text to see what table was being
complained about, then looked up a friendly entity name from a
table. Deletions, though, were only part of the problem. There
were also database trigger errors on insert/update, etc., and
these could be tied to tables I had bound continuous subforms
connected to. Without trapping every attempted update, and
implementing it in code, there was no way to get sufficient
error information from code. Also, it turns out that with
client/server apps, Access is writing to the field objects in
the ODBC recordset before calling BeforeUpdate, so anything the
database driver complains about, the user gets the ODBC error
message. I think the Form_Error event can catch those, but it
only gets the error number - the "ODBC Call Failed" error
number. Useful.


Sounds like a problem in the ODBC driver, not with Access. Is that
your only option for access to this data?


No - it's not a problem with the ODBC driver. It's a problem wiht
the fact that Access doesn't give you access to ether the error
description or the ODBC Errors collection when these errors occur
outside of VBA code. It can be solved only by making sure that
field and record updates occur -only- from within VBA code, and
this can only be achieved using using awful kludges with their own
serious side effects when bound forms (such as the handy
continuous subform) are used. If all forms are unbound, it's not
a problem, but this defeats much of the reason for using Access.


The BeforeUpdate event doesn't work with a server back end?
Seems to me that you've gotten yourself into this position by
attempting to use too much of Access's built-in functionality.
At least, that's the only way I can see that the child deletions
issue could be involved, since if you're coding the deletion
yourself (instead of using the builtin UI with AllowDeletions
turned ON), you would either have anticipated the child record
deletion problem, or could very easily put it into your code
once you discovered it was a problem.

Again, why would I want to duplicate logic like that unless I
couldn't avoid it? . . .


Control? If you need better confirmation that default behaviors
can give you, you have to do something different. I don't usually
use default deletion behaviors with simple deletes, even when
there are no child records.


Yes, I agree, but we shouldn't have to duplicate code to get that
control. Since Access insulates what happens in bound forms from
what the code can see, we end up with no alternative other than
the duplication. That's why this is a down-side of Access for
making large apps that are easy to maintain.


Access is a RAD tool. That means it makes certain common tasks
extremely easy and fast. Why should it be surprising when certain
non-common problems that it wasn't designed to handle require more
effort?

Maybe you've outgrown the tools -- you either adjust to the
limitations or find new tools. It's when the adjustments become
unaccemptably numerous or important that you have to switch.

[]
Or, perhaps you rejected a DELETE command button for some
reason?

I'm not averse to a Delete button or trapping the
BeforeDeleteConfirm action, . . .


I'd never depend on that because if SetWarnings is OFF, that event
doesn't fire.


My code always turns it on to make sure.


Why not use methods that don't depend on it at all?
. . . but I'll still want to have thought of
how the code will be maintained, preferably some way that it
willb e hard to make a change and not know this code will be
affected. That might involve, and sometimes does involve some
sort of code generation.


I don't see it, myself. Of course, my schemas follow certain rules
relating PK names to table names (tblTable has PK TableID), so I
can pass a list of tables, the first being the parent, the second
being child tables, and generate code to check for child records,
generate message text for a confirmation dialog, and then do the
deletion of the child records before the parent.


Nice. But, I see you agree that code generation is helpful for
making Access apps mroe supportable. If the code generation can
be re-run post-release, so much the better.


Code generation? I'm not talking about code generation at all. I'm
talking about generating metadata that can be processed by
pre-existing code.

I don't think code generation has any place whatsoever in any
project that I'm qualified to work on.

[]
In any event, I still see no role for code generation, since
nothing in the code I'm talking about would need any specifics for
running in particular contexts -- it would get its needed
information either from naming conventions or from walking the
relationships.


I'm not advocating code generation for handling primarily for
handling deletes, though there's certainly a good role for it
there. What I'm talking about is more an issue of making code
that's internally consistent and has a consistent GUI style, etc.
This gets harder on a big application.

For instance, what if you use code to build queries out of
templates with replaceable arguments for table and field names,
and you also have "chunk" templates that can be shared between
queries with common pieces. Now, your code generator can spit out
all the queries (as embedded SQL string constants and/or saved
querydefs) on demand, and it can tell you if anything tried to use
a field or table that doesn't exist. Also, the chunks act as a
code reuse mechanism more manageable than saved queries of saved
queries, since there's no static checking to see what depends on
what.

Now, take forms. Have a small set of standard template forms, and
some code that can take a list of query names, and generate forms
like the template form, but with additional fields added in a
section marked with, say, a specially named rectangle. Presto -
consistent look and feel. Now, let's say, the generator can also
find which fields require high-level validation and which function
names to call to do that high-level validation, and auto-generate
the event calls from the form.


I've done this by storing the rules in tables. Adding fields to the
data structure required adding metadata to the table storing the
rules for the fields (which included friendly names and labels for
the end user, as well as data types to determine validation
methods). Again, I'm a believer in storing the metadata and having
code that will run according to the metadata.
That's what I'm really talking about with code generation. That,
and things like inserting and updating standard error handling
blocks in the code (so the templates only need to be maintained in
one place), and things along those lines.


Well, I don't believe in overuse of error handling code, myself, so
haven't found any need for code generation on that. On other issues,
I would rather store data in tables describing the structure and
then have code that will always work based on that metadata. Making
changes to the data structure would then be only a matter of
entering altered metadata, and once it runs correctly once, you need
no further testing. With code generation, you're constantly in need
of tests of the *code*, not just of the validity of the data. I'd
much rather "debug" data than code.
I actually prefer to trap BeforeDeleteConfirm over adding a
Delete button just because Access maintains a consistent
metaphor and look-and-feel for me. . . .


SetWarnings can be turned off, and in that case, the event doesn't
fire, so I'd never depend on it. Ever.


I just keep checking it, and turning it back on if it gets turned
off. I got in this habit after spending way too long getting
consistent shortcut menus onto all the forms for a client who
insisted on right-click delete and also needed complex delete
logic (I guess code generation could help there, too). I've never
had a problem using the BeforeDeleteConfirm event. ...


I simply don't trust that something that can be turned off by the
user and in other parts of the code will always be on.

It's also why I only very rarely use On Error Resume Next. Once off,
I don't trust that it will get turned on in a timely manner and that
it will stay on.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #37

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:Sq*******************@nwrddc03.gnilink.net:
I'm still just at a total loss to understand your insistence on
"code generation" here. What kind of code generation tools is it
that you are talking about? VS.NET, AFAIK, is more code-intensive
than Access or classic VB, and more complex, with a steeper
learning curve (witness Kathleen Dollard's guest editorial
recently, about saving the hobbyist programmer, but in which she
admitted that the continued learning curve was a problem for most
professional developers as well -- one of her suggestions was that
code generators need to be conceived and implemented, but I saw no
recommendations to use existing code generators).


We've come full circle.

Access allowed us to generate working apps without having to write
(or generate) code ourselves.

Of course, there was actually tons of code back behind those Access
forms and reports, just not code that *we* wrote.

A RAD tool should hide as much of the code as it can from me so I
don't have to deal with it, but it should expose the internal
workings as much as necessary to allow me to troubleshoot problems.

That is one area where RAD tools can never equal traditional
development tools, since there's always a black box aspect of it
that you can't get inside of. I think Steve is running up against
this (insofar as I understand his problems -- I still don't
understand why he can't handle his validation in the BeforeUpdate
event of his controls), and it may be time for him to evaluate tools
that are closer to the metal.

However, I also think Steve is an inveterate worrier and a
perfectionist. This is why he's so good at what he does.

But it also means that he's prone to worrying too much about things
that don't really matter much. He tends to go off on wild goose
chases that may solve the problem, but are such Rube Goldberg
monstrosities that even he admits it was a waste of time, except as
a learning experience.

I don't mean this as a criticism of Steve -- it's a FEATURE not a
BUG! :)

But it does lead to these long discussions, that often result in
Steve working out for himself methods of solving his problem that
are not as full-featured as what he started out to pursue, but which
give him most of the bang he was seeking for his buck.

In this case, it's all been too generalized for me to offer much in
the way of suggestions. I would like to hear the details of the
bound validation problem. I've never run onto it myself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #38

P: n/a
On Thu, 22 Jul 2004 18:43:04 GMT, "Larry Linson" <bo*****@localhost.not>
wrote:
"Steve Jorgensen" wrote
What I said was "What if you decide
to change a relation to a cascade delete".
Now, if the code is not updated at the
same time, the code is enforcing an
obsolete rule that would -not- be enforced
by the database engine.
Perhaps, Steve, you work in a corporate team environment where "things
sometimes fall through the cracks". But, even in such environments, it is
the responsibility of the developer to understand the effects of changes and
accomodate them. Further, it is _someone's_ responsibility to test changes
before they are released to the user, so it is not _they_ who receive the
surprises from developers who haven't met their responsibilities.


Actually, I work in several environments, including some corporate team
environments, and for companies doing outsourced contracts for corporations.

Yes, everyone should be aware of the potential side effects of what they are
doing, but everyone should also contribute to the flexibility and ease of
maintenance by putting in the extra effort to make sure things are hard to
break, and thay when they do break, they'll usually do it immediately and
loudly, so they can be fixed up front. It's also valuable to make sure that
every piece of functionality exists in as few places as possible, preferably
one, and that none of those places are obscure, and likely to be unknown by a
future maintainer (like you after a few months have gone by).

Studies have shown that it costs 3 times more to fix a bug that makes it to
testing than to engineer it out up front, and it costs 3 times more than that
to fix it after it makes it to production.
The reason this is an issue is because the
front-end logic duplicates the back-end logic,
and the chances for sync problems between
the 2 are why duplication should be
avoided.


Perhaps, in a team environment, someone might make such a change to the
server DB and the UI developers not be informed. If so, that is a flaw in
the development environment, not in the tools nor the approach. In all the
team environments in which I have worked, there were procedures and
guidelines to prevent the very kind of "overlooked effects" that you
describe.


OK, but the simplest procedure to make sure of those things is to make sure
that, to the greatest extent possible, some sort of automated check process
(such as a compile) will simply fail up front when something is out of whack.
Anything else, in my opinion, is a kludge that should be fixed when you can
think of a clever way to do it, and that leaves fairly few things your process
outside of the code itself has to keep careful track of.
I definitely have had cases go the other
way, and I've had the update come
from a database maintainer that was
not me.


You can't fix all the defects in the working arrangement by using different
tools, languages, and code.


No, but I can't imagine thinking that means a tool with way fewer means of
static validity checking, and lots of ways to make silently (until rigorous
testing - some of it by the final end user) bad code is just as good as a tool
with lots of mechanisms for making cleaner code, less likely to have bugs, and
not actually encouraging application structures that are not automatically
checked for simple validity.
I can see why you'd mention "code generation" in the VS.NET environment.
Unlike classic VB and Access, it is very code intensive... not the
"point-and-click-your-way-to-a-friendly-UI-and-sprinkle-just-enough-code-beh
ind-it-to-make-it-work-smoothly". It's an evironment where the
originators/architects thought that "glitz and glitter" were necessary parts
of business applications, so you'd need "visual inheritance" and need to be
able to modify the controls, etc.. The "Whidbey" beta seems to be, maybe,
2/3 of the way to the hype and promise of the original announcement. Perhaps
the next "Orcas" release will get them most of the way there, and it will be
as useful, or nearly as useful, as classic VB and classic Access for the
modest business database applications for which both were so well suited.
You seem to think that, by code generation, I mean only VBA code. Actually, I
mean everything that's part of the application, so that means forms, reports,
saved queries, command bars, graphs, etc.

Actually, though, what's so wrong with wanting to have our cake and eat it
too? The form code that .NET makes is hidden by default, so you don't have to
think of it as code at all until and unless you want to. When you do want to,
though, you can, and that's great and powerful, saves time, reduces stress,
and walks your dog (OK, I went a step too far, there).

Using automated generation, you can do a lot of the same with Access forms,
and why not do it when the benefit is worth the cost, considering that a very
large number of forms, reports, etc., will be required by the time the
application is done? Why not have forms and reports easy to reliably change
en mass for both visual and business rule reasons?
Hang in there, and maybe those new releases will have some of the code
generation that is needed with those products. Then again, given the
history, they may be third-party products at a(n unconscionable) price.


Actually, I am assembling a team to put out some of those tools for free. See
http://timestream.net/learningxp/. I'm hoping there will eventually be an
Access.NET, but I'm not holding my breath on seeing it any sooner than 4 or 5
years from now.
Nov 13 '05 #39

P: n/a
On Fri, 23 Jul 2004 01:04:13 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....
>I never use or allow the builtin deletion routines. I always
>have a command button for deletion that allows me to control the
>order of events, so in the case of your deletion of a record
>selected in a continuous form, it would check for child records,
>produce a suitable deletion confirmation message, and, if the
>user answered the confirmation in the affirmative, then, in a
>transaction, delete the child record(s), then the parent
>records, and then requery the subform and reposition the current
>position (if appropriate).
>
>Isn't that the standard way to do this?

Well, no. It's duplication of logic between the database layer
and the code layer. . . .

Well, you haven't explained how you are doing the delete -- is it
with a command button and code that checks the error conditions?
You say the problem is with your back end not adequately reporting
the error conditions, right? How, then, is this a problem with
*Access*, and not with your back end db access method?
The error info from the back-end is fine. Access simply provides
no way to read that error information when the update attempt is
made via a bound form and not from VBA code. That means updating
data through a bound form is a problem.


Hold on -- I thought the problem was with deletions, not updates?
I'm talking about what amount to unbound deletes, so why would bound
matter?


I'm agreeing with you that deletions were a bad example. This can be handled
without resorting to extreme measures. It's actually all the -other- error
handling cases that have been more hassle to work around.

....
What I said was "What if you decide to change a relation to a
cascade delete". Now, if the code is not updated at the same time,
the code is enforcing an obsolete rule that would -not- be
enforced by the database engine. The reason this is an issue is
because the front-end logic duplicates the back-end logic, and the
chances for sync problems between the 2 are why duplication should
be avoided.


Well, first off, the only thing that matters in terms of
functionality is the confirmation message to the end user. Seems to
me you'd want that whether or not cascading deletions were one or
off, since the end user needs to be reminded of the implications of
a deletion.


I generally consider the enabling of cascade deletions to be an indicator that
a design decision was made that the behavior would be intuitive and expected
to the user in that case, so no warning would appear. In many of the other
cases, the user should have no option to do a "cascade delete" from code, and
must manually check related records, and delte them first.

I'm skipping replying to the rest of the deletion points because, as I said
above, the deltion case is not the sticky case that requires extraordinary
work-arounds.
....
....
Of course, as you say, this is all no problem when just deleting
because that's trappable, and you can handle it with whatever
fancy code you want. The problem is with low-level field type
rules (including things like deleting the contents of a required
field) and database rules that fire on update, not on delete.


Er, use the BeforeUpdate event for this. Unless Access deals
differently with non-Jet data sources than with Jet, it works fine.
I've been working with it this past week, fine tuning validation
code for an app.


That's the rub. Access check many conditions before calling BeforeUpdate, so
any attempt to check conditions and tell the user why they are wrong is
thwarted. Try, for instance, to delete the contents of a requried field.
This is made doubly bad, because the error is actually different dependign
whether the back-end is ODBC or not.

Now, what about conditions that are only trapped by database triggers? The
error occurs only when the user saves the record, and the only error info the
code can get is the error number for ODBC Call Failed from the Form_Error
event.
...
I've never actually implemented it that way, myself (when I have
cascade delete turned off, it usually means there is never a case
when the parent record is allowed to be deleted, ever), but I
can't see it as being difficult.


It is a maintenance issue, though, and much more so when the
back-end is not yours, and the front-end may have to be maintained
later in the life cycle, possibly by someone else. That's where
having a simple way to eliminate static rules in the front-end or
auto-generate them pays off.


Is it possible to get to the relationships in your back end via DAO?
Or through some other method? If it is, then you can write code that
will do what you need. It would be complicated code, code that I'd
want help writing (and that I'm sure others in this newsgroup would
be happy to help with -- hint, hint), but it is certainly
discoverable in code, without needing to build any rules into your
user interface.


That's true, and it's true even with a server back-end, though different for
each server, but then what about the business rules that are not expressible
as simple relationships, those that are implemented as triggers, or that
should really be in a middle layer? A different solution is called for, and
the business rule object layer is one of the most common and preferred models.
Access can't really deal with that at all, but code generation could be used
to generate event handlers, etc. that might be able to solve the problem
satisfactorily.

....
Actually, I did end up using a custom deletion routine, but the
routine parsed the error text to see what table was being
complained about, then looked up a friendly entity name from a
table. Deletions, though, were only part of the problem. There
were also database trigger errors on insert/update, etc., and
these could be tied to tables I had bound continuous subforms
connected to. Without trapping every attempted update, and
implementing it in code, there was no way to get sufficient
error information from code. Also, it turns out that with
client/server apps, Access is writing to the field objects in
the ODBC recordset before calling BeforeUpdate, so anything the
database driver complains about, the user gets the ODBC error
message. I think the Form_Error event can catch those, but it
only gets the error number - the "ODBC Call Failed" error
number. Useful.

Sounds like a problem in the ODBC driver, not with Access. Is that
your only option for access to this data?


No - it's not a problem with the ODBC driver. It's a problem with
the fact that Access doesn't give you access to ether the error
description or the ODBC Errors collection when these errors occur
outside of VBA code. It can be solved only by making sure that
field and record updates occur -only- from within VBA code, and
this can only be achieved using using awful kludges with their own
serious side effects when bound forms (such as the handy
continuous subform) are used. If all forms are unbound, it's not
a problem, but this defeats much of the reason for using Access.


The BeforeUpdate event doesn't work with a server back end?


Yes, but it doesn't handle problems at the field update level. Access sets
the ODBC record field value before calling BeforeUpdate, and there are several
kinds of typo that can trigger these errors before you ever get a chance to
trap them.

....
Again, why would I want to duplicate logic like that unless I
couldn't avoid it? . . .

Control? If you need better confirmation that default behaviors
can give you, you have to do something different. I don't usually
use default deletion behaviors with simple deletes, even when
there are no child records.


Yes, I agree, but we shouldn't have to duplicate code to get that
control. Since Access insulates what happens in bound forms from
what the code can see, we end up with no alternative other than
the duplication. That's why this is a down-side of Access for
making large apps that are easy to maintain.


Access is a RAD tool. That means it makes certain common tasks
extremely easy and fast. Why should it be surprising when certain
non-common problems that it wasn't designed to handle require more
effort?


It's not surprising at all, but that's why I'm saying Access might not be
right for some larger applications with oodles of forms, or some kind of
automated generation might need to be used to augment Access' capabilities to
make it workable for such a job.
Maybe you've outgrown the tools -- you either adjust to the
limitations or find new tools. It's when the adjustments become
unaccemptably numerous or important that you have to switch.
I'm actually trying to do both, but more of the former than the latter.
That's one of the reasons I'm doing serious playing with code generation,
because it appears that it will allow me to increase the quality and
flexibility of my apps in the environment I already know well.

....Why not use methods that don't depend on it at all?
Because it saves me rewriting and debugginf what Access already does well. I
find it adequate to use code to make sure the setting is correct.
. . . but I'll still want to have thought of
how the code will be maintained, preferably some way that it
willb e hard to make a change and not know this code will be
affected. That might involve, and sometimes does involve some
sort of code generation.

I don't see it, myself. Of course, my schemas follow certain rules
relating PK names to table names (tblTable has PK TableID), so I
can pass a list of tables, the first being the parent, the second
being child tables, and generate code to check for child records,
generate message text for a confirmation dialog, and then do the
deletion of the child records before the parent.


Nice. But, I see you agree that code generation is helpful for
making Access apps mroe supportable. If the code generation can
be re-run post-release, so much the better.


Code generation? I'm not talking about code generation at all. I'm
talking about generating metadata that can be processed by
pre-existing code.


You said "generate code". I thought you were referring to automated code
generation.
I don't think code generation has any place whatsoever in any
project that I'm qualified to work on.


Possibly, but code generation is not necessarily as complex as you might
imagine.
In any event, I still see no role for code generation, since
nothing in the code I'm talking about would need any specifics for
running in particular contexts -- it would get its needed
information either from naming conventions or from walking the
relationships.


I'm not advocating code generation primarily for
handling deletes, though there's certainly a good role for it
there. What I'm talking about is more an issue of making code
that's internally consistent and has a consistent GUI style, etc.
This gets harder on a big application.

For instance, what if you use code to build queries out of
templates with replaceable arguments for table and field names,
and you also have "chunk" templates that can be shared between
queries with common pieces. Now, your code generator can spit out
all the queries (as embedded SQL string constants and/or saved
querydefs) on demand, and it can tell you if anything tried to use
a field or table that doesn't exist. Also, the chunks act as a
code reuse mechanism more manageable than saved queries of saved
queries, since there's no static checking to see what depends on
what.

Now, take forms. Have a small set of standard template forms, and
some code that can take a list of query names, and generate forms
like the template form, but with additional fields added in a
section marked with, say, a specially named rectangle. Presto -
consistent look and feel. Now, let's say, the generator can also
find which fields require high-level validation and which function
names to call to do that high-level validation, and auto-generate
the event calls from the form.


I've done this by storing the rules in tables. Adding fields to the
data structure required adding metadata to the table storing the
rules for the fields (which included friendly names and labels for
the end user, as well as data types to determine validation
methods). Again, I'm a believer in storing the metadata and having
code that will run according to the metadata.


Some of that can be done dynamically. More of it can be done with generation.
Sometimes, the generation can actually be simpler.
That's what I'm really talking about with code generation. That,
and things like inserting and updating standard error handling
blocks in the code (so the templates only need to be maintained in
one place), and things along those lines.


Well, I don't believe in overuse of error handling code, myself, so
haven't found any need for code generation on that. On other issues,
I would rather store data in tables describing the structure and
then have code that will always work based on that metadata. Making
changes to the data structure would then be only a matter of
entering altered metadata, and once it runs correctly once, you need
no further testing. With code generation, you're constantly in need
of tests of the *code*, not just of the validity of the data. I'd
much rather "debug" data than code.


I have gotten much benefit from error handlers that build a call stack as they
bubble back up the chain. That's a real headache to maintain by hand, but
it's a pretty simple code generation task. It's also pretty simple to
automatically rip the generated code back out for easier editing, then throw
it back in again for production code.

Actually, the problem of testing generated code is not particularly different
that the problem of testing dynamic code that's data driven. In fact,
generated code should be as dynamic as possible, with only the most
troublesome elements being truly generated. Generation activities can include
things like generating named controls that match the field names, generating
the event handling code for a particular rule on all the controls that need
it, and only those controls, etc.
I actually prefer to trap BeforeDeleteConfirm over adding a
Delete button just because Access maintains a consistent
metaphor and look-and-feel for me. . . .

SetWarnings can be turned off, and in that case, the event doesn't
fire, so I'd never depend on it. Ever.


I just keep checking it, and turning it back on if it gets turned
off. I got in this habit after spending way too long getting
consistent shortcut menus onto all the forms for a client who
insisted on right-click delete and also needed complex delete
logic (I guess code generation could help there, too). I've never
had a problem using the BeforeDeleteConfirm event. ...


I simply don't trust that something that can be turned off by the
user and in other parts of the code will always be on.

It's also why I only very rarely use On Error Resume Next. Once off,
I don't trust that it will get turned on in a timely manner and that
it will stay on.


I've developed the same habit. I try to leave it off only for 1 to 3
consecutive statements. I have also found it valuable to split the logic og
statements for which only one half needs to skip error handling, such as the
half that retrieves a property, and the half that puts the value into another
property.

varFromFoo = Null ' Default to Null.
On Error Resume Next
varFromFoo = Me.Parent!txtFoo ' Might not be accessible yet.
On Error Goto 0
Me!txtBar = varFromFoo

Nov 13 '05 #40

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ol********************************@4ax.com:
OK, but the simplest procedure to make sure of those things is to
make sure that, to the greatest extent possible, some sort of
automated check process (such as a compile) will simply fail up
front when something is out of whack. Anything else, in my
opinion, is a kludge that should be fixed when you can think of a
clever way to do it, and that leaves fairly few things your
process outside of the code itself has to keep careful track of.


Your ideal of relying on compile-time validity checking strikes me
as wildly optimistic. Your whole wrapper thing for controls was
designed for that same purpose, and it struck me as ignoring all the
problems that come from run-time issues and actual data.

In Access, you're never going to have it.

I don't know what environment you *will* ever have it in.

And I think it vastly overrates the importance of runtime
conditions. Indeed, I don't think it's possible to convert runtime
problems into compiler errors. That assumes that you can test with
absolutely every state and absolutely every variation of
combinations of data that will ever occur at any point when your
application runs. One can implement error handlers that will allow
you to recover from such errors, but I don't think you can ever
engineer them out by depending on the compiler.

I think you're chasing a chimera on this one, Steve, and needlessly
complicating your coding as a result.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #41

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ol********************************@4ax.com:
I can't imagine thinking that means a tool with way fewer means of
static validity checking, and lots of ways to make silently (until
rigorous testing - some of it by the final end user) bad code is
just as good as a tool with lots of mechanisms for making cleaner
code, less likely to have bugs, and not actually encouraging
application structures that are not automatically checked for
simple validity.


But will you give up all the productivity and ease-of-use that you
get with the RAD tool to get this level of safety?

And will your client be saving money in the long run?

You seem to be seeking perfection.

Access (and Microsoft) has always been about producing something
that was "good enough," when not as close to perfection as possible.
That's why homegrown apps held together with chewing gum and baling
wire often serve perfectly well over extremely long periods of time
-- it's good enough.

And I often find that I doubt the actual ROI in some of my own
"bullet-proof" methods -- they sometimes seem to complicate things
without a huge benefit to the user. I do them anyway, because I
consider it important, but I'm never quite sure if the client
wouldn't be financially better off with a point-and-click solution
in the end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #42

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2o********************************@4ax.com:
On Fri, 23 Jul 2004 01:04:13 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
[]
Of course, as you say, this is all no problem when just deleting
because that's trappable, and you can handle it with whatever
fancy code you want. The problem is with low-level field type
rules (including things like deleting the contents of a required
field) and database rules that fire on update, not on delete.


Er, use the BeforeUpdate event for this. Unless Access deals
differently with non-Jet data sources than with Jet, it works
fine. I've been working with it this past week, fine tuning
validation code for an app.


That's the rub. Access check many conditions before calling
BeforeUpdate, so any attempt to check conditions and tell the user
why they are wrong is thwarted. Try, for instance, to delete the
contents of a requried field. . . .


Er, with a Jet back end, I have no trouble trapping it in the
BeforeUpdate and cancelling the update after providing a friendly
error message to the user. Here's the code for a control bound to
one of the two required fields on the main data entry form of the
project I've been working on this past week:

If IsNull(Me!cmbUnit) Then
MsgBox "You can't have a record without a Unit Number.", _
vbExclamation, "Value required"
Me!cmbUnit.Undo
Cancel = True
Else
Cancel = CheckForDups(Me!InputSheetID)
End If

That catches the error.

Is it different with a server back end?
. . . This is made doubly bad, because the
error is actually different dependign whether the back-end is ODBC
or not.
You're saying that with an ODBC data source, the validation against
field-level rules happens *before* the BeforeUpdate event?
Now, what about conditions that are only trapped by database
triggers? The error occurs only when the user saves the record,
and the only error info the code can get is the error number for
ODBC Call Failed from the Form_Error event.


And your complaint here is with what? Access?

If these have been ongoing problems with Access, why is it only an
issue for you right now?
...
I've never actually implemented it that way, myself (when I have
cascade delete turned off, it usually means there is never a
case when the parent record is allowed to be deleted, ever), but
I can't see it as being difficult.

It is a maintenance issue, though, and much more so when the
back-end is not yours, and the front-end may have to be
maintained later in the life cycle, possibly by someone else.
That's where having a simple way to eliminate static rules in
the front-end or auto-generate them pays off.


Is it possible to get to the relationships in your back end via
DAO? Or through some other method? If it is, then you can write
code that will do what you need. It would be complicated code,
code that I'd want help writing (and that I'm sure others in this
newsgroup would be happy to help with -- hint, hint), but it is
certainly discoverable in code, without needing to build any rules
into your user interface.


That's true, and it's true even with a server back-end, though
different for each server, but then what about the business rules
that are not expressible as simple relationships, those that are
implemented as triggers, or that should really be in a middle
layer? A different solution is called for, and the business rule
object layer is one of the most common and preferred models.
Access can't really deal with that at all, but code generation
could be used to generate event handlers, etc. that might be able
to solve the problem satisfactorily.


That's where I'd store metadata.

And it should be in the back end.

And it should be updated every time the data structure is changed.

*That* would be something that should be automated, I'd say, insofar
as it's possible.
> Actually, I did end up using a custom deletion routine, but
> the routine parsed the error text to see what table was being
> complained about, then looked up a friendly entity name from a
> table. Deletions, though, were only part of the problem.
> There were also database trigger errors on insert/update,
> etc., and these could be tied to tables I had bound continuous
> subforms connected to. Without trapping every attempted
> update, and implementing it in code, there was no way to get
> sufficient error information from code. Also, it turns out
> that with client/server apps, Access is writing to the field
> objects in the ODBC recordset before calling BeforeUpdate, so
> anything the database driver complains about, the user gets
> the ODBC error message. I think the Form_Error event can
> catch those, but it only gets the error number - the "ODBC
> Call Failed" error number. Useful.

Sounds like a problem in the ODBC driver, not with Access. Is
that your only option for access to this data?

No - it's not a problem with the ODBC driver. It's a problem
with the fact that Access doesn't give you access to ether the
error description or the ODBC Errors collection when these
errors occur outside of VBA code. It can be solved only by
making sure that field and record updates occur -only- from
within VBA code, and this can only be achieved using using awful
kludges with their own serious side effects when bound forms
(such as the handy continuous subform) are used. If all forms
are unbound, it's not a problem, but this defeats much of the
reason for using Access.


The BeforeUpdate event doesn't work with a server back end?


Yes, but it doesn't handle problems at the field update level.
Access sets the ODBC record field value before calling
BeforeUpdate, and there are several kinds of typo that can trigger
these errors before you ever get a chance to trap them.


Well, I didn't know that.

I assume that ODBC is your only option here (i.e., it's not SQL
Server, and there's no ADO driver)?

[]
> . . . but I'll still want to have thought of
> how the code will be maintained, preferably some way that it
> willb e hard to make a change and not know this code will be
> affected. That might involve, and sometimes does involve some
> sort of code generation.

I don't see it, myself. Of course, my schemas follow certain
rules relating PK names to table names (tblTable has PK
TableID), so I can pass a list of tables, the first being the
parent, the second being child tables, and generate code to
check for child records, generate message text for a
confirmation dialog, and then do the deletion of the child
records before the parent.

Nice. But, I see you agree that code generation is helpful for
making Access apps mroe supportable. If the code generation can
be re-run post-release, so much the better.


Code generation? I'm not talking about code generation at all. I'm
talking about generating metadata that can be processed by
pre-existing code.


You said "generate code". I thought you were referring to
automated code generation.


I don't see the phrase "generate code" in what you quote. I did mean
that, at runtime, error messages could be constructed from data
provided to the code at runtime, but I didn't say that you'd code
that into saved procedures -- I don't see any point in that.

I still don't.
I don't think code generation has any place whatsoever in any
project that I'm qualified to work on.


Possibly, but code generation is not necessarily as complex as you
might imagine.


I don't write code to propagate schema changes because the amount of
time it takes me to do that and test it is vastly larger than the
amount of time it takes me to visit all copies of the database and
manually alter the schema myself.

Code generation just means that you're generating code that itself
is subject to problems created by the code generating code. You've
just moved the testing issue back a step -- you still have to verify
that the result is valid. Just because your generation code appears
error-free does not mean that in all runtime environments it will
generate error-free code.

[]
Now, take forms. Have a small set of standard template forms,
and some code that can take a list of query names, and generate
forms like the template form, but with additional fields added
in a section marked with, say, a specially named rectangle.
Presto - consistent look and feel. Now, let's say, the
generator can also find which fields require high-level
validation and which function names to call to do that
high-level validation, and auto-generate the event calls from
the form.


I've done this by storing the rules in tables. Adding fields to
the data structure required adding metadata to the table storing
the rules for the fields (which included friendly names and labels
for the end user, as well as data types to determine validation
methods). Again, I'm a believer in storing the metadata and
having code that will run according to the metadata.


Some of that can be done dynamically. More of it can be done with
generation. Sometimes, the generation can actually be simpler.


I still don't see the advantage of writing code dynamically, as
opposed to altering the result of code based on data inputs.

[]

I can see your frustration with the BeforeUpdate thing with required
fields. But I don't see why it's such a huge issue for you *today*,
since it's surely something that has been a problem with Access and
ODBC datasources from the beginning?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #43

P: n/a
On Sat, 24 Jul 2004 00:03:58 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ol********************************@4ax.com :
OK, but the simplest procedure to make sure of those things is to
make sure that, to the greatest extent possible, some sort of
automated check process (such as a compile) will simply fail up
front when something is out of whack. Anything else, in my
opinion, is a kludge that should be fixed when you can think of a
clever way to do it, and that leaves fairly few things your
process outside of the code itself has to keep careful track of.
Your ideal of relying on compile-time validity checking strikes me
as wildly optimistic. Your whole wrapper thing for controls was
designed for that same purpose, and it struck me as ignoring all the
problems that come from run-time issues and actual data.


I don't want to "rely" on anything. On the other hand, I want to "use"
everything that can help, since I won't rely on anything. If a build step can
catch 50% of errors that would otherwise make it to testing, that's 50% better
than if I don't use it. To me, it's stupid not to value the feature, and
include that in weighing the value of a platform for develpment.

A also am well aware that a correctly compiled program with good static
checking will still have bugs, mostly related to unpredictable input, and
failure to have complete and accurate requirements. No, I don't think static
checking helps with most of these issues, however a system with static
checking combined with a careful software engineering process can get the
compiler to detect some failures of design logic.
In Access, you're never going to have it.
But code generation can solve many of the same problems. Heck, actually,
Access Analyzer solves some of the problems by doing some of the static
checking a compile step would do if there was one. Just like a C compiler
eliminates some issues before they ever become machine code, an automated
builder can eliminate some problems before they become Access applications.

Also, it appears clear to me that MS plans to have a real Access.NET
eventually. Whether they do it right by adding controls to WinForms that can
emulate all the capabilities Access has now is another question.
I don't know what environment you *will* ever have it in.
Now, I'm not sure we're talking about the same "what". Of course languages
have static checking. Most do. Access/VBA has less than most.
And I think it vastly overrates the importance of runtime
conditions. Indeed, I don't think it's possible to convert runtime
problems into compiler errors. That assumes that you can test with
absolutely every state and absolutely every variation of
combinations of data that will ever occur at any point when your
application runs. One can implement error handlers that will allow
you to recover from such errors, but I don't think you can ever
engineer them out by depending on the compiler.
I never claimed that static checking gave absolute certainty of code accuracy,
just that it eliminates a class of errors. Isn't that a good thing, just like
Option Explicit is better than not using Option Explicit? I think the
question "Why would you not want way more static checking?" is just as
significant as "Why would you ever not use Option Explicit?".
I think you're chasing a chimera on this one, Steve, and needlessly
complicating your coding as a result.


I respectfully disagree. It is true that I'm creating some solutions more
complicated than the original problems in my attempt to find solutions,
however, I am learning how to construct real solutions that are simple. Code
generation is a largely solved problem, it's just a matter of translating the
work others have applied elsewhere to VBA/Access.
Nov 13 '05 #44

P: n/a
On Sat, 24 Jul 2004 00:19:17 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ol********************************@4ax.com :
I can't imagine thinking that means a tool with way fewer means of
static validity checking, and lots of ways to make silently (until
rigorous testing - some of it by the final end user) bad code is
just as good as a tool with lots of mechanisms for making cleaner
code, less likely to have bugs, and not actually encouraging
application structures that are not automatically checked for
simple validity.
But will you give up all the productivity and ease-of-use that you
get with the RAD tool to get this level of safety?


No. I want my cake and eat it too. Actually, it would depend on the job.
For pretty much all of my jobs, the RAD capabilities of Access are a winner.
but that doesn't mean I don't curse every minute that I can't be using a
better engineered programming platform in the bargain. Anywhere I can find
reasonable ways to improve the engineering process -and- use Access, I'm going
to do it.
And will your client be saving money in the long run?
On a job for which Access was less suited than a less-RAD tool with better
code management, sure. I've had 2 jobs that would have qualified for that so
far. One I did badly in Access, and the other, I did less badly in ASP.
You seem to be seeking perfection.
Yup, I only expect to move continually toward it, though. I know there is no
pot of gold at the end of the rainbow.
Access (and Microsoft) has always been about producing something
that was "good enough," when not as close to perfection as possible.
That's why homegrown apps held together with chewing gum and baling
wire often serve perfectly well over extremely long periods of time
-- it's good enough.
But now, we (the global we) know much more about how to engineer reliable and
maintainable software. Other platforms have managed to incorporate that while
failing to provide what makes Access so cool. yeah, I'm a perfectionist, and
that bugs me.
And I often find that I doubt the actual ROI in some of my own
"bullet-proof" methods -- they sometimes seem to complicate things
without a huge benefit to the user. I do them anyway, because I
consider it important, but I'm never quite sure if the client
wouldn't be financially better off with a point-and-click solution
in the end.


I figure, to some extent, that's just breaking a few eggs. You try a
solution, and it doesn't have sufficient ROI, so you scrap it. next time,
perhaps, yuo come up with a better idea. You don't stop trying because it's
hard.
Nov 13 '05 #45

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2f********************************@4ax.com:
On Sat, 24 Jul 2004 00:03:58 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
[]
I never claimed that static checking gave absolute certainty of
code accuracy, just that it eliminates a class of errors. Isn't
that a good thing, just like Option Explicit is better than not
using Option Explicit? I think the question "Why would you not
want way more static checking?" is just as significant as "Why
would you ever not use Option Explicit?".


It depends entirely on the amount of work it takes to get the
benefit. Including Option Explicit takes less than a second. Writing
code to generate code takes longer than writing the final code by
hand and testing by hand. And it won't be any better code, in my
opinion. You may *feel* like it's better code, but it really won't
be -- you will have just moved the uncertainties of your code into a
different layer of the process.
I think you're chasing a chimera on this one, Steve, and
needlessly complicating your coding as a result.


I respectfully disagree. It is true that I'm creating some
solutions more complicated than the original problems in my
attempt to find solutions, however, I am learning how to construct
real solutions that are simple. Code generation is a largely
solved problem, it's just a matter of translating the work others
have applied elsewhere to VBA/Access.


Well, I don't think code generation is something I'll ever use, as I
just don't get the point. Leaving aside simple cases like automating
the adding of error handlers (which I wouldn't want in every
subroutine, anyway, so I don't know exactly how one would automate
making the choice), I can't think of instances where there is code
that needed to be drastically different for different similar
applications -- if I could think of such an application, code
generation would make sense.

But I'm not sure the amount of time it would take to write the code
that generates the code (including testing of the code generation
code) is going to be worth it. Indeed, I foresee that it would take
vastly longer to write and test the code generation code than it
would to write all instances created by hand and then thoroughly
test them.

I have an app that exists in three different versions with a small
set of choices as to how certain things are handled (i.e., slightly
different schemas). I've isolated all the differences out and know
how to adapt to any new instance of the application. Code generation
with a set of parameters to choose which methods sounds wonderful,
but the amount of time it would take me to write it is so great that
I'd never get any benefit from it.

I don't see how you get around that problem.

You seem to me to be getting to the point of the stereotypical
German mapmakers, who kept making maps of bigger scale, 100:1 and
then 50:1 and so on until the map became so big that they just
decided to use the country itself as its own map.

That's what it sounds to me like you're ending up with -- you reach
an absurd point where you've spent so much time writing the code to
generate the code that you can't actually produce an application.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #46

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ma********************************@4ax.com:
On Sat, 24 Jul 2004 00:19:17 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
[]
Access (and Microsoft) has always been about producing something
that was "good enough," when not as close to perfection as
possible. That's why homegrown apps held together with chewing gum
and baling wire often serve perfectly well over extremely long
periods of time -- it's good enough.


But now, we (the global we) know much more about how to engineer
reliable and maintainable software. . . .


I categorically dispute this statement.

Lots of people have done lots of studies about software engineering
and so forth.

But to me, it looks like just more buzz words.
. . . Other platforms have managed
to incorporate that while failing to provide what makes Access so
cool. yeah, I'm a perfectionist, and that bugs me.


Get used to it. Access.NET will likely kill a lot of the things that
make Access attractive, in favor of making it more .NET-like, the
same way so many things in A2K were hosed because of the
re-architecting of Access to make it more server database-oriented.

I am completely convinced that the people who make the decisions in
the marketing department at Microsoft really have no comprehension
whatsoever of what Access is about, and that's why it's been so
bollixed up in the last few years.
And I often find that I doubt the actual ROI in some of my own
"bullet-proof" methods -- they sometimes seem to complicate things
without a huge benefit to the user. I do them anyway, because I
consider it important, but I'm never quite sure if the client
wouldn't be financially better off with a point-and-click solution
in the end.


I figure, to some extent, that's just breaking a few eggs. You
try a solution, and it doesn't have sufficient ROI, so you scrap
it. next time, perhaps, yuo come up with a better idea. You
don't stop trying because it's hard.


Well, I'm a heretic -- I'm suspicious of overly normalized apps,
even.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #47

P: n/a
On Sat, 24 Jul 2004 15:33:22 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:2f********************************@4ax.com :
On Sat, 24 Jul 2004 00:03:58 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
[]
I never claimed that static checking gave absolute certainty of
code accuracy, just that it eliminates a class of errors. Isn't
that a good thing, just like Option Explicit is better than not
using Option Explicit? I think the question "Why would you not
want way more static checking?" is just as significant as "Why
would you ever not use Option Explicit?".


It depends entirely on the amount of work it takes to get the
benefit. Including Option Explicit takes less than a second. Writing
code to generate code takes longer than writing the final code by
hand and testing by hand. And it won't be any better code, in my
opinion. You may *feel* like it's better code, but it really won't
be -- you will have just moved the uncertainties of your code into a
different layer of the process.


Then perhaps, you haven't worked in an enviromnent that provided stronger
static checking nough to appreciate how helpful it can be. No, nothing solves
all problems, but each class of problems that can be categorically eliminated
increses confidence and saves lots of time down the road. It also adds
flexibility. If you know you can easily and reliably make a large change to
an application, you are more likely to do it and provide value to the customer
in the process rather than have to say it's cost prohibitive.
I think you're chasing a chimera on this one, Steve, and
needlessly complicating your coding as a result.


I respectfully disagree. It is true that I'm creating some
solutions more complicated than the original problems in my
attempt to find solutions, however, I am learning how to construct
real solutions that are simple. Code generation is a largely
solved problem, it's just a matter of translating the work others
have applied elsewhere to VBA/Access.


Well, I don't think code generation is something I'll ever use, as I
just don't get the point. Leaving aside simple cases like automating
the adding of error handlers (which I wouldn't want in every
subroutine, anyway, so I don't know exactly how one would automate
making the choice), I can't think of instances where there is code
that needed to be drastically different for different similar
applications -- if I could think of such an application, code
generation would make sense.


I'm not sure, but I think you still might be thinking that by "code"
generation, I just mean code text. I actually mean anything (though not
necessarily everything) that could include all or part of a form, report,
command bar, etc.
But I'm not sure the amount of time it would take to write the code
that generates the code (including testing of the code generation
code) is going to be worth it. Indeed, I foresee that it would take
vastly longer to write and test the code generation code than it
would to write all instances created by hand and then thoroughly
test them.
That's true and false, and depends entirely on the application. Our original
context was an application with over 100 each of forms and reports. At that
point, I start to consider that a generator will probably save more time than
it costs, particularly, the first time a categorical change to the UI is
requested.

As for testing, once you know that a sufficient sample of forms works, you
have confidence that the generator is doing its job properly. Since it's
automated, it's following the same rule everywhere. Possiblities for bugs?
Sure, but less so than doing the forms manually. Testing and debugging are
always required to some extent.
I have an app that exists in three different versions with a small
set of choices as to how certain things are handled (i.e., slightly
different schemas). I've isolated all the differences out and know
how to adapt to any new instance of the application. Code generation
with a set of parameters to choose which methods sounds wonderful,
but the amount of time it would take me to write it is so great that
I'd never get any benefit from it.
Perhaps, and perhaps not. How big is the application? How hard do you think
it would be to write the generation code? Is there a fairly simple automation
task that would help a lot, even if it would not replace all of the manual
labor? Once that tool was productively in use, might you not enhance it down
the road to make it save more manual labor?
I don't see how you get around that problem.
Well, you don't. That problem gets around itself when the application has
something about it that costs less to automate than to do manually, or when
you can borrow a tool from a previous app that had that justification. If
your app has nothing about it for which automation would add sufficient value,
the you don't need it, and I don't claim otherwise.
You seem to me to be getting to the point of the stereotypical
German mapmakers, who kept making maps of bigger scale, 100:1 and
then 50:1 and so on until the map became so big that they just
decided to use the country itself as its own map.
By suggesting that Access, without help, is not right for all jobs, I'm not
suggesting that it needs to be replaced or enhanced for most jobs. For most,
it definitely does not justify replacing or enhancing with automation. If,
however, someone brings me a job that sounds like it runs up against Access
inherent weaknesses, I'm most likely going choose to use another environment,
or use automation to make Access into a better environment for developing the
app.
That's what it sounds to me like you're ending up with -- you reach
an absurd point where you've spent so much time writing the code to
generate the code that you can't actually produce an application.


I'm suggesting no such absurdity. I'm suggesting that there are applications
for which the cost of doing the app in raw Access is too high, and doing the
app using another platform inspite of what it lacks, or using automation to
make up for what Access lacks is a clear net savings. Whether a given
developer ever needs or wants to deal with that case is another matter.

.... and yes, it would also be important to prevent creep in the generator
design, and do the simplest bit of automation that saves the most developer
time first, then see if more would be useful and cost effective to add.

Here's a hypothetical example. Let's say, you can generate a category of
forms automatically with pretty simple code, but customizing them
automatically is a much bigger problem. Great, 1/2 your work done on those
forms that do need the cusomization.

Now, 15% need customization. Which ones? Well, modify the generator, so it
can compare the existing forms with what it would generate (check if a control
exists rather than create, check its position rather than set, etc.) if you
ran it again, and give you a list of what what objects are different, and how,
then have it make backups of the customized forms. At this point, you can
safely regenerate, and re-copy the manual exceptions back to the 15% of
exceptional forms in the category. With the list of what things changed, you
can have a check-list of what needs to be done.

Of course, how many categories of forms to automate generation of is also a
pragmatic decision. 100 of one - 10 of the other? probably don't generate
the 10.

Too much work for most apps? Yeah, you bet. But if it took a week to write
the code for a generator (and it wouldn't necessarily take that long), and
your app has 100 forms or more? Now, add 5 more forms later in development,
etc.
Nov 13 '05 #48

P: n/a
On Sat, 24 Jul 2004 15:38:08 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:ma********************************@4ax.com :
On Sat, 24 Jul 2004 00:19:17 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
[]
Access (and Microsoft) has always been about producing something
that was "good enough," when not as close to perfection as
possible. That's why homegrown apps held together with chewing gum
and baling wire often serve perfectly well over extremely long
periods of time -- it's good enough.


But now, we (the global we) know much more about how to engineer
reliable and maintainable software. . . .


I categorically dispute this statement.


Then you're not paying attention (not a put-down, but I have been doing a lot
of catching up lately). The .NET platform has to be played with to see how
much nicer it is than older environments. It takes the recent engineering
improvements that have gone into Java and its IDEs, and adds the next logical
steps. That's so far beyond the norm when Access was designed, it's not even
close. Also, we know more about techniques for making more powerful code more
simply in terms of how to apply OOP, etc., more about how to have better
automated test coverage without taking more time to write the code, how to
make code more self-describing, more about how to eliminate duplication in
ways that saves the programmer time, and reduces coding errors.

Now, someone just needs to bring Access into the 21st century or develop
something new under the new platforms that can do what Access does.

....
. . . Other platforms have managed
to incorporate that while failing to provide what makes Access so
cool. yeah, I'm a perfectionist, and that bugs me.


Get used to it. Access.NET will likely kill a lot of the things that
make Access attractive, in favor of making it more .NET-like, the
same way so many things in A2K were hosed because of the
re-architecting of Access to make it more server database-oriented.


There is no certainty that it will be so. I see no reason a .NET
implementation of Access should not be better, not worse than the Access we
have now. You never know what MS marketing will do, but...
I am completely convinced that the people who make the decisions in
the marketing department at Microsoft really have no comprehension
whatsoever of what Access is about, and that's why it's been so
bollixed up in the last few years.


You may be right, but I think an Access rewritten from the bottom up will be
sufficiently better designed that the cost of responding to developer requests
may be much lower, and therefore we'll be more likely to get them ins spite of
lower marketting priority. They might do well in spite of themselves.
And I often find that I doubt the actual ROI in some of my own
"bullet-proof" methods -- they sometimes seem to complicate things
without a huge benefit to the user. I do them anyway, because I
consider it important, but I'm never quite sure if the client
wouldn't be financially better off with a point-and-click solution
in the end.


I figure, to some extent, that's just breaking a few eggs. You
try a solution, and it doesn't have sufficient ROI, so you scrap
it. next time, perhaps, you come up with a better idea. You
don't stop trying because it's hard.


Well, I'm a heretic -- I'm suspicious of overly normalized apps,
even.


Well, sure. Anything can be ovrdone.
Nov 13 '05 #49

P: n/a

After reading many of the posts regarding this subject and the needs of
the user, you can get most of what you are looking for by just using
Oracle and its Forms & Reports. You get a good, secure DB and a good
tool for building what you have already created in Access, both forms
and reports, which uses very good wizards to create your initial forms
and reports. You can port your information over, and you have a
cross-platform for scalability. Disadvantage is it will cost you some
money but far cheaper than say the $300,000 solution someone mentioned.
Also, I think the cost will be relative if you don't already own SQL
Server and Crystal Reports which was mentioned before as well. Another
disadvantage is that you will incur a learning curve to get up to speed
with Oracle. I found the installation pretty easy and getting up to
speed relatively painless. This is just an alternative suggestion if
you are willing and able to look at something aside from Microsoft.
Personally, I love SQL Server and prefer using it but I have incurred
the need sometimes for particular applications and reports and it was
just easier, cheaper and faster to buy, install, and develop it in
Oracle which had everything I needed in one package. Something to
consider...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #50

This discussion thread is closed

Replies have been disabled for this discussion.