A2007 ADPs

[Guy]

Hi All,



Just some general comments and the odd question about A2007 projects...
Currently I am upgrading an Access 2002/2003 ADP to A2007.



It is a large application that was originally upsized from an .MDB to an
..ADP in Access 2000 for scalability, reliability and performance reasons
when ADPs were new and all the rage.



Comments - The Good and not so Good:



1) The "Ribbon" - great concept now I can move all my command buttons that
perform functions like print a report for the current form record,
import/export data in a nice user friendly format from the Form Header to
the Ribbon. Well that's the theory anyway. Please see postings dated from
22/5/07 in Microsoft.Public.Access.Forms for more information. If you do
read these postings you will find an elegant solution for managing lots of
Ribbons developed by Albert Kalall MVP.



2) The new navigation bar... hmm Jury's still out on this one, with the
amount for objects I have there has always been some scrolling involved to
get to the object I wanted, particularly Query objects (1000's of them) it's
just worse now that Microsoft split out the views and stored procedures.
There goes my naming convention that kept like queries for a form or report
together, and as noted by Allen Browne MVP it doesn't remember where you
were last and for an ADP it doesn't appear to be customizable - not that I
would want to spend hours building custom navigation views for all the
objects that I have anyway.



3) Many of the new cool features in A2007 such data updated via html forms
in email, report highlighting and drill down don't appear to be supported in
an ADP.



4) Lots of A2007 application unresponsive errors developing on Windows
Vista.



5) Poor application startup and shutdown performance only partially remedied
by the hotfix released recently.



6) With A2002 and A2003 also installed on Windows Vista lots of reference
issues and Office re-installation delays. However, I believe most of these
have already been highlighted by Allen Browne MVP.



7) A potential bug looping through the reports collection to
programmatically, rather than manually, to update properties such as the
Ribbon name? (has always worked on XP)



Questions:



1) What is the future for ADPs? It appears to me that Microsoft hasn't
really concentrated on improving the ADP interface since inception in A2000.
Should my employer forget about upgrading the application to A2007 and look
at a .NET solution?



2) Should I look at "downsizing" the application to use linked tables, which
from my experience in versions of Access prior to 2000 had many performance
and reliability problems, but depending on whose commentary you read now
appears to be the preferred mechanism for talking to SQL Server? If yes what
about support for ADO code?



Your thoughts appreciated.



Regards,

Guy

 

[Vayse]

As far as I can tell, there is no clear road map for ADP. Some say it will
be cancelled, some say it will be supported for a long time to come. I think
it has been confirmed that no further development will be done on ADP
interface.

Several people seem to have slow response from ADPs in 2007. This is the
main reason why I haven't upgraded the ADPs in my office.
I haven't tried using linked SQL tables in Office 2007, so I can't comment
on it.

As regards Net, I'm also not so sure. Net 3.0 is to be released within the
next few months, and with it will come more to learn. Is your employer
prepared to spend time on the constant research required to get the most of
dot net? I enjoy using dot net, but the ever changing nature of it is time
consuming.
I know new versions of Office require changes as well, but at least you can
be sure your prior Access applications will work in the latest version.

Vayse
P.S. - From a developers point of view, it will look better if you have dot
net on your cv, so take that into consideration - just don't tell your boss!

 

[Guy]

Appreciate your thoughts especially the P.S. (and don't I know that only
too well)

 

[Guy]

Vayse,

The only performance problems I have experienced so far are during
application startup and shutdown (as in SLOW) even with the hotfix.

Once the application is up and running it appears to work as in prior
versions of Access, although, I have yet to do exhastive testing yet because
I am still in the process of migrating and adding those A2007 features
supported by an ADP.

Having said this... from my current experience why upgrade, other than at
some stage we have to (or rewrite the application in another language) when
many of the cool new features in A2007 don't appear to be supported for ADPs
and the Ribbon (like the idea) but I've said enough on this already in other
Access postings.

Cheers
Guy

 

[Guy]

It would be nice if Microsoft came to the party and indicated their
strategy/future for ADPs.

Potentially it may save a lot a companies from spending good money in the
wrong direction.

Guy

 

[Guy]

Vayse,

In my opinion, which frankly doesn't really count for much, the facts are:

1) Microsoft have introduced a new database format . accdb, does this mean
they are now going to support, or are prepared to support, 3 different
formats all with slightly different features?

2) The ADP interface does not appear to have been significantly improved
since inception.

3) Many of the new features available in A2007 don't appear to be supported
in ADPs.

4) Commentary suggestes that linked tables are now the preferred option to
talking to non .MDB databases.

Can only mean one thing the end of the ADP format, and unless I hear
anything to contary this is what I will be advising my employer.

