Opinion on Access 2007

[Rick Brandt]

Does that mean it will never happen? Perhaps 4k was too much to ask
for, but certainly more than 255?

I have used memo fields sparingly on reasonable-sounding advice, but
the text(255) fields are just too small.

I suspect that the reason that they don't provide larger text sizes is because
the increase in file size should a user index them might not be practical
whereas the server databases don't have that issue. I believe our IBM box allows
VarChar all the way up to 32000 now which seems a bit extreme to me.

 

[Darryl Kerkeslager]

Thanks, David. I don't think I had seen that put that way before.

 

[f_l_louw]

Hi Allen,

We have an A2000 ADP app that runs in our branch offices in 100+
countries. A2000 was a great technology for us, but it is time to move
on. I was really looking forward to A2007, and was glad to see that
ADP continues to be supported.

Then the bad news. The first time I opened a form bound to a simple
stored proc like:

CREATE PROCEDURE dbo.spselTableName
AS
SET NOCOUNT ON
SELECT * FROM dbo.TableName

Where TableName is a simple reference table with less than 10 fields
and a dozen records, It took more than a minute to open! I put a trace
on to see what was happening, and I noticed loads of code like this:

select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences
srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where
srfk.constid =
sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and
user_name(sofk.uid) = N'dbo' and object_name(sofk.id) =
N'FK_ForeignKeyTableName_TableName_PrimaryKeyFieldName'

It looks like A2007 is interpreting the foreign key relationships in
the SQL database. In our SQL 2000 database (1400 tables, about 3000
relationships) each of these calls generates about 100,000 reads. That
is really, really nasty. It is even worse in SQL 2005. A2000 never
did this, so the same form is lightning fast in A2000, but dead slow in
A2007.

What happened?? IMHO, this problem kills ADP as a usable technology
for corporate development. The most attractive upgrade path for us
would have been A2007, but now we are looking at a complete re-write of
our app. Not so nice.

Any suggestions?

Thanks,
Francois Louw

 

[Lyle Fairfield]

Hi Allen,

We have an A2000 ADP app that runs in our branch offices in 100+
countries. A2000 was a great technology for us, but it is time to move
on. I was really looking forward to A2007, and was glad to see that
ADP continues to be supported.

Then the bad news. The first time I opened a form bound to a simple
stored proc like:

CREATE PROCEDURE dbo.spselTableName
AS
SET NOCOUNT ON
SELECT * FROM dbo.TableName

Where TableName is a simple reference table with less than 10 fields
and a dozen records, It took more than a minute to open! I put a trace
on to see what was happening, and I noticed loads of code like this:

select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences
srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where
srfk.constid =
sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and
user_name(sofk.uid) = N'dbo' and object_name(sofk.id) =
N'FK_ForeignKeyTableName_TableName_PrimaryKeyFieldName'

It looks like A2007 is interpreting the foreign key relationships in
the SQL database. In our SQL 2000 database (1400 tables, about 3000
relationships) each of these calls generates about 100,000 reads. That
is really, really nasty. It is even worse in SQL 2005. A2000 never
did this, so the same form is lightning fast in A2000, but dead slow in
A2007.

What happened?? IMHO, this problem kills ADP as a usable technology
for corporate development. The most attractive upgrade path for us
would have been A2007, but now we are looking at a complete re-write of
our app. Not so nice.

Any suggestions?

Thanks,
Francois Louw

Perhaps we should all read and explore a bit more and report our
findings.

I too have found my ADPs so slow as to be useless in Access 2007.

I haven't tested this solution enough to venture an opinion as to why
or how, but I have found that a newly created (in 2007) ADP operates
just as quickly in 2007 as other ADPs do in 2003. When I import forms
and reports from the old 2003 created ADP to the new 2007 created ADP
they work just as quickly as before.

This has worked for two ADPs here, one connected to a local SQLExpress
Server, and one connected to a remote SQL-2000 server.

 

[Allen Browne]

Perhaps someone who uses ADPs can comment on this.
Thanks for posting.

 

[ManningFan]

Or you can get the full version of Office 2007 for free from your
favorite torrent site. ;o)

 

[aaron.kempf]

yeah how ****ing DARE you spread misinformation on ADP?

we should have _HELPED_ the JAPS invade your wimpy-ass 'country' in WW2


-Aaron

 

[Larry Linson]

yeah how ****ing DARE you spread mis-
information on ADP?

