ADP vs. MDB: Speed

[Neil]

I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.

I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.

In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.

Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.

Thanks,

Neil

SQL for view in question:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL for vwInventory_Dupes, used as subquery:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[david epsom dot com dot au]

Access buffers "gets", so as to not lock up Windows.

If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.

If this is causing problems, you might be interested
in playing around with different methods of accessing
the linked view.

For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)

"Set rs = application.codedb.openrecordset" won't
give you a visible data sheet, but it should run faster,
and you have a number of options for what kind of
recordset you want. Obviously, it won't solve your
problem, but it might be interesting.

Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.

Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.
BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:Mt****************@newsread3.news.pas.earthli nk.net...

Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then
refreshed the original view's link, the original view's link lost its
virtual primary key. (Don't know why, but that's what happened. And I was
able to reproduce this phenomenon.) And, apparently, not having the
virtual primary key made the linked view scroll faster.

To test this I created two links to the same view: one with, and one
without virtual primary key. The one without the virtual primary key
scrolled fast, just like the ADP file; the one with the virtual primary
key was slow.

I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.

When opening the view in the ADP file, profiler shows the following:

SELECT * FROM "vwWebMaintDuplicates"

However, when opening the linked view with the virtual primary key in
place from the MDB file, I get the following:

SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"

sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6
int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attS igned","SignedCond","YRPUB","PRICE","Web","Status" ,"WebStatusPending","ActivateDate","DeactivateDate ","WebAddedBatchID","AllowDuplicate","WebAction"," WebActionPending","DateModified","DateWebActionApp lied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1
select @P1

sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749

SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSi gned","SignedCond","YRPUB","PRICE","Web","Status", "WebStatusPending","ActivateDate","DeactivateDate" ,"WebAddedBatchID","AllowDuplicate","WebAction","W ebActionPending","DateModified","DateWebActionAppl ied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2 OR
"Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10

"Index" is the name of the primary key field in the main table in the
view, and it's the field that's used as the virtual primary key. The
numbers 1364, 1971, etc., above, are the Index values for the first
records that are returned.

Thus, the MDB linked view with virtual primary key first gets a list of pk
values, then grabs the records ten at a time, specifying the pk values to
get.

This would explain why it's slow in scrolling -- it only gets the records
in sets of ten and has to keep grabbing them.

When I open the linked view in the MDB without the virtual primary key,
profiler shows:

SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"

This is essentially the same as what's going on in the ADP file, except
that it's specifying a field list, instead of "*".

Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual primary
key, though, it's getting the list of pk's, and then grabbing records ten
at a time.

This appears to be an inherent property of the way the MDB file/ODBC
driver handles virtual primary keys, and doesn't appear to be anything I
can change. So the question still remains: why is it going slowly when it
used to not, and what can I do about it? Is there anyway to tell it to get
all of it's "sets of ten" at once, when the view is opened, rather than
waiting until the view is scrolled? Are there any other workarounds or
settings that can be tried here?

Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the first
place).

Thanks!

Neil

"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net...

Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.

First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would scroll
very slowly; but in the ADP file, it was zippidy fast.

I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.

Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!

Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.

Though I had tried refreshing the original link before encrypting the
view copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.

In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.

I don't understand what happened here, but it clearly must be some sort
of compile/optimization issue which kicked in when I encrypted that copy
of the view.

So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?

Thanks everyone for your assistance.

Neil


"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...

I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open the
view in an ADP file, it scrolls quickly.

I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.

In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and is
very disconcerting re. using an MDB file.

Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.

Thanks,

Neil

SQL for view in question:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL for vwInventory_Dupes, used as subquery:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[jimfortune]

Albert D. Kallal wrote:

...

Thanks for your insights. I used to think that each record was
indexed. I'll keep Access' scheme in the back of my mind. Hmm.. what
if I had a text field called 'Bank' filled with junk text? When
getting ready to add other data via code I could 'go to the Bank' and
throw away some junk. Would that prevent page splits from happening?
Just before compacting the db I could refill the Bank to make room for
more new data. BTW, I'm not seriously considering this idea.

James A. Fortune

[Neil]

> If you open your linked view in the database window.

you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.
In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. Yet
the form is hideously slow in scrolling. Even displaying the initial screen
of about 20 takes forever (about 5 seconds :-) ).

All this could be attributed to the MDB blues. However, as noted, this form
has been in place for a long time without any slowness. All of a sudden, it
started acting this way, and I can't track it down.
For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)
Not sure what you mean by "clicking in the database window." In my case, the
form is opened or the user specifies an option in the form and the recordset
is reset. A dynamic SQL statement is built, and the form's recordsource is
set to the SQL.

I did try opening a DAO recordset and setting the form's Recordset property
instead of using the Recordsource, but the results were the same.
Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.
All locking in the form is off.

Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.
Well, that would be simpler, of course. But there are times when you need
continuous form view. And, as noted, it's been working fine until recently.

BTW, since you mention those who advocate MDB over ADP, does that mean that
you're an advocate of ADP?
BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.
I haven't seen any places to turn this on on an application level. And since
this is happening globally (both on the client's LAN, as well as on my local
PC), I can't see all of the machines having that turned on. Or is there a
global setting for the application itself, rather than the PC?

Thanks,

Neil

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:Mt****************@newsread3.news.pas.earthli nk.net...

Well, I was premature in stating that this had been resolved. While
encrypting the view copy *did* in fact increase performance, there was a
price to pay. Apparently, when I encrypted the view copy and then
refreshed the original view's link, the original view's link lost its
virtual primary key. (Don't know why, but that's what happened. And I was
able to reproduce this phenomenon.) And, apparently, not having the
virtual primary key made the linked view scroll faster.

To test this I created two links to the same view: one with, and one
without virtual primary key. The one without the virtual primary key
scrolled fast, just like the ADP file; the one with the virtual primary
key was slow.

I ran a trace on these two and on the ADP file when opening and scrolling
the view, and I think I see what's going on here.

When opening the view in the ADP file, profiler shows the following:

SELECT * FROM "vwWebMaintDuplicates"

However, when opening the linked view with the virtual primary key in
place from the MDB file, I get the following:

SELECT "dbo"."vwWebMaintDuplicates"."Index" FROM
"dbo"."vwWebMaintDuplicates"

sp_prepare @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6
int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT
"Index","TITLE","AUTHILL1","attFirstEdition","attS igned","SignedCond","YRPUB","PRICE","Web","Status" ,"WebStatusPending","ActivateDate","DeactivateDate ","WebAddedBatchID","AllowDuplicate","WebAction"," WebActionPending","DateModified","DateWebActionApp lied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2
OR "Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10', 1
select @P1

sp_execute 4, 1364, 1971, 1978, 2303, 3140, 3480, 3605, 4962, 6747, 6749

SELECT
Index","TITLE","AUTHILL1","attFirstEdition","attSi gned","SignedCond","YRPUB","PRICE","Web","Status", "WebStatusPending","ActivateDate","DeactivateDate" ,"WebAddedBatchID","AllowDuplicate","WebAction","W ebActionPending","DateModified","DateWebActionAppl ied","JIT","MImage","HImage","AdCode","OnWeb"
FROM "dbo"."vwWebMaintDuplicates" WHERE "Index" = @P1 OR "Index" = @P2
OR "Index" = @P3 OR "Index" = @P4 OR "Index" = @P5 OR "Index" = @P6 OR
"Index" = @P7 OR "Index" = @P8 OR "Index" = @P9 OR "Index" = @P10

"Index" is the name of the primary key field in the main table in the
view, and it's the field that's used as the virtual primary key. The
numbers 1364, 1971, etc., above, are the Index values for the first
records that are returned.

Thus, the MDB linked view with virtual primary key first gets a list of
pk values, then grabs the records ten at a time, specifying the pk values
to get.

This would explain why it's slow in scrolling -- it only gets the records
in sets of ten and has to keep grabbing them.

When I open the linked view in the MDB without the virtual primary key,
profiler shows:

SELECT "Index" ,"TITLE" ,"AUTHILL1" ,"attFirstEdition" ,"attSigned"
,"SignedCond" ,"YRPUB" ,"PRICE" ,"Web" ,"Status" ,"WebStatusPending"
,"ActivateDate" ,"DeactivateDate" ,"WebAddedBatchID" ,"AllowDuplicate"
,"WebAction" ,"WebActionPending" ,"DateModified" ,"DateWebActionApplied"
,"JIT" ,"MImage" ,"HImage" ,"AdCode" ,"OnWeb" FROM
"dbo"."vwWebMaintDuplicates"

This is essentially the same as what's going on in the ADP file, except
that it's specifying a field list, instead of "*".

Thus, without the virtual primary key, the MDB link is fast, like the ADP
file, because it's just getting a set of records. With the virtual
primary key, though, it's getting the list of pk's, and then grabbing
records ten at a time.

This appears to be an inherent property of the way the MDB file/ODBC
driver handles virtual primary keys, and doesn't appear to be anything I
can change. So the question still remains: why is it going slowly when it
used to not, and what can I do about it? Is there anyway to tell it to
get all of it's "sets of ten" at once, when the view is opened, rather
than waiting until the view is scrolled? Are there any other workarounds
or settings that can be tried here?

Anyway, we seem to be back at square one. But at least there's a bit more
knowledge now, and at least it explains the performance increase when the
encryption took place (though not why the virtual pk was lost in the
first place).

Thanks!

Neil

"Neil" <nj****@pxdy.com> wrote in message
news:8K***************@newsread1.news.pas.earthlin k.net...

Well, here's the bizarre resolution of this -- fitting for a bizarre
situation.

First, note that the view in question had one main table, two lookup
tables, and a subview. Again, when linked to the MDB file it would
scroll very slowly; but in the ADP file, it was zippidy fast.

I took out the two lookup tables, and reattached the view, and it became
fast in the MDB file as well. However, when I took out just one lookup
table and reattached the view, it was still slow.

Looking for some sort of setting that might help, I stumbled up Encrypt
View. I made a copy of the original view (with both lookup tables) and
encrypted it, and it became zippidy fast in the MDB file!

Great, I figured; now I have to encrypt all my views that are slow and
keep a spare copy for editing. However (and this is the really bizarre
part) that turned out to not be the case.

Though I had tried refreshing the original link before encrypting the
view copy (with no improvement), I tried refreshing the link again after
encrypting the view copy, and, it too was zippidy fast.

In other words, though the original view was unchanged, after I made a
copy of it and encrypted that copy, it became fast again.

I don't understand what happened here, but it clearly must be some sort
of compile/optimization issue which kicked in when I encrypted that copy
of the view.

So if anyone has any idea as to what's going on here, it would be
appreciated. Also, is there any way to force whatever happened here to
happen without having to make a copy of a view, encrypt it, and then
delete it?

Thanks everyone for your assistance.

Neil


"Neil" <nj****@pxdy.com> wrote in message
news:Ux*******************@newsread1.news.pas.eart hlink.net...
I have a situation with an ODBC linked view in an Access 2000 MDB with a
SQL 7 back end. The view is scrolling very slowly. However, if I open
the view in an ADP file, it scrolls quickly.

I needed to use an ODBC link for the view because it needs to be
editable. Otherwise, I would have used a pass-through query.

In previous discussions about using an MDB file vs. an ADP file as a
front end for SQL Server, the impression I got was that both were about
the same, but that the MDB was a more mature technology and less
problematic than the ADP technology. However, the speed difference I'm
noticing with the ADP file in regards to this view is significant and
is very disconcerting re. using an MDB file.

Any thoughts/comments/suggestions would be appreciated. I've reproduced
the view's SQL below for reference.

Thanks,

Neil

SQL for view in question:

SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))

SQL for vwInventory_Dupes, used as subquery:

SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))

[david epsom dot com dot au]

> In this case, though, we're dealing with a very small number of records

(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. the
form is hideously slow in scrolling. Even displaying the initial of about
20 takes forever (about 5 seconds :-) ).

I suggest that you start a new thread with this information.

Don't mention ADP's at all. Do mention that it is SQL Server,
and that it works differently if there is no primary key defined.


'--

I'm not an advocate on the mdb/adp issue: I just don't
use adp. We have a very large application with most
customers using an MDB backend: we do the best we can
for SQL Server clients.

Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced

You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.
'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...

If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.

In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in. Yet
the form is hideously slow in scrolling. Even displaying the initial
screen of about 20 takes forever (about 5 seconds :-) ).

All this could be attributed to the MDB blues. However, as noted, this
form has been in place for a long time without any slowness. All of a
sudden, it started acting this way, and I can't track it down.

For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)

Not sure what you mean by "clicking in the database window." In my case,
the form is opened or the user specifies an option in the form and the
recordset is reset. A dynamic SQL statement is built, and the form's
recordsource is set to the SQL.

I did try opening a DAO recordset and setting the form's Recordset
property instead of using the Recordsource, but the results were the same.

Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.

All locking in the form is off.

Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.

Well, that would be simpler, of course. But there are times when you need
continuous form view. And, as noted, it's been working fine until
recently.

BTW, since you mention those who advocate MDB over ADP, does that mean
that you're an advocate of ADP?

BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.

I haven't seen any places to turn this on on an application level. And
since this is happening globally (both on the client's LAN, as well as on
my local PC), I can't see all of the machines having that turned on. Or is
there a global setting for the application itself, rather than the PC?

Thanks,

Neil

[Neil]

> Record-Level locking is a Engine-level option,

controlled by Access when a database is opened:
Tools | Options | Advanced
My understanding is that the option in Tools | Options | Advanced is only
the default record locking for forms, reports, and queries. That is, when a
new object is created, it is given the default record locking option
specified there.

I suppose if one edits data directly in a table, the default record locking
option comes into play. But when there is a form, report, or query, the
RecordLocks property of that object trumps the default record locking option
specified in Tools | Options | Advanced. That's my understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:

"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the record-locking
setting you choose for your database, Microsoft Access always acts as though
the No Locks setting has been selected."

You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.
That could be; but one wouldn't think that going from 21 fields displayed to
24 would make that much of a difference. Also, I tried removing the three
fields, and it's still slow -- which leads me to wonder if it's some sort of
optimization issue on the SQL end which will eventually work itself out.
Probably not; but it's a thought.

Neil
'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...

If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.

In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in.
Yet the form is hideously slow in scrolling. Even displaying the initial
screen of about 20 takes forever (about 5 seconds :-) ).

All this could be attributed to the MDB blues. However, as noted, this
form has been in place for a long time without any slowness. All of a
sudden, it started acting this way, and I can't track it down.

For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)

Not sure what you mean by "clicking in the database window." In my case,
the form is opened or the user specifies an option in the form and the
recordset is reset. A dynamic SQL statement is built, and the form's
recordsource is set to the SQL.

I did try opening a DAO recordset and setting the form's Recordset
property instead of using the Recordsource, but the results were the
same.

Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.

All locking in the form is off.

Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.

Well, that would be simpler, of course. But there are times when you need
continuous form view. And, as noted, it's been working fine until
recently.

BTW, since you mention those who advocate MDB over ADP, does that mean
that you're an advocate of ADP?

BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.

I haven't seen any places to turn this on on an application level. And
since this is happening globally (both on the client's LAN, as well as on
my local PC), I can't see all of the machines having that turned on. Or
is there a global setting for the application itself, rather than the PC?

Thanks,

Neil

[david epsom dot com dot au]

The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.

The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.

Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:VG****************@newsread1.news.pas.earthli nk.net...

Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced

My understanding is that the option in Tools | Options | Advanced is only
the default record locking for forms, reports, and queries. That is, when
a new object is created, it is given the default record locking option
specified there.

I suppose if one edits data directly in a table, the default record
locking option comes into play. But when there is a form, report, or
query, the RecordLocks property of that object trumps the default record
locking option specified in Tools | Options | Advanced. That's my
understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:

"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the
record-locking setting you choose for your database, Microsoft Access
always acts as though the No Locks setting has been selected."

You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.

That could be; but one wouldn't think that going from 21 fields displayed
to 24 would make that much of a difference. Also, I tried removing the
three fields, and it's still slow -- which leads me to wonder if it's some
sort of optimization issue on the SQL end which will eventually work
itself out. Probably not; but it's a thought.

Neil

'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...

If you open your linked view in the database window.
you will see the row pointer, and, eventually, the total
number of records, updated continuously as Access gets
the records.

In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in.
Yet the form is hideously slow in scrolling. Even displaying the initial
screen of about 20 takes forever (about 5 seconds :-) ).

All this could be attributed to the MDB blues. However, as noted, this
form has been in place for a long time without any slowness. All of a
sudden, it started acting this way, and I can't track it down.

For a start, try using an ADO or DAO object instead
of the Access Application object (clicking in the
database window is equivalent to application.DoCmd
or application.OpenQuery)

Not sure what you mean by "clicking in the database window." In my case,
the form is opened or the user specifies an option in the form and the
recordset is reset. A dynamic SQL statement is built, and the form's
recordsource is set to the SQL.

I did try opening a DAO recordset and setting the form's Recordset
property instead of using the Recordsource, but the results were the
same.

Another thing you can try is 'record level locking'.
Turn that off if you can: I've noticed an obscure effect
on ODBC transaction handling, which indicates that
the connection is subtly different when this option
is selected.