Thankfully I am currently working towards .NET MCPD (Web Developer)
certification paid for by the company, although, having said that the amount
of (almost) irrelavant information you have to learn about classes you will
probably/may never use for your 70-528 exam is astounding, when I would
suggest that a large majority of the worlds computer applications just talk
to directly/indirectly to an RDBMS database via a form (web based or
otherwise) and most are not writing the next replacement for Excel. Still,
no different from Computer Science at University I suppose (and not for this
newsgroup forum).

Good luck.

Guy

 

[David W. Fenton]

1) Microsoft have introduced a new database format . accdb, does
this mean they are now going to support, or are prepared to
support, 3 different formats all with slightly different features?

Er, what are you talking about? ACCDB is a new version of Jet. It
could have been called Jet 5 and used the MDB format, but the Access
team probably wants to shed the undeserved bad reputation of Jet
(since it's ignorant people that think Jet is bad, this silly name
change may fool them).

An MDB is a Jet database.

An ACCDB is a Jet database.

Both can be used for storing the objects for an application that
connects to any back end.

So, in one sense, it's not really a third format, it's just a
variation of the old Jet format.

On the other hand, if you consider that all versions of Access back
to A2K are supported, you have *four* different formats for
MDB/ACCDB.

But I don't think that way of looking at it makes much sense.

2) The ADP interface does not appear to have been significantly
improved since inception.

Bugs have been fixed and regressed in various incarnations of the
ADP, according to reports from those who were trying desperately to
make use of ADPs.

3) Many of the new features available in A2007 don't appear to be
supported in ADPs.

Just as they aren't supported in MDBs, because they are
ACCDB-specific features. That is, things like multi-value fields are
a db engine feature, not a front-end feature.

4) Commentary suggestes that linked tables are now the preferred
option to talking to non .MDB databases.

That is what MS is saying, and their reason is because there are
just as many inefficient layers between an ADP and SQL Server as
between an MDB and SQL Server.

Can only mean one thing the end of the ADP format, and unless I
hear anything to contary this is what I will be advising my
employer.

I would say that if you have existing ADP apps, keep maintaining
them as ADPs, but do all development as ACCDB/MDB to ODBC.

Thankfully I am currently working towards .NET MCPD (Web
Developer) certification paid for by the company, although, having
said that the amount of (almost) irrelavant information you have
to learn about classes you will probably/may never use for your
70-528 exam is astounding, when I would suggest that a large
majority of the worlds computer applications just talk to
directly/indirectly to an RDBMS database via a form (web based or
otherwise) and most are not writing the next replacement for
Excel. Still, no different from Computer Science at University I
suppose (and not for this newsgroup forum).

The whole point of .NET, seems to me, is stateless data editing,
which is not really what Access apps are about, so I just don't see
how .NET can ever fit into the niche filled by Access apps.

 

[Guy]

Dave,

Thanks for your input and clearing up my format misconceptions, although, I
really meant functional format variances.

Agree, any new development I do will probably not be using the ADP format.

Cheers
From a grumpy old software developer or as a fellow newsgroup poster on
comp.object says...
"There is nothing wrong with me that a cup of Draino wouldn't fix"

 

[Sylvain Lafontaine]

They have created a new format (ACCDB) in order to make sure that the older
format .MDB will never change again. This way, this will give the
possibility to use JET for small databases without having Access/Office or
without beeing concerned with the version of Access/Office installed on a
system.

For the new format ACCDB, the tight integration between this format and the
version of Access/Office installed on a system will permit adding new
fonctionalities directly into the ACCDB format with each new version of
Access/Office without disturbing other applications that will use JET/MDB.

 

[Sylvain Lafontaine]

You must spend a lot of time studying .NET but either you like or not, you
will have no choice in the future to do so because VB6 is no longer sold,
C++ with MFC is one hundred times more complicated and there is only so much
you can do with Access/VBA.

This is the problem with the actual GUI of Access: great for simple things
like textboxes, comboboxes and listboxes byt everytime you want to add a
small piece of functionality that is a little outside of these bounds -
however small it is - you are litterally blocked by Access.

The simple task of simply adding a image field to a continuous forms will
litterally make Access drop to the ground like a rock. You want to add an
unbound control - like a checkbox - to a continuous form? Can't do. You
want to have a continuous subform on a continuous form? Can't do. You want
to have a selection (multiples rows for example) and keep it when the user
click on something else? Can't do. Rows of different colors? Can't do.
Want to add some kind of diagram/schema, even a very simple one? Can't do.

Some of these previous can't do can be solved by adding/buying an ActiveX
control or adding some very complicated code with calls to the API32 of
Windows; however, the performance of using any of these controls will
usually make Access looks like a turtle (when was the last time that you
have used a commercial grid control with VB6 or Access without eating your
keyboard after a few days of work?) or will push the simplicity of Access
back to the prehistory.