Among the rankings of those who have "spread misinformation" here, you are
"right up there," aaron. If anyone took your ranting seriously, you'd easily
fall in the category of "disruptive poster." (Another name for "disruptive
poster" would be "troll".)

we should have _HELPED_ the JAPS
invade your wimpy-ass 'country' in WW2

Keep trying, as there are probably other nationalities you haven't publicly
insulted yet -- but with your vast talent in the area of insult, I'm sure
you'll get around to covering them all.

Larry

 

[Francois Louw]

I checked out your suggestion, and creating a new ADP in 2007 and
importing all objects rather than opening one created in 2003 or 2000
definitely improves performance. However, the trace still reveals the
selects against sysobjects. One commonly used reference table in one
of our databases has 38 FK relationships. Each one generates a select
against sysobjects, 98,000 reads each time. That makes 3,724,000 reads
just to open the form.

Ok, the form is not opened very much, being reference data that isn't
acessed very often, but 3.7 million reads is still nasty.

 

[Francois Louw]

asinine and ignorant. what a delightful combination.

 

[Lyle Fairfield]

I checked out your suggestion, and creating a new ADP in 2007 and
importing all objects rather than opening one created in 2003 or 2000
definitely improves performance. However, the trace still reveals the
selects against sysobjects. One commonly used reference table in one
of our databases has 38 FK relationships. Each one generates a select
against sysobjects, 98,000 reads each time. That makes 3,724,000 reads
just to open the form.

Ok, the form is not opened very much, being reference data that isn't
accessed very often, but 3.7 million reads is still nasty.

I tried responding on Google a few minutes ago but the posting seemed to
fail. I apologize if this is a duplicate (and even more if it appears to be
but is not.)
I know very little of tracing.
There seems to be no easily available utility accompanying SQLExpress
(2005) for reading trace files.
SQLExpress maintains a default trace unless it is turned off.
I wrote a couple of Sprocs, one to read the location of the default trace
file and another to return its contents in table form.
I fooled with the ADP for two hours with that task and opening various
forms and views as a means of testing.
My trace table has 466 rows. The time-date information covers the time I
was working with the ADP. It seems to have a row for each action I took.
There was no evidence of scanning the SysObjects table.

Perhaps we are not talking about the same thing?

Perhaps we did not create our new ADP (in Access 2007) in the same way. I'm
embarrassed to tell how I do it, as it seems "Hackish". Perhaps you have
discovered the easy, approved way and will tell us?

 

[Lyle Fairfield]

yeah how ****ing DARE you spread misinformation on ADP?

we should have _HELPED_ the JAPS invade your wimpy-ass 'country' in WW2

Why spoil your helpful contributions with this nonsense, Aaron? You
have plenty of knowledge; you can be very helpful; why cover this with
the darkness of anger?

Yes, I know; for a long time you pointed out the strengths of ADPs and
the errors of those who denigrated them. You were ignored or derided.
Your statements were twisted.

After years of that many would feel like lashing out.

But is that effective? Does it work? Does it result in a greater or
lesser likelihood that your position will be considerd?

I think ADPs are fabulous. Once you get into them, you forget MDBs. But
I've rejected them because of my failure to find any sound way of
controlling, beyond the scope of the ADP, the permssions which their
users must have. Recently I've come to believe, (but I'm not 100% sure)
that MS-SQL/ODBC connected Access mdbs have the same potential for
misuse of permissions. If that's true then I'm back on the ADP
bandwagon.

 

[Tim Marshall]

yeah how ****ing DARE you spread misinformation on ADP?

we should have _HELPED_ the JAPS invade your wimpy-ass 'country' in WW2

I didn't see any smileys.... so...

You are an ignoramus of the highest degree. The Aussies were dying
fighting "the JAPS" (as you call them) up to a year after the Americans
declared victory in 1945. Of course, you knew that. Boy, if the whole
world was as knowledgeable as you are, what a place it would be... You
must be an advisor to the current American president.

Regardless of where Allen is posting from, to attack someone who has
given so much to the Access development community requires a public apology.

 

[Francois Louw]

My database is in SQL 2000, and I used the SQL 2000 Profiler utility,
checking for TSQL events of type SQL:StmtCompleted, with a filter for
my mahine name as HostName. After starting the trace, I then just open
the form, and sit back to watch the fireworks. The profiler shows the
TSQL commands executed by Access, along with the number of database
reads generated by each one, and some other statistcal information.

I use Profiler extensively to see what IO Access is generating on the
SQL server. Access 2000 ASPs are remarkably well behaved, and generate
very clean, pretty efficient TSQL code. I just wish Access 2007 would
do the same.

Btw, SQL 2005 has the same utility in the managment studio. I'm not
sure about the express version though.

The way I created my ADP in Access 2007 is: Office Button. New. Browse
for the location of the database (hit the folder icon). Change the
"Save as type" to ADP.

Thanks,
Francois Louw