All locking in the form is off.
Regarding ADP vs MDB: it is only fair to note that those
people who advocate MDB over ADB would not in general
countenance the opening of a view in datasheet view
at all. Accepted wisdom is that, as when using a bank
ATM, the user should request only one record, and only
the requested record should be displayed.

Well, that would be simpler, of course. But there are times when you
need continuous form view. And, as noted, it's been working fine until
recently.

BTW, since you mention those who advocate MDB over ADP, does that mean
that you're an advocate of ADP?

BTW, a classic cause of the kind of behaviour you are
seeing is ---- inadvertent SQL or ODBC logging ----
Check that you haven't accidentally left logging turned
on somewhere :~(. The Jet settings are TraceSQLMode
and TraceODBCAPI: there are more places to turn on
tracing in your ODBC DSN etc.

I haven't seen any places to turn this on on an application level. And
since this is happening globally (both on the client's LAN, as well as
on my local PC), I can't see all of the machines having that turned on.
Or is there a global setting for the application itself, rather than the
PC?

Thanks,

Neil

[Neil]

OK, thanks for clarifying what you were referring to. Still, that would be
handled on a per-machine basis, and this is something that changed in the
database (either SQL back end or front end MDB), since it happened across
the board, both locally and across the LAN.

N

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:OF**************@TK2MSFTNGP09.phx.gbl...

The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.

The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.

Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:VG****************@newsread1.news.pas.earthli nk.net...

Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced

My understanding is that the option in Tools | Options | Advanced is only
the default record locking for forms, reports, and queries. That is, when
a new object is created, it is given the default record locking option
specified there.

I suppose if one edits data directly in a table, the default record
locking option comes into play. But when there is a form, report, or
query, the RecordLocks property of that object trumps the default record
locking option specified in Tools | Options | Advanced. That's my
understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:

"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the
record-locking setting you choose for your database, Microsoft Access
always acts as though the No Locks setting has been selected."

You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.

That could be; but one wouldn't think that going from 21 fields displayed
to 24 would make that much of a difference. Also, I tried removing the
three fields, and it's still slow -- which leads me to wonder if it's
some sort of optimization issue on the SQL end which will eventually work
itself out. Probably not; but it's a thought.

Neil

'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...
> If you open your linked view in the database window.
> you will see the row pointer, and, eventually, the total
> number of records, updated continuously as Access gets
> the records.

In this case, though, we're dealing with a very small number of records
(usually < 200). When the form is opened, the total number of records
appears immediately, indicating that all records have been brought in.
Yet the form is hideously slow in scrolling. Even displaying the
initial screen of about 20 takes forever (about 5 seconds :-) ).

All this could be attributed to the MDB blues. However, as noted, this
form has been in place for a long time without any slowness. All of a
sudden, it started acting this way, and I can't track it down.

> For a start, try using an ADO or DAO object instead
> of the Access Application object (clicking in the
> database window is equivalent to application.DoCmd
> or application.OpenQuery)

Not sure what you mean by "clicking in the database window." In my
case, the form is opened or the user specifies an option in the form
and the recordset is reset. A dynamic SQL statement is built, and the
form's recordsource is set to the SQL.

I did try opening a DAO recordset and setting the form's Recordset
property instead of using the Recordsource, but the results were the
same.

> Another thing you can try is 'record level locking'.
> Turn that off if you can: I've noticed an obscure effect
> on ODBC transaction handling, which indicates that
> the connection is subtly different when this option
> is selected.

All locking in the form is off.

>
> Regarding ADP vs MDB: it is only fair to note that those
> people who advocate MDB over ADB would not in general
> countenance the opening of a view in datasheet view
> at all. Accepted wisdom is that, as when using a bank
> ATM, the user should request only one record, and only
> the requested record should be displayed.

Well, that would be simpler, of course. But there are times when you
need continuous form view. And, as noted, it's been working fine until
recently.

BTW, since you mention those who advocate MDB over ADP, does that mean
that you're an advocate of ADP?