Five years ago, I've been fired by a company (who had just hired me for this
occasion) because they wanted to associate engineering diagrams with a
database. I told them it was practically impossible to do this with either
VB6 or Access and that we'll have to use C++. Later, I learned that the
project got cancelled after one year and all the money put into it was gone
into the wind. Today, I would told them to use C# or VB.NET instead and to
my current understanding, such a project should be perfectly doable with
..NET.

ADP has been cancelled because it offers no real advantage over MDB file but
this conclusion is not based on performance but on functionality. The
capabilities of ADP are so closely related to MDB that if you cannot do it
with MDB, chances are high that you cannot do it with ADP either. When you
take into account the vast amount of money invested by MS into the .NET
technologies, the slight gain of speed or the easier communications with SP
that you get using ADP is not sufficient to justify the continuous supports
by MS of three different methods to work against a SQL-Server database. The
ratio costs/benefices is simply just not there to justify the presence of an
intermediate solution between the two others.

MDB/ACCDB files with ODBC linked tables will be kept for the design of
interfaces that will use nothing more than text boxes, combo boxes et lis
tboxes, a few tabed forms and maybe a continous subforms if the main form is
not continuous but for anything more complicated than that or for any
advanced database operations (like transactions or mobility); you will have
to use .NET. This is the new credo of MS: no more a complicated stack of
multiples overlapping and non-homogeneous (and sometimes contradictory)
options, each with their own syntaxe and idiosyncrasies; only two will
remain: simple tasks with Access/MDB or ACCDB and more complicated tasks
with .NET. (Of course, if you want to, you can also code simple tasks with
..NET.).

In order to justify the buying of Access, MS is also giving its users some
candies: a pre-coded solution for mail forms and a better integration with
Sharepoint; along with a few others things that I forgot. Of course, you
can also do all of this with .NET but you have to do it yourself.

 

[Vayse]

Good points there Sylvain, makes the whole ACCDB thing a bit clearer.

They have created a new format (ACCDB) in order to make sure that the
older format .MDB will never change again. This way, this will give the
possibility to use JET for small databases without having Access/Office or
without beeing concerned with the version of Access/Office installed on a
system.

For the new format ACCDB, the tight integration between this format and
the version of Access/Office installed on a system will permit adding new
fonctionalities directly into the ACCDB format with each new version of
Access/Office without disturbing other applications that will use JET/MDB.

 

[Yvonne Anderson]

What is this group, and why are you all appearing on my computer?

Good points there Sylvain, makes the whole ACCDB thing a bit clearer.

 

[Yvonne Anderson]

What is this group, and why are you all appearing on my computer?

Yvonne Michele Anderson

Good points there Sylvain, makes the whole ACCDB thing a bit clearer.

 

[Sylvain Lafontaine]

I should have added that while the format for the storing of forms/modules
and macros has changed in the previous versions of JET, the format for the
storage of data has not changed; excerpt when going from the 3.5 to the 4
version because of the migration to Unicode.

It is my understanding (but I never verified this point personally) that the
format of data has changed between MDB and ACCDB (and that further
changements might likely occur in the future for ACCDB). While I never
checked it personally, I think that while an older version of Access cannot
open the forms and modules of a more recent version of MDB, it still can
link to it, even by using an older version of the ODBC provider (albeit it's
strongly suggested to use the latest version avalaible for bug/performance
purposes). This is something you can't do with the new format: the older
versions of JET cannot open the new ACCDB format, even if it's only for
linking to the tables.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Good points there Sylvain, makes the whole ACCDB thing a bit clearer.

 

[Bob]

It's very discouraging hearing all the negatives associated with .adp files.
I built a number of them, all being currently used without problems across a
wan.
I'm currently working on two more targeted for end of year deployment.

Using ADO with stored procedures give the speed to the app. I would not
look to
using ODBC & linked tables. The whole point of using SQL is to allow the
work to
be done at the server, returning only the resultset of data.

I'm pasting an example from the vba side of an .adp.
I'm curious if anyone else is not happy with performance from the .adp
when using it like this. Your input is much appreciated.

Thanks in advance,
bob.


Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "ORDER_AddNew"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 50 ' 3 bytes
param.Direction = adParamInput
param.Value = Me.tbCalledInBy
param.Name = "cib" 'Called In By
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 25 ' 3 bytes
param.Direction = adParamInput
param.Value = Me.tbCalledInByPhone
param.Name = "p" 'Called In By Phone
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 50 ' 3 bytes
param.Direction = adParamInput
param.Value = Me.tbOrderTakenBy
param.Name = "tb" 'Order Taken By
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamOutput
param.Name = "oid" 'New OrderID (Scope_Identity)
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords

Me.tbOrderID = oCmd.Parameters("oid").Value

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

You
want to have a continuous subform on a continuous form? Can't do.

Yes you can, by using a standard form and embedding a continuous
form in it. When you view it as a datasheet, the continuous form
will be displayed as the subdatasheet.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

I should have added that while the format for the storing of
forms/modules and macros has changed in the previous versions of
JET, the format for the storage of data has not changed; excerpt
when going from the 3.5 to the 4 version because of the migration
to Unicode.

That is simply not true. There were major changes besides Unicode in
Jet 4, such as the addition of decimal and byte datatypes, and
extensive changes to the replication subsystems.

It is my understanding (but I never verified this point
personally) that the format of data has changed between MDB and
ACCDB (and that further changements might likely occur in the
future for ACCDB).

ACCDB is Jet 5 by another name. I think MS is trying to escape the
undeserved "bad reputation" of Jet. The reality is that there are
now two groups at MS maintaining two forks of the Jet database
engine. The first is part of the Windows group, and is maintaining
Jet 4 for its role as data store for Active Directory. Someday when
there's a database-driven file system, Jet 4 will be retired for
that purpose (WinFS would have been that point, but WinFS is
probably never going to happen), but for now, Jet 4 is being
maintained as a stable platform which will get bug fixes and
security updates precisely because of its important role as a
component of Windows itself.

In planning for Access 2007, the Access development team forked the
Jet 4 codebase into a new version of Jet that will be maintained by
the Access development team and the future of which will be shaped
by the needs of Access, not by the needs of Windows. This new
version of Jet is called ACE and it's replacement for the MDB is the
ACCDB. However, the ACE is fully backward compatible with all
previous versions of Jet.

While I never
checked it personally, I think that while an older version of
Access cannot open the forms and modules of a more recent version
of MDB,

Don't confuse Access with Jet -- a Jet 4 MDB can store Access 2000,
Access 2002, Access 2003 or Access 2007 objects, but it can only
store Jet 4 data (though perhaps with some field properties specific
to each version).

it still can
link to it,

Jet 4 can read Jet 4 (and all previous versions of Jet) data tables.

Access can only work with the Access objects (forms/reports/etc.)
for its own version or older.

even by using an older version of the ODBC provider (albeit it's
strongly suggested to use the latest version avalaible for
bug/performance purposes).

An Access MDB or ACCDB cannot use ODBC to access any Jet data.

This is something you can't do with the new format: the older
versions of JET cannot open the new ACCDB format, even if it's
only for linking to the tables.

But you still have the MDB format for cross-version compatibility.

I suggest you read this blog to familiarize yourself with the
situation in Access 2007:

http://blogs.msdn.com/access/

Also of interest:

http://blogs.msdn.com/clintcovington

 

[Robert Morley]

Of course, the thing to keep in mind here is that we've heard companies make
promises of full forwards and backwards compatibility in the past, or that
something would be THE way to do things from now on, and they've only rarely
ever actually stayed around for more than a few years. Some new and better
thing entered someone's head that was incompatible with the
supposedly-catch-all technology, and it had to be changed.

Certainly with some of their Office products (Word comes to mind), Microsoft
has done an excellent job of bi-directional compatibility, but I'm more than
a little sceptical of any such claims until they've been compatible for
10-15 years or so.


Rob

They have created a new format (ACCDB) in order to make sure that the
older format .MDB will never change again. This way, this will give the
possibility to use JET for small databases without having Access/Office or
without beeing concerned with the version of Access/Office installed on a
system.

For the new format ACCDB, the tight integration between this format and
the version of Access/Office installed on a system will permit adding new
fonctionalities directly into the ACCDB format with each new version of
Access/Office without disturbing other applications that will use JET/MDB.

 

[Robert Morley]

I'm curious, can you do continuous forms AT ALL in .NET? That, and
sub-forms, have always been one of my chief reasons not to even consider a
move to .NET (next to the speed issue).

And by "continuous forms", I don't mean "data grid"...I mean true,
continuous forms, where you've got all the same functionality. For example,
having a multi-line text box, with command buttons, check-boxes, images,
etc.



Rob

 

[Jim Rand]

Hi Rob,

Sub forms are really easy to do in .NET. I have not found a way to do
continuous forms which I do miss in .NET.

The closest approximation I've got so far is a split panel. The left
contains a "navigator" grid and the right contains a single row form. With
the navigator, the user can quickly get an overview of the set of rows
available.

An alternative is the Developer Express card view which provides a form view
containing rows of label / controls pairs. See
http://www.devexpress.com/Products/NET/WinForms/XtraGrid/build.xml

Jim