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))
The ways ADPs and MDBs use recordsets are very different, so sometimes one or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection to
grab the list of keys for the records, then reads the actual records as needed
for display by doing individual selects of 10 records aby explicit key. This
can help speed when each row has a lot of data because only the rows that are
actually viewd will be read, not all of them. On the other hand, if the table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>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))
>
|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.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))
>
|||On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>You may try to add a virtual index on your linked view:
>http://support.microsoft.com/kb/q209123/
>If this doesn't help, then you will have to use an unbound form coupled with
>SQL pass-through queries (or ADO objects) to resolve your speed problem with
>MDB. Another solution could be to go with TS to simulate a high-speed LAN.
>I don't know where you got the impression that MDB were about the same as
>ADP in term of speed. It has been repeated a number of times that the use
>of MDB' linked tables and views is only a workable solution for small
>databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP. When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.eart hlink.net...
>
|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlin k.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>
|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>
|||Did you specify a field or set of fields to be used as the primary key for the
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sure
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to bring
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com.. .
>
|||Well, perhaps the reason for our difference of opinion is that I never thought
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriting
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate that
>MDB files are much slower to access a SQL-Server backend. The use of linked
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred of
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com.. .
>
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Thursday, March 8, 2012
ADP vs. MDB: Speed
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))The ways ADPs and MDBs use recordsets are very different, so sometimes one o
r
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection
to
grab the list of keys for the records, then reads the actual records as need
ed
for display by doing individual selects of 10 records aby explicit key. Thi
s
can help speed when each row has a lot of data because only the rows that ar
e
actually viewd will be read, not all of them. On the other hand, if the tab
le
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each ro
w
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQ
L
>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 fil
e
>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))
>|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.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))
>|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
>|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlink.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>|||Did you specify a field or set of fields to be used as the primary key for t
he
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sur
e
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to brin
g
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
>|||Well, perhaps the reason for our difference of opinion is that I never thoug
ht
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriti
ng
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fi
ll
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate tha
t
>MDB files are much slower to access a SQL-Server backend. The use of linke
d
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred o
f
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
>|||"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
> 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.
Yes, a few things needs to cleared up here.
First, using a ADP with sql server is kind of like using a automatic truck
vs a truck with a standard shift. If you don't know trucks, and how to shift
gears, then obviously the automatic truck is going to be better. On the
other hand, an advanced driver will get the same performance out of the
standard truck...
If you look at the sql you posted, there is a number of tables involved.
If you just link a bunch of tables to sql server, and then tell ms-access to
"join" them together..you will often get poor performance. I mean, obviously
ms-access/JET has to pull data from multiple tables. With ODBC linked
tables, each table is *often* thought as a separate data source. With
a ADP, that sql is processed server side.
Remember, ms-access/JET has a difficult
time working with multiple ODBC DATA sources when you do a join on them. The
reason of course is that one table might be from Oracle, and the other might
be a local FoxPro table. When you join data through linked tables, you have
to remem ber what the Robot in Lost in space used to say:
"Danger ...Will Robison....Danger"...
When you use a ADP, that whole sql statement is sent to sql server to be
processed.
So, of course, if you used a pass-through query, then both the ADP, and the
ODBC will performance the same.
However, you mentioned that you can't use a pass through. However, you STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise if
you think about what is going on here. However, the simple solution here is
thus to simply create a view on sql server, and then link to that!
Doing this, you will get the same
performance again as the ADP.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on...
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution, and
extra effort to avoid bottle necks. In effect, the increased flexibility of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the same
performance with a mdb/odbc as you get with a
ADP.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
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))The ways ADPs and MDBs use recordsets are very different, so sometimes one o
r
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection
to
grab the list of keys for the records, then reads the actual records as need
ed
for display by doing individual selects of 10 records aby explicit key. Thi
s
can help speed when each row has a lot of data because only the rows that ar
e
actually viewd will be read, not all of them. On the other hand, if the tab
le
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each ro
w
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQ
L
>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 fil
e
>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))
>|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.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))
>|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
>|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlink.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>|||Did you specify a field or set of fields to be used as the primary key for t
he
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sur
e
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to brin
g
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
>|||Well, perhaps the reason for our difference of opinion is that I never thoug
ht
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriti
ng
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fi
ll
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate tha
t
>MDB files are much slower to access a SQL-Server backend. The use of linke
d
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred o
f
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
>|||"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
> 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.
Yes, a few things needs to cleared up here.
First, using a ADP with sql server is kind of like using a automatic truck
vs a truck with a standard shift. If you don't know trucks, and how to shift
gears, then obviously the automatic truck is going to be better. On the
other hand, an advanced driver will get the same performance out of the
standard truck...
If you look at the sql you posted, there is a number of tables involved.
If you just link a bunch of tables to sql server, and then tell ms-access to
"join" them together..you will often get poor performance. I mean, obviously
ms-access/JET has to pull data from multiple tables. With ODBC linked
tables, each table is *often* thought as a separate data source. With
a ADP, that sql is processed server side.
Remember, ms-access/JET has a difficult
time working with multiple ODBC DATA sources when you do a join on them. The
reason of course is that one table might be from Oracle, and the other might
be a local FoxPro table. When you join data through linked tables, you have
to remem ber what the Robot in Lost in space used to say:
"Danger ...Will Robison....Danger"...
When you use a ADP, that whole sql statement is sent to sql server to be
processed.
So, of course, if you used a pass-through query, then both the ADP, and the
ODBC will performance the same.
However, you mentioned that you can't use a pass through. However, you STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise if
you think about what is going on here. However, the simple solution here is
thus to simply create a view on sql server, and then link to that!
Doing this, you will get the same
performance again as the ADP.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on...
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution, and
extra effort to avoid bottle necks. In effect, the increased flexibility of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the same
performance with a mdb/odbc as you get with a
ADP.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
Friday, February 24, 2012
ADO.NET DATAVIEWS VERSUS SQL ENGINE
Hi All,
Any suggestions / views / help on below question would be welcomed.
I am building an asp.net 2.0 application with sql 2005 express as back end. My back end has 3 major tables which are:
tblArticles - saves basic info on articles posted by user (like articleid, title, short desc, rating, views, etc)
tblCategories - saves various categories and their hierarchies (id, parented, name, etc)
tblArticleCategories - saves info on which articles fall in which categories (like articleid, categoryid)
as of now, i am caching all rows from the first 2 tables, but i am in a bit of doubt for caching the third table (tblArticleCategories), although data in this table wont change very often and also this table will just have 2 columns and not many rows as well and this is a good target for caching,
but the reason I am in a bit of doubt to cache this table is, when my website visitor clicks on any category link in the category tree view, I need to use an inner join across all these 3 tables to locate and return all articles found in that particular category.
But I can do the same thing without hitting the database as I already have 2 of the required 3 tables in my cache, I can simply add the third table to my cache and then using the dataview objects rowfilter property on these 3 cached tables, I can very well get the appropriate results.
But I wonder which of the 2 methods would you prefer and suggest, I mean do you feel that just to save hits against the database, I am going to far and doing a lot of crap using the dataview (which might not be as efficient as sql engine) or you feel that the inefficiency of the dataview will still win compared to the cost of hitting the database for this
Thanks in advance, bye take care
Raj Chaudhari, Mumbai, India (MCAD.NET)
www.xtremebiz.biz
i would cache smaller objects that are the result of a more specific query.
let the db do its joins and return from the db only articles for a specific category.
cache this data.
when other categories are selected, hit the db, and cache that data too as a seperate object.
the result is that you dont need to perform rowfiltering on every page hit and you will only have articles in cache for categories that have been selected
|||If you are using SQL Express or SQL Server 2005 as the back end, it sounds like you are doing a lot of manual work that you really don't need to. Just use the SQLDatasource object, put your query in the select query text property, set the caching to true, and set the cache dependancy property to the appropriate value "tblArticles,tblCategories,tblArticleCategories" for the query that access all 3 tables. If you have performance problems after that, address them at the time. Heck, remember prior to ASP.NET 2.0 *most* applications did no caching of database queries at all and ran just fine.
ADO Using ODBC vs. OLEDB
I am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
Any input would be appreciated...ThanksCan you post the error message you got?|||Is this an asp or asp.net application ? Yes and please post the errors and the code it is failing on.|||It definitely sounds like all your back-end code sits on the front-end, right? You would save a lot of time if instead of cleaning it up try to move to where it belongs, - back-end.|||Thanks for responding to my post.
I am using standard ASP pages not ASP.NET.
The applications I am working with are comprised of roughly 10K pages altogether. There are many errors that occur and I am able to address them as they arise. It really has to do with a lack of support from one driver to another through ADO.
I really was looking for some resources on this topic not solutions to specific code errors.
If I listed each error and code sample here it could take a long time.
Thanks|||One place you might find useful is the recordset.supports method, it will tell you if a particular method is supported by the recordset or not.
You will find differences with transaction handling too I expect.
The dynamic recordset & connection properties change too.
You could get a copy of Adoanywher browser, it displays the supported functionality of your recordsets and allows you to open multiple connections with multiple recordsets. Full support for transactions. It displays all available recordset and connection properties.
I basically use it to compare drivers just as you are attempting to do.
The browser is currently free if you register quickly at the forum :
http://www.adoanywhere.com/forum
Mike.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
Any input would be appreciated...ThanksCan you post the error message you got?|||Is this an asp or asp.net application ? Yes and please post the errors and the code it is failing on.|||It definitely sounds like all your back-end code sits on the front-end, right? You would save a lot of time if instead of cleaning it up try to move to where it belongs, - back-end.|||Thanks for responding to my post.
I am using standard ASP pages not ASP.NET.
The applications I am working with are comprised of roughly 10K pages altogether. There are many errors that occur and I am able to address them as they arise. It really has to do with a lack of support from one driver to another through ADO.
I really was looking for some resources on this topic not solutions to specific code errors.
If I listed each error and code sample here it could take a long time.
Thanks|||One place you might find useful is the recordset.supports method, it will tell you if a particular method is supported by the recordset or not.
You will find differences with transaction handling too I expect.
The dynamic recordset & connection properties change too.
You could get a copy of Adoanywher browser, it displays the supported functionality of your recordsets and allows you to open multiple connections with multiple recordsets. Full support for transactions. It displays all available recordset and connection properties.
I basically use it to compare drivers just as you are attempting to do.
The browser is currently free if you register quickly at the forum :
http://www.adoanywhere.com/forum
Mike.
Monday, February 13, 2012
ADO & MS SQL 6.5
Hi all,
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server.
Upscene Productions
http://www.upscene.comADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||
> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server.
Upscene Productions
http://www.upscene.com
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server.
Upscene Productions
http://www.upscene.comADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||
> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server.
Upscene Productions
http://www.upscene.com
ADO & MS SQL 6.5
Hi all,
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
ADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||
> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
ADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||
> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
ADO & MS SQL 6.5
Hi all,
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.comADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
How well does ADO work with MS SQL 6.5 ... When trying my
application, it comes back with a "connection failure" quite often
upon trying to run system defined stored procedures and so on.
Can anyone comment on that?
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.comADO should work fine with SQL Server 6.5.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||> ADO should work fine with SQL Server 6.5.
Also on Windows XP?
I've installed MS SQL 6.5 on a Windows XP machine
and when running my app, I keep on getting
Connection Lost.
Would ODBC be better?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
Sunday, February 12, 2012
administrators group is deleted...
I deleted "administrators" group and "administrator" user in sql server by
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>
administrators group is deleted...
I deleted "administrators" group and "administrator" user in sql server by
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>
Subscribe to:
Posts (Atom)