> BTW, a classic cause of the kind of behaviour you are
> seeing is ---- inadvertent SQL or ODBC logging ----
> Check that you haven't accidentally left logging turned
> on somewhere :~(. The Jet settings are TraceSQLMode
> and TraceODBCAPI: there are more places to turn on
> tracing in your ODBC DSN etc.

I haven't seen any places to turn this on on an application level. And
since this is happening globally (both on the client's LAN, as well as
on my local PC), I can't see all of the machines having that turned on.
Or is there a global setting for the application itself, rather than
the PC?

Thanks,

Neil

[Alex - IntraLAN]

Hi all,
great post, good debate, I just want to add my opinion to the post. I
have been using access for years since Access v2, I have used all
combinations of front-end/back-end technologies and the conclusion I
have drawn is this. If you want a database of < 100mb in size the MDB
method works very well, as soon as you are in a large database > 200mb
100+ tables 100+ forms then adp style projects seem to be better as the
maximum underlying connections to a DB is 2048 and linked MDB/ODBC
tables require 2 connections for every table/combo box/and other table
style connections and ADP/SQL tables only require 1. I have only hit
this limitation 3 times in my 10+ years developing access applications,
but when I hit it with the MDB I have only one choice convert to an ADP
project. From a performance perspective I would say that DAO is
slightly faster using similar code/loops etc to ADO, but once the move
to ADP has been made then it is a shame if the application is not
optimised using stored procedures to run the main code loops that don't
require user input or progress to the screen whilst running, I see
performance increases of 10x+ using stored procedure over client side
code loops. At the end of the day it is horses for courses, small apps
+ small data MDB every time, big app + big data ADP/SQL every time. I
keep hearing of problems with ADP projects I have not had any major
problems with these and I have one site running an ADP (access
2000)/SQL7 application on 150+ terminals I have not had to fix or get
involved in anything for 4 years. The most important thing to me is db
design with as much of the program logic held in the database and not
the front end. A couple of thing that I have done to improve query
performance is
never use 'Select * from' and to trim the field length of long fields
if the results are going to a list box 'SELECT
Convert(char(30),rsContacts.Name_Of_Solicitor)' and to add the
following to the end of those line 'FOR BROWSE' which seems to help
with locking on the server.

Just my thoughts.

Alex



Neil wrote:

OK, thanks for clarifying what you were referring to. Still, that would be handled on a per-machine basis, and this is something that changed in the database (either SQL back end or front end MDB), since it happened across the board, both locally and across the LAN.

N

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:OF**************@TK2MSFTNGP09.phx.gbl...

The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.

The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.

Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:VG****************@newsread1.news.pas.earthli nk.net...

Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced

My understanding is that the option in Tools | Options | Advanced is only the default record locking for forms, reports, and queries. That is, when a new object is created, it is given the default record locking option specified there.

I suppose if one edits data directly in a table, the default record locking option comes into play. But when there is a form, report, or query, the RecordLocks property of that object trumps the default record locking option specified in Tools | Options | Advanced. That's my
understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL database, Jet can't lock the records anyway. From Access online help:
"Note When you edit data in a linked SQL database table by using ODBC, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the
record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected."
You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.

That could be; but one wouldn't think that going from 21 fields displayed to 24 would make that much of a difference. Also, I tried removing the three fields, and it's still slow -- which leads me to wonder if it's some sort of optimization issue on the SQL end which will eventually work itself out. Probably not; but it's a thought.

Neil

'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...
>> If you open your linked view in the database window.
>> you will see the row pointer, and, eventually, the total
>> number of records, updated continuously as Access gets
>> the records.
>
> In this case, though, we're dealing with a very small number of records> (usually < 200). When the form is opened, the total number of records> appears immediately, indicating that all records have been brought in.> Yet the form is hideously slow in scrolling. Even displaying the initial screen of about 20 takes forever (about 5 seconds :-) ).
>
> All this could be attributed to the MDB blues. However, as noted, this> form has been in place for a long time without any slowness. All of a> sudden, it started acting this way, and I can't track it down.
>
>> For a start, try using an ADO or DAO object instead
>> of the Access Application object (clicking in the
>> database window is equivalent to application.DoCmd
>> or application.OpenQuery)
>
> Not sure what you mean by "clicking in the database window." In my> case, the form is opened or the user specifies an option in the form> and the recordset is reset. A dynamic SQL statement is built, and the> form's recordsource is set to the SQL.
>
> I did try opening a DAO recordset and setting the form's Recordset> property instead of using the Recordsource, but the results were the> same.
>
>> Another thing you can try is 'record level locking'.
>> Turn that off if you can: I've noticed an obscure effect
>> on ODBC transaction handling, which indicates that
>> the connection is subtly different when this option
>> is selected.
>
> All locking in the form is off.
>
>>
>> Regarding ADP vs MDB: it is only fair to note that those
>> people who advocate MDB over ADB would not in general
>> countenance the opening of a view in datasheet view
>> at all. Accepted wisdom is that, as when using a bank
>> ATM, the user should request only one record, and only
>> the requested record should be displayed.
>
> Well, that would be simpler, of course. But there are times when you> need continuous form view. And, as noted, it's been working fine until> recently.
>
> BTW, since you mention those who advocate MDB over ADP, does that mean> that you're an advocate of ADP?
>
>> BTW, a classic cause of the kind of behaviour you are
>> seeing is ---- inadvertent SQL or ODBC logging ----
>> Check that you haven't accidentally left logging turned
>> on somewhere :~(. The Jet settings are TraceSQLMode
>> and TraceODBCAPI: there are more places to turn on
>> tracing in your ODBC DSN etc.
>
> I haven't seen any places to turn this on on an application level. And> since this is happening globally (both on the client's LAN, as well as> on my local PC), I can't see all of the machines having that turned on.> Or is there a global setting for the application itself, rather than> the PC?
>
> Thanks,
>
> Neil
>

[david epsom dot com dot au]

I don't see that you have reposted as a I suggested?
I don't think that the MDB subject is exhausted, but I
don't want to continue posting in an ADP thread if you
have start a newer, more relevant thread.

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:1Z****************@newsread2.news.pas.earthli nk.net...

OK, thanks for clarifying what you were referring to. Still, that would be
handled on a per-machine basis, and this is something that changed in the
database (either SQL back end or front end MDB), since it happened across
the board, both locally and across the LAN.

N

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:OF**************@TK2MSFTNGP09.phx.gbl...

The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.

The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.

Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:VG****************@newsread1.news.pas.earthli nk.net...

Record-Level locking is a Engine-level option,
controlled by Access when a database is opened:
Tools | Options | Advanced

My understanding is that the option in Tools | Options | Advanced is
only the default record locking for forms, reports, and queries. That
is, when a new object is created, it is given the default record locking
option specified there.

I suppose if one edits data directly in a table, the default record
locking option comes into play. But when there is a form, report, or
query, the RecordLocks property of that object trumps the default record
locking option specified in Tools | Options | Advanced. That's my
understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:

"Note When you edit data in a linked SQL database table by using ODBC,
Microsoft Access doesn't lock records; instead, the rules of that SQL
database govern locking. In this instance, regardless of the
record-locking setting you choose for your database, Microsoft Access
always acts as though the No Locks setting has been selected."
You have obviously loaded a 'dyanaset' instead of a
'snapshot': Jet has retrieved pointers to all of the
records, and is now laboriously retrieving the data,
10 records at a time. The switch may have happened
just because of the size of the records increased.

That could be; but one wouldn't think that going from 21 fields
displayed to 24 would make that much of a difference. Also, I tried
removing the three fields, and it's still slow -- which leads me to
wonder if it's some sort of optimization issue on the SQL end which will
eventually work itself out. Probably not; but it's a thought.

Neil

'---

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:bh****************@newsread2.news.pas.earthli nk.net...
>> If you open your linked view in the database window.
>> you will see the row pointer, and, eventually, the total
>> number of records, updated continuously as Access gets
>> the records.
>
> In this case, though, we're dealing with a very small number of
> records (usually < 200). When the form is opened, the total number of
> records appears immediately, indicating that all records have been
> brought in. Yet the form is hideously slow in scrolling. Even
> displaying the initial screen of about 20 takes forever (about 5
> seconds :-) ).
>
> All this could be attributed to the MDB blues. However, as noted, this
> form has been in place for a long time without any slowness. All of a
> sudden, it started acting this way, and I can't track it down.
>
>> For a start, try using an ADO or DAO object instead
>> of the Access Application object (clicking in the
>> database window is equivalent to application.DoCmd
>> or application.OpenQuery)
>
> Not sure what you mean by "clicking in the database window." In my
> case, the form is opened or the user specifies an option in the form
> and the recordset is reset. A dynamic SQL statement is built, and the
> form's recordsource is set to the SQL.
>
> I did try opening a DAO recordset and setting the form's Recordset
> property instead of using the Recordsource, but the results were the
> same.
>
>> Another thing you can try is 'record level locking'.
>> Turn that off if you can: I've noticed an obscure effect
>> on ODBC transaction handling, which indicates that
>> the connection is subtly different when this option
>> is selected.
>
> All locking in the form is off.
>
>>
>> Regarding ADP vs MDB: it is only fair to note that those
>> people who advocate MDB over ADB would not in general
>> countenance the opening of a view in datasheet view
>> at all. Accepted wisdom is that, as when using a bank
>> ATM, the user should request only one record, and only
>> the requested record should be displayed.
>
> Well, that would be simpler, of course. But there are times when you
> need continuous form view. And, as noted, it's been working fine until
> recently.
>
> BTW, since you mention those who advocate MDB over ADP, does that mean
> that you're an advocate of ADP?
>
>> BTW, a classic cause of the kind of behaviour you are
>> seeing is ---- inadvertent SQL or ODBC logging ----
>> Check that you haven't accidentally left logging turned
>> on somewhere :~(. The Jet settings are TraceSQLMode
>> and TraceODBCAPI: there are more places to turn on
>> tracing in your ODBC DSN etc.
>
> I haven't seen any places to turn this on on an application level. And
> since this is happening globally (both on the client's LAN, as well as
> on my local PC), I can't see all of the machines having that turned
> on. Or is there a global setting for the application itself, rather
> than the PC?
>
> Thanks,
>
> Neil
>

[Neil]

Alex,

Thank you for your very helpful post. A few comments below.

the conclusion I
have drawn is this. If you want a database of < 100mb in size the MDB
method works very well, as soon as you are in a large database > 200mb
100+ tables 100+ forms then adp style projects seem to be better
The database file itself is about 200-270 mb; but it doesn't have that many
tables. There are two main tables, which make up most of the data; a few
medium-sized tables; and a slew of small lookup tables -- roughly 80 tables,
though I'd say that at any given time less than 10 are in use by any single
user.
as the
maximum underlying connections to a DB is 2048 and linked MDB/ODBC
tables require 2 connections for every table/combo box/and other table
style connections and ADP/SQL tables only require 1. I have only hit
this limitation 3 times in my 10+ years developing access applications,
but when I hit it with the MDB I have only one choice convert to an ADP
project.
As noted, there aren't a lot of tables in use at any given time. And our
most complex form has maybe 40 combo boxes. So we don't seem to be
approaching this limit.

But, be that as it may, the situation I noted in this thread regarded a view
which was fast in an ADP file, and used to be just as fast in an MDB file;
but now is slow in the MDB file. This situation occurs:

* when I use the database files on my unnetworked PC running MSDE with no
other apps running;

* when the MDB file is open without any forms or reports open;

* when the linked view is opened by itself from the database window without
a form.

As noted previously, it takes about 5 seconds just to bring up a screen of
about 20 records, and each time the scroll bar is clicked (even when moving
down a single record), the same or a similar delay occurs.

This same view was working fine until recently, and was used for several
years as the recordsource for a form that edited it in Continuous Forms
view. I added a few fields to the view, and things slowed down. I tried
taking the fields out, but the results are the same. (The adding of the
three fields may have been coincidental to something else that may have
caused the slowdown.)

Either way, the view is still fast in an ADP file, but is slow as molasses
in its original MDB file.
I
keep hearing of problems with ADP projects I have not had any major
problems with these and I have one site running an ADP (access
2000)/SQL7 application on 150+ terminals I have not had to fix or get
involved in anything for 4 years.
That's good to know. One of the things I've been discussing here is that
this database is going to be rebuilt from the ground up (was converted to
Access from an old database program by a developer, then was modified by
another developer who didn't understand principles of normalized design,
then was inherited by me). I am leaning towards redoing it in MDB,
especially given the many testimonies of problems people have had with ADP.
But your input is certainly significant here as well. So that's good to
know.
The most important thing to me is db
design with as much of the program logic held in the database and not
the front end.
Yeah, well, here the whole thing is in the back end. The whole idea is that
SQL Server does all the work and the MDB just has to get the data. But,
apparently, it has problems even with just that!
A couple of thing that I have done to improve query
performance is
never use 'Select * from' and to trim the field length of long fields
if the results are going to a list box 'SELECT
Convert(char(30),rsContacts.Name_Of_Solicitor)' and to add the
following to the end of those line 'FOR BROWSE' which seems to help
with locking on the server.
I didn't see a "FOR BROWSE" option in the SQL 7 BOL. Is that a SQL 2000
option?

Thanks,

Neil

Just my thoughts.

Alex



Neil wrote:

OK, thanks for clarifying what you were referring to. Still, that

would be

handled on a per-machine basis, and this is something that changed in

the

database (either SQL back end or front end MDB), since it happened

across

the board, both locally and across the LAN.

N

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in

message

news:OF**************@TK2MSFTNGP09.phx.gbl...

> The Option that is labelled "Open databases using record-level
> locking" controls the way databases are opened. The options
> are record-level locking and page-level locking. Another
> 'kind' of locking is table-level locking. At the engine level,
> these are the kinds of locks that are used.
>
> The decisions about WHEN to lock (early, late, never,
> optimistically, pessimistically etc) are controlled
> separately, for example by form properties.
>
> Theoretically, there is no reason why the lock scope should
> affect the data transfer strategy, but I have noticed some
> odd effects of this setting, which is why I suggested that
> you try changing it.
>
> (david)
>
>
> "Neil" <nj****@pxdy.com> wrote in message
> news:VG****************@newsread1.news.pas.earthli nk.net...
>>> Record-Level locking is a Engine-level option,
>>> controlled by Access when a database is opened:
>>> Tools | Options | Advanced
>>
>> My understanding is that the option in Tools | Options | Advanced is only >> the default record locking for forms, reports, and queries. That is, when >> a new object is created, it is given the default record locking option >> specified there.
>>
>> I suppose if one edits data directly in a table, the default record >> locking option comes into play. But when there is a form, report, or >> query, the RecordLocks property of that object trumps the default record >> locking option specified in Tools | Options | Advanced. That's my
>> understanding, anyway.
>>
>> Regardless, the point is somewhat moot, since, when dealing with a SQL >> database, Jet can't lock the records anyway. From Access online help: >>
>> "Note When you edit data in a linked SQL database table by using ODBC, >> Microsoft Access doesn't lock records; instead, the rules of that SQL >> database govern locking. In this instance, regardless of the
>> record-locking setting you choose for your database, Microsoft Access >> always acts as though the No Locks setting has been selected."
>>
>>>
>>> You have obviously loaded a 'dyanaset' instead of a
>>> 'snapshot': Jet has retrieved pointers to all of the
>>> records, and is now laboriously retrieving the data,
>>> 10 records at a time. The switch may have happened
>>> just because of the size of the records increased.
>>
>> That could be; but one wouldn't think that going from 21 fields displayed >> to 24 would make that much of a difference. Also, I tried removing the >> three fields, and it's still slow -- which leads me to wonder if it's >> some sort of optimization issue on the SQL end which will eventually work >> itself out. Probably not; but it's a thought.
>>
>> Neil
>>
>>
>>
>>> '---
>>>
>>> (david)
>>>
>>>
>>>
>>> "Neil" <nj****@pxdy.com> wrote in message
>>> news:bh****************@newsread2.news.pas.earthli nk.net...
>>>>> If you open your linked view in the database window.
>>>>> you will see the row pointer, and, eventually, the total
>>>>> number of records, updated continuously as Access gets
>>>>> the records.
>>>>
>>>> In this case, though, we're dealing with a very small number of records >>>> (usually < 200). When the form is opened, the total number of records >>>> appears immediately, indicating that all records have been brought in. >>>> Yet the form is hideously slow in scrolling. Even displaying the >>>> initial screen of about 20 takes forever (about 5 seconds :-) ).
>>>>
>>>> All this could be attributed to the MDB blues. However, as noted, this >>>> form has been in place for a long time without any slowness. All of a >>>> sudden, it started acting this way, and I can't track it down.
>>>>
>>>>> For a start, try using an ADO or DAO object instead
>>>>> of the Access Application object (clicking in the
>>>>> database window is equivalent to application.DoCmd
>>>>> or application.OpenQuery)
>>>>
>>>> Not sure what you mean by "clicking in the database window." In my >>>> case, the form is opened or the user specifies an option in the form >>>> and the recordset is reset. A dynamic SQL statement is built, and the >>>> form's recordsource is set to the SQL.
>>>>
>>>> I did try opening a DAO recordset and setting the form's Recordset >>>> property instead of using the Recordsource, but the results were the >>>> same.
>>>>
>>>>> Another thing you can try is 'record level locking'.
>>>>> Turn that off if you can: I've noticed an obscure effect
>>>>> on ODBC transaction handling, which indicates that
>>>>> the connection is subtly different when this option
>>>>> is selected.
>>>>
>>>> All locking in the form is off.
>>>>
>>>>>
>>>>> Regarding ADP vs MDB: it is only fair to note that those
>>>>> people who advocate MDB over ADB would not in general
>>>>> countenance the opening of a view in datasheet view
>>>>> at all. Accepted wisdom is that, as when using a bank
>>>>> ATM, the user should request only one record, and only
>>>>> the requested record should be displayed.
>>>>
>>>> Well, that would be simpler, of course. But there are times when you >>>> need continuous form view. And, as noted, it's been working fine until >>>> recently.
>>>>
>>>> BTW, since you mention those who advocate MDB over ADP, does that mean >>>> that you're an advocate of ADP?
>>>>
>>>>> BTW, a classic cause of the kind of behaviour you are
>>>>> seeing is ---- inadvertent SQL or ODBC logging ----
>>>>> Check that you haven't accidentally left logging turned
>>>>> on somewhere :~(. The Jet settings are TraceSQLMode
>>>>> and TraceODBCAPI: there are more places to turn on
>>>>> tracing in your ODBC DSN etc.
>>>>
>>>> I haven't seen any places to turn this on on an application level. And >>>> since this is happening globally (both on the client's LAN, as well as >>>> on my local PC), I can't see all of the machines having that turned on. >>>> Or is there a global setting for the application itself, rather than >>>> the PC?
>>>>
>>>> Thanks,
>>>>
>>>> Neil
>>>>
>>>
>>>
>>
>>
>
>

[Neil]

Yeah, I sort of gave up on the discussion. But, if you want to post some
more, I'll go ahead and start a new thread. Will reply here again with the
name after I post it.

Thanks!

Neil
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:ud**************@TK2MSFTNGP14.phx.gbl...

I don't see that you have reposted as a I suggested?
I don't think that the MDB subject is exhausted, but I
don't want to continue posting in an ADP thread if you
have start a newer, more relevant thread.

(david)

"Neil" <nj****@pxdy.com> wrote in message
news:1Z****************@newsread2.news.pas.earthli nk.net...

OK, thanks for clarifying what you were referring to. Still, that would
be handled on a per-machine basis, and this is something that changed in
the database (either SQL back end or front end MDB), since it happened
across the board, both locally and across the LAN.

N

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:OF**************@TK2MSFTNGP09.phx.gbl...

The Option that is labelled "Open databases using record-level
locking" controls the way databases are opened. The options
are record-level locking and page-level locking. Another
'kind' of locking is table-level locking. At the engine level,
these are the kinds of locks that are used.

The decisions about WHEN to lock (early, late, never,
optimistically, pessimistically etc) are controlled
separately, for example by form properties.

Theoretically, there is no reason why the lock scope should
affect the data transfer strategy, but I have noticed some
odd effects of this setting, which is why I suggested that
you try changing it.

(david)
"Neil" <nj****@pxdy.com> wrote in message
news:VG****************@newsread1.news.pas.earthli nk.net...
> Record-Level locking is a Engine-level option,
> controlled by Access when a database is opened:
> Tools | Options | Advanced

My understanding is that the option in Tools | Options | Advanced is
only the default record locking for forms, reports, and queries. That
is, when a new object is created, it is given the default record
locking option specified there.

I suppose if one edits data directly in a table, the default record
locking option comes into play. But when there is a form, report, or
query, the RecordLocks property of that object trumps the default
record locking option specified in Tools | Options | Advanced. That's
my understanding, anyway.

Regardless, the point is somewhat moot, since, when dealing with a SQL
database, Jet can't lock the records anyway. From Access online help:

"Note When you edit data in a linked SQL database table by using
ODBC, Microsoft Access doesn't lock records; instead, the rules of that
SQL database govern locking. In this instance, regardless of the
record-locking setting you choose for your database, Microsoft Access
always acts as though the No Locks setting has been selected."

>
> You have obviously loaded a 'dyanaset' instead of a
> 'snapshot': Jet has retrieved pointers to all of the
> records, and is now laboriously retrieving the data,
> 10 records at a time. The switch may have happened
> just because of the size of the records increased.

That could be; but one wouldn't think that going from 21 fields
displayed to 24 would make that much of a difference. Also, I tried
removing the three fields, and it's still slow -- which leads me to
wonder if it's some sort of optimization issue on the SQL end which
will eventually work itself out. Probably not; but it's a thought.

Neil

> '---
>
> (david)
>
>
>
> "Neil" <nj****@pxdy.com> wrote in message
> news:bh****************@newsread2.news.pas.earthli nk.net...
>>> If you open your linked view in the database window.
>>> you will see the row pointer, and, eventually, the total
>>> number of records, updated continuously as Access gets
>>> the records.
>>
>> In this case, though, we're dealing with a very small number of
>> records (usually < 200). When the form is opened, the total number of
>> records appears immediately, indicating that all records have been
>> brought in. Yet the form is hideously slow in scrolling. Even
>> displaying the initial screen of about 20 takes forever (about 5
>> seconds :-) ).
>>
>> All this could be attributed to the MDB blues. However, as noted,
>> this form has been in place for a long time without any slowness. All
>> of a sudden, it started acting this way, and I can't track it down.
>>
>>> For a start, try using an ADO or DAO object instead
>>> of the Access Application object (clicking in the
>>> database window is equivalent to application.DoCmd
>>> or application.OpenQuery)
>>
>> Not sure what you mean by "clicking in the database window." In my
>> case, the form is opened or the user specifies an option in the form
>> and the recordset is reset. A dynamic SQL statement is built, and the
>> form's recordsource is set to the SQL.
>>
>> I did try opening a DAO recordset and setting the form's Recordset
>> property instead of using the Recordsource, but the results were the
>> same.
>>
>>> Another thing you can try is 'record level locking'.
>>> Turn that off if you can: I've noticed an obscure effect
>>> on ODBC transaction handling, which indicates that
>>> the connection is subtly different when this option
>>> is selected.
>>
>> All locking in the form is off.
>>
>>>
>>> Regarding ADP vs MDB: it is only fair to note that those
>>> people who advocate MDB over ADB would not in general
>>> countenance the opening of a view in datasheet view
>>> at all. Accepted wisdom is that, as when using a bank
>>> ATM, the user should request only one record, and only
>>> the requested record should be displayed.
>>
>> Well, that would be simpler, of course. But there are times when you
>> need continuous form view. And, as noted, it's been working fine
>> until recently.
>>
>> BTW, since you mention those who advocate MDB over ADP, does that
>> mean that you're an advocate of ADP?
>>
>>> BTW, a classic cause of the kind of behaviour you are
>>> seeing is ---- inadvertent SQL or ODBC logging ----
>>> Check that you haven't accidentally left logging turned
>>> on somewhere :~(. The Jet settings are TraceSQLMode
>>> and TraceODBCAPI: there are more places to turn on
>>> tracing in your ODBC DSN etc.
>>
>> I haven't seen any places to turn this on on an application level.
>> And since this is happening globally (both on the client's LAN, as
>> well as on my local PC), I can't see all of the machines having that
>> turned on. Or is there a global setting for the application itself,
>> rather than the PC?
>>
>> Thanks,
>>
>> Neil
>>
>
>