SQL Adding a User account: adp

Showing posts with label adp. Show all posts

Thursday, March 8, 2012

ADP/ADE Permissions Issue

I cannot add, update or delete records in a SQL Server database using ADP/ADE but I can within another database on the same server.

I have two databases. One is the test environment and the other is production. Both database reside on the same server but they have unique logins so as not to allow an admin in the test environment to automatically be an admin in production.

I can do everything and anything necessary through Enterprise Manager to both databases. I can do everything necessary to an Access ADP/ADE application in the test environment (add, update, delete) but I cannot do the same in the production database. I can connect and get a valid connection but the tables do not allow me to update or add rows to tables through the ADP/ADE application.

Given that the only difference to the application is which database it connects to I am left with the conclusion that there is something different to the login in the production environment that is not allowing the ADP application appropriate permissions. Can anyone please help or at least direct me to an answer to correct this problem?

Thanks!Well... I suppose this is what happens when you aren't in charge of the data and it is migrated by someone else.

After much digging I found that the tables in question did not have their primary key migrated with them to the production server. If the table did not have a primary key defined then ADP/ADE would not allow a user to add rows.

This was structural, not security. Hopefully someone else who runs across a similar problem in the future will find this information and more quickly correct their issue.

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 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.. .
>

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

Adp sql - help

Hi,

Does anybody knows how to re-write this access sql into access adp query? Access adp does not seem to recognise the WHERE statement where is refers to other forms. Also applies to the IIF statement??
Please help..... Thanks.

Ms Access query (mdb):
---------
SELECT billing.[Job No], IIf([CountAFE]>1,"Multiple",[FirstOfAFE No]) AS [AFE No]
FROM ([Job-2] INNER JOIN billing ON [Job-2].[Job No] = billing.[Job No]) INNER JOIN [JOB COST-Query1 Sub] ON [Job-2].[Job No] = [JOB COST-Query1 Sub].[Job No]
WHERE (((billing.[Job No])=[Forms]![frmJobCostReport]![JobNumber]));Create a stored procedure
Pass the value of your control as an input parameter|||Try (case when) to replace (IIf).

IIF(condition, result1,result2)
is equivalent to:
CASE WHEN [condintion] THEN [result1] ELSE [result2] END

Good luck!

ADP revision numbers

I have an ADP connected to an SQL 2000 database that works fine on my
machine,
Opening the ADP show lists of stored procedures ala
up_ListPrograms
but, when I copy it to another computer, and open the ADP with the
same connection properties
all of the stored procedures and views have a ?revision? number after
them ala
up_ListPrograms,1

this breaks all my forms, reports etc.
anyone know if this can be controlled somehow?

ThansBruce,

I feel your pain. I know exactly what your are talking about but can't
remember what I did that broke and fixed it for sure.

Hmm, this may be a long shot, but you might want to run "cliconfg" and see
if your using the same default protocol on the client as it is on your dev
unit. Using Named Pipes might be what is causing it. If it is, you can
actually go into the Server Network Utility on the SQL Server and set your
default protocol to TCP/IP and your clients will follow that.

Also, I use integrated/domain security so I can deploy generic ADE's to my
users. Then, when they open, their windows credentials are used to auth
with Sql.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:2cd7rv030dvl7oqo23idekbvmcpp7valv0@.4ax.com...
> I have an ADP connected to an SQL 2000 database that works fine on my
> machine,
> Opening the ADP show lists of stored procedures ala
> up_ListPrograms
> but, when I copy it to another computer, and open the ADP with the
> same connection properties
> all of the stored procedures and views have a ?revision? number after
> them ala
> up_ListPrograms,1
> this breaks all my forms, reports etc.
> anyone know if this can be controlled somehow?
> Thans|||Thanks, turns out the problem machine did not have
all the Service Packs up-to-date. Fixed now.

On Mon, 17 Nov 2003 14:39:32 GMT, "Jerry Boone"
<jerry@.antech.biz.nospam> wrote:

>Bruce,
>I feel your pain. I know exactly what your are talking about but can't
>remember what I did that broke and fixed it for sure.
>Hmm, this may be a long shot, but you might want to run "cliconfg" and see
>if your using the same default protocol on the client as it is on your dev
>unit. Using Named Pipes might be what is causing it. If it is, you can
>actually go into the Server Network Utility on the SQL Server and set your
>default protocol to TCP/IP and your clients will follow that.
>Also, I use integrated/domain security so I can deploy generic ADE's to my
>users. Then, when they open, their windows credentials are used to auth
>with Sql.

ADP Report Problems

Hi all I have two tables joined in a query and I need for one record to show up in the report rather then the whole history. Does that make sense??Does that make sense??No, but if you could post the SELECT statment, an example of what you don't want, and a "cut down" example or two of what you do want, I bet we could figure it out! ;)

-PatP|||Hi all I have two tables joined in a query and I need for one record to show up in the report rather then the whole history. Does that make sense??

No ... but if you hum a few bars I think we can fake it :D|||"ADP Report Problem"?
Should this thread be in the Access forum?|||"ADP Report Problem"?
Should this thread be in the Access forum?I thought the same but I was waiting until anyone actually figured out what the problem was....|||Considering that 95% or better of Desireemm's problems are SQL, I'm willing to give this one the benefit of the doubt. If it turns out that the question belongs elsewhere, we can move it once we know enough to know where to move it to!

-PatP|||I'm so sorry guys I didnt post the Select Statement. You see I have two tables that are joined, the ActiveConditionals table holds the employees conditionals and they could have several of them but I only want to show in my report the most recent ones, from the last two years I would say?/

SELECT TOP 100 PERCENT dbo.EmployeeGamingLicense.TM#, dbo.EmployeeGamingLicense.LASTNAME, dbo.EmployeeGamingLicense.FIRSTNAME,
dbo.ActiveConditional_tbl.CONDITIONAL, dbo.ActiveConditional_tbl.DATEOFCONDITIONAL, dbo.ActiveConditional_tbl.INVESTIGATOR_COND,
dbo.ActiveConditional_tbl.REASONFORCOND
FROM dbo.ActiveConditional_tbl INNER JOIN
dbo.EmployeeGamingLicense ON dbo.ActiveConditional_tbl.TM# = dbo.EmployeeGamingLicense.TM#
ORDER BY dbo.EmployeeGamingLicense.LASTNAME|||So what do you want. The most recent record, or the records from the past 2 years (possibly multiple records)?|||I figured it out guys I'm sorry to waste your time, your the BEST|||I figured it out guys I'm sorry to waste your time, your the BESTI know - we are that good. Ever so glad we sorted out the problem for you :D

ADP project on SQL 2000 sp4

We currently have a adp project linked with a Sql server 7 running NT4.
We need to replace old server with a win server 2003 with SQL 2000 sp4.
When viewing tables, stored procedures from ADP project all objects have
(dbo) after them. I then run into problems when trying to run saved reports -
the report is looking for the table name itself instead of tablename (dbo).
It would be a lot of work to fix all of the reports.
ThanksMaybe you should create a view to meet client tool demands:
CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
so reports will access table1 through the view with the name they wanted for
"exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> We currently have a adp project linked with a Sql server 7 running NT4.
> We need to replace old server with a win server 2003 with SQL 2000 sp4.
> When viewing tables, stored procedures from ADP project all objects have
> (dbo) after them. I then run into problems when trying to run saved
> reports -
> the report is looking for the table name itself instead of tablename
> (dbo).
> It would be a lot of work to fix all of the reports.
> Thanks|||Is there a way to remove the (dbo) from everything. Do you know what causes
this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
no (dbo)
Should I run another instance of SQL 2000 with sp3 instead?
Thank you
"Alex Cieszinski" wrote:
> Maybe you should create a view to meet client tool demands:
> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
> so reports will access table1 through the view with the name they wanted for
> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
> > We currently have a adp project linked with a Sql server 7 running NT4.
> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
> > When viewing tables, stored procedures from ADP project all objects have
> > (dbo) after them. I then run into problems when trying to run saved
> > reports -
> > the report is looking for the table name itself instead of tablename
> > (dbo).
> > It would be a lot of work to fix all of the reports.
> >
> > Thanks
>
>|||It's related to the security context you are using in the
ADP and what permissions that login, user has in SQL Server.
And it's handled a bit differently with different version of
Access - but I don't remember what they changed with 2002
and above from Access 2000.
I know that in Access 2000, if you connect as a sysadmin,
you won't see the dbo appended. All others will see dbo. You
can't remove dbo from everything, you should be creating the
Access objects to use fully qualified names using
Owner.Object.
This is more of an Access issue so you would want to post in
an Access newsgroup. Try:
microsoft.public.access.adp.sqlserver
-Sue
On Fri, 3 Mar 2006 08:13:29 -0800, exchangerookie1994
<exchangerookie1994@.discussions.microsoft.com> wrote:
>Is there a way to remove the (dbo) from everything. Do you know what causes
>this. I tried the exact same thing on a SQL 2000 sp3 and eveything was fine -
>no (dbo)
>Should I run another instance of SQL 2000 with sp3 instead?
>Thank you
>"Alex Cieszinski" wrote:
>> Maybe you should create a view to meet client tool demands:
>> CREATE VIEW [table1 (dbo)] AS SELECT * FROM table1
>> so reports will access table1 through the view with the name they wanted for
>> "exchangerookie1994" <exchangerookie1994@.discussions.microsoft.com> wrote in
>> message news:03F79A1A-56AF-4440-BE3B-27C00AF3FB93@.microsoft.com...
>> > We currently have a adp project linked with a Sql server 7 running NT4.
>> > We need to replace old server with a win server 2003 with SQL 2000 sp4.
>> > When viewing tables, stored procedures from ADP project all objects have
>> > (dbo) after them. I then run into problems when trying to run saved
>> > reports -
>> > the report is looking for the table name itself instead of tablename
>> > (dbo).
>> > It would be a lot of work to fix all of the reports.
>> >
>> > Thanks
>>
>>

ADP project on SQL 2000 sp4

ADP Problem

Hi All,

I have a server that contains sqlserver 2000 and ms access 2000.
Everything were working fine when i ran my adp file in the server side,
and i faced some problem when i ran it on the client computer.

After checking my adp file, i found all of my stored procedures added by
comma and number at the behind. For example i have a stored procedure
p_rpt_supp_list in the server, but i found p_rpt_supp_list;1 in my ms
access.

Does anyone know how to solve this?

Thanks in advance,
Rudy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

The number is the version number see:
http://msdn.microsoft.com/library/d...create_4hk5.asp

This can be seen as the number column in syscomments see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sys-c_2y2b.htm

According to SQL Server 2000 Performance Tuning Technical Reference (P322)
;1 is assummed no number is stated. If version 1 is hard coded, then the
only way I can think of re-creating a version 1 is to drop all versions of
the procedure and recreate it (without a version number of explicitly at
version 1).

John

"rudy susanto" <rsantolee@.yahoo.com.sg> wrote in message
news:401f2a8d$0$70306$75868355@.news.frii.net...
> Hi All,
> I have a server that contains sqlserver 2000 and ms access 2000.
> Everything were working fine when i ran my adp file in the server side,
> and i faced some problem when i ran it on the client computer.
> After checking my adp file, i found all of my stored procedures added by
> comma and number at the behind. For example i have a stored procedure
> p_rpt_supp_list in the server, but i found p_rpt_supp_list;1 in my ms
> access.
> Does anyone know how to solve this?
>
> Thanks in advance,
> Rudy
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi John,

I never created any stored procedures using ; separator.

I always drop the procedure if it's exists before creating it.

The problem is office 2k (without service pack) but everything is
working fine in office xp.

Thank very much for your response John. :)

Warmest Regards,
Rudy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

I am not sure what is happening then as your stored procedures will
always be a version 1. You may want to try SQL Profiler and view what
commands are being sent to the server. If you can update/patch the
version of office it may help.

John

rudy <rudy@.mail.com> wrote in message news:<4026f768$0$193$75868355@.news.frii.net>...
> Hi John,
> I never created any stored procedures using ; separator.
> I always drop the procedure if it's exists before creating it.
> The problem is office 2k (without service pack) but everything is
> working fine in office xp.
> Thank very much for your response John. :)
> Warmest Regards,
> Rudy
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||rudy susanto (rsantolee@.yahoo.com.sg) writes:
> I have a server that contains sqlserver 2000 and ms access 2000.
> Everything were working fine when i ran my adp file in the server side,
> and i faced some problem when i ran it on the client computer.
> After checking my adp file, i found all of my stored procedures added by
> comma and number at the behind. For example i have a stored procedure
> p_rpt_supp_list in the server, but i found p_rpt_supp_list;1 in my ms
> access.

That may just be the way Access lists the procedures. As John said, the ;1
is there. Saying

EXEC my_proc;1

is the same as

EXEC my_proc

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ADP Parameterized list box stored proecedure /function

I am trying to populate a list box (MS Access - don't ask it is what my boss wants) with a table-valued function or a stored
procedure. The list box is used to navigate among records that populate the
form, so the same query can be used for the form and the list box thereby,
theoretically, reducing calls to the DB. The records are filtered on two
fields, we'll call them type (GUID) and name (nvarchar), whereby the values
are in two different controls on the form.

The form is using a function, fActiveCompanies, and the imput parameter
propety is completed. The form is able to navigate through the records with
no problem.

I am having difficulty create a SMOOTH method of populating the list box.
currently i am using a stored procedure that is called in VB and populates
the list box with a list of values.

I would prefer to not use a list of values and set the row source type to
Table/View/Stored Procedure.

Please point me in the correct direction for populaitng hte list box not
using a list of values... thank you

Access 2003 - Access Data Projects (ADP) - ADO
SQL Server 2000 (production) SQL Server Express 2005 (devlopment)

my current code for populating the list box is below.

Dim str As String: str = ""
Dim lst As String: lst = ""

Dim rds As ADODB.Recordset
Set rds = New ADODB.Recordset

Forms!frmcompanies!lstCompanies.RowSource = lst
str = "EXEC spActiveCompanies @.pIdTypeCompany='" &
Nz(Forms!frmcompanies!cboTblTypeEntity, "%") & "', @.pNameLegal='" &
Nz(Forms!frmcompanies!txtNav, "%") & "'"

rds.Open str, CurrentProject.Connection

Do Until rds.EOF

If lst = "" Then
lst = """" & rds(0) & """;""" & rds(1) & """"
Else
lst = lst & ";""" & rds(0) & """;""" & rds(1) & """"
End If

rds.MoveNext
Loop

Forms!frmcompanies!lstCompanies.RowSource = lst
Forms!frmcompanies!lstCompanies.Requery

rds.Close: Set rds = Nothing

Forms!frmcompanies.Requery

i mis-read one document, the parameters must be the same as the controls...

field = @.ComboBoxControlName

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

ADP is fine for smaller groups of people, and if you have a ready supply of people that can troubleshoot the applications if they have an issue. Visual Studio applications can scale much higher and are better supported.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

ADP or MDB - which is a better front end for SQL Server?

When Access 2000 and 2002 came out, Microsoft published information
suggesting that Access ADP Projects would be the preferred choice over an
Access MDB file as a front end for a SQL Server database.
More recently, however, several people have told me they actually prefer to
use MDB files as their front ends, while using the Enterprise Manager and
Query Analyzer in SQL Server to build all of their objects in the SQL Server
back end.
I recently attended one of the MS SQL Server Roadshows, and none of the
sessions I attended made any mention of front end development platforms,
which was somewhat surprising, since it would seem to be such an important
part of the development picture. Am I wrong in having the impression that
Microsoft is deemphasizing the benefits of using Access Projects over Access
MDB files?
I am just now getting around to adopting SQL Server for our databases, and
it would therefore be helpful to know which type of file would make a better
front end for SQL Server databases that don't require web access.
Any comments in this regard would be helpful.
Thanks in advance,
Paul
I have found that the effort required in putting together a good ADP is
substantially more significant than an MDB. But it has been worth it.
Example: I had a client using MDB/SQL and they had some very complex
reports that took anywhere from 5 minutes to 14 hours to run. Same reports
take from 5 seconds to 10 minutes in the ADP, with none of the underlying
code/queries changing. Speed of the SQL Server engine makes a lot of
difference. So does indexing (for mdb or adp).
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul Ponzelli" <pponzelliREMOVE@.ANTISPAMsurewest.net> wrote in message
news:OGFADYYUFHA.3944@.tk2msftngp13.phx.gbl...
> When Access 2000 and 2002 came out, Microsoft published information
> suggesting that Access ADP Projects would be the preferred choice over an
> Access MDB file as a front end for a SQL Server database.
> More recently, however, several people have told me they actually prefer
> to use MDB files as their front ends, while using the Enterprise Manager
> and Query Analyzer in SQL Server to build all of their objects in the SQL
> Server back end.
> I recently attended one of the MS SQL Server Roadshows, and none of the
> sessions I attended made any mention of front end development platforms,
> which was somewhat surprising, since it would seem to be such an important
> part of the development picture. Am I wrong in having the impression that
> Microsoft is deemphasizing the benefits of using Access Projects over
> Access MDB files?
> I am just now getting around to adopting SQL Server for our databases, and
> it would therefore be helpful to know which type of file would make a
> better front end for SQL Server databases that don't require web access.
> Any comments in this regard would be helpful.
> Thanks in advance,
> Paul
>
|||Hi Paul,
Using an .mdb on a local network has always worked fine for me. For
projects needed
across our wan, I use an .adp simply for the SQL Client/Server advantages.
A good way to look at it is for an .mdb query to run on someone's local
front end,
they pull the table data to them and query it locally. On a wan this is
unacceptable.
With an .adp, you can send the query string to the remote SQL server, it's
processed there,
and only the result set is returned. When you factor in triggers & stored
procs that run
at the server you can easily see the true benefit.
In my opinion, any new solution that is not exclusive to the branch running
it,
is developed using an .adp with SQL backend.
The RAD environment that Access provides, combined with the seemingly
limitless
potential to put together whatever specs are required, makes it my choice
for all
our wan solutions.
..aspx pages working against SQL backends takes it to the next level for
solutions
needing web access.
...just my humble opinion..
bob mcclellan
"Paul Ponzelli" <pponzelliREMOVE@.ANTISPAMsurewest.net> wrote in message
news:OGFADYYUFHA.3944@.tk2msftngp13.phx.gbl...
> When Access 2000 and 2002 came out, Microsoft published information
> suggesting that Access ADP Projects would be the preferred choice over an
> Access MDB file as a front end for a SQL Server database.
> More recently, however, several people have told me they actually prefer
> to use MDB files as their front ends, while using the Enterprise Manager
> and Query Analyzer in SQL Server to build all of their objects in the SQL
> Server back end.
> I recently attended one of the MS SQL Server Roadshows, and none of the
> sessions I attended made any mention of front end development platforms,
> which was somewhat surprising, since it would seem to be such an important
> part of the development picture. Am I wrong in having the impression that
> Microsoft is deemphasizing the benefits of using Access Projects over
> Access MDB files?
> I am just now getting around to adopting SQL Server for our databases, and
> it would therefore be helpful to know which type of file would make a
> better front end for SQL Server databases that don't require web access.
> Any comments in this regard would be helpful.
> Thanks in advance,
> Paul
>
|||On Thu, 5 May 2005 11:19:34 -0500, "Kevin3NF"
<KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:

>I have found that the effort required in putting together a good ADP is
>substantially more significant than an MDB. But it has been worth it.
>Example: I had a client using MDB/SQL and they had some very complex
>reports that took anywhere from 5 minutes to 14 hours to run. Same reports
>take from 5 seconds to 10 minutes in the ADP, with none of the underlying
>code/queries changing. Speed of the SQL Server engine makes a lot of
>difference. So does indexing (for mdb or adp).
Personally, I've always found the effort required to optimize MDB reports is
well worth it compared to the constant battle required to develop and maintain
an ADP. I've worked extensively on one large ADP project, and it was torture.
Previously, I worked on several MDB front-ends to MS SQL Server that went much
more smoothly. Basically, to make an MDB work well with SQL Server, you have
to know some tricks and work-arounds, but they're not too arduous, and they
pretty much always work as expected. Note that you don't have to rewrite all
your Access queries as SPs and view, just the few that JET can't translate
properly for you without help.
To get an ADP to just work as advertized requires a vast number of
work-arounds made more difficult be the fact that ADP and ADO try so hard to
think for you that you're not allowed to do the thinking when you know how you
want it to do its work. Furthermore, each version of Access has such
different quirks with ADPs that work-arounds for one might break the app in
the other. Some bugs have even been fixed, then reappeared again more than
once. ADPs also became less stable, not more in Access 2002, plus Microsoft
seems to have removed almost all resources from improving and maintaining ADP
capability because it has never taken off.
|||My thanks to all the contributors to this thread. The information you have
provided will be very helpful to us.
I'm surprised to read that even with mdb files, there are issues that have
to be dealt with in getting it to work well with SQL Server. It makes me
wonder whether there are better applications than Access to use as a
front-end for SQL Server. If no version of Access is entirely seamless, is
there some other application that would be a preferred front-end for SQL
Server?
Thanks again in advance,
Paul
|||Hi Paul,
I personally write software in access/vb6/vb.net/asp.net and they are all
good for different types of projects, what I would say about access projects
is in my view they are the quickest to write by quite a large margin, it is
a system developed as a database program and as such is highly optimised to
work with databases. VB6 working with SQL is quite straight forward, takes
longer to write than the equivalent access program, in that system you have
a much larger array of components at your disposal so your application can
seem much richer in functionality. VB.Net/C# in my view the most robust
language I write in, the exception handling is so much better than previous
versions of vb/vba. The main point here is all these systems are using very
similar technologies under the hood to access databases, either
DAO/ADO/ADO.NET the choice really is a personal one as all the above systems
can produce relatively fast and reliable code. If I had to recommend any one
of them for a brand new project my choice for a standard workstation
installed application working with SQL server would be VB.NET, currently the
beta version of VS2005 and SQL 2005 are what all my new projects are being
written in with both programs due for release sometime this year (I hope).
About 50% of my development time is spent writing/re-writing access mdb/adp
projects and personally I don't see this type of system being phased out by
Microsoft in the near future as I believe Access is one of the best programs
that Microsoft has ever written (I know a lot of people that would disagree
with that comment).
Just my thoughts...
Regards
Alex White MCDBA MCSE
http://www.intralan.co.uk
"Paul Ponzelli" <begone@.spam.forever> wrote in message
news:%23VrhFapUFHA.2172@.tk2msftngp13.phx.gbl...
> My thanks to all the contributors to this thread. The information you
> have
> provided will be very helpful to us.
> I'm surprised to read that even with mdb files, there are issues that have
> to be dealt with in getting it to work well with SQL Server. It makes me
> wonder whether there are better applications than Access to use as a
> front-end for SQL Server. If no version of Access is entirely seamless,
> is
> there some other application that would be a preferred front-end for SQL
> Server?
> Thanks again in advance,
> Paul
>
|||Thanks for adding these further thoughs to the conversation, Alex. They're
very helpful and also encouraging.
Another question - are you finding that the beta versions of both VS2005 and
SQL 2005 are far enough along that you're actually able to develop a
production application with them?
|||Thank you for this information, Sylvain.
I'm puzzled about one thing you said in your message. When you said
"MS is in the process of slowly killing . . . ADO and Recordsets"
Could you explain a bit about what that means? I'm confused when you say
that, because there is an ADO.net, and it would seem that if MS were phasing
out ADO, I wouldn't have expected them to develop a methodology to work with
ADO objects in the dot net framework. Could you elaborate a bit further on
this?
Thanks for your help on this.
Paul
|||Sylvain, since I'm new to both SQL Server and dot net, I need to ask a very
basic question just to make sure I'm understanding the implication of what
you're saying.
Are you saying, in effect, that dot net is likely to become the platform of
choice in the future for both OS and web applications, and that mdb and adp
files are likely to be deprecated or deemphasized?
|||> I would say (given the newest beta's) MS are a few months away from
> releasing both products.
At the SQL Server Roadshow I attended last week, someone asked the head of
the SQL Server 2005 development team when it would be released, and he
answered by saying it was "aptly named." I took that to mean that it would
be released sometime in 2005, but he wouldn't commit to a more specific
timeframe. He did say, however, that Barnes & Noble has actually been using
SQL Server 2005 in production for some time now.

ADP files?

Hi,
I just upsized my Access 2000 database to MSDE (which I intend to upsize
to SQL Server in a few w

s) and got a .adp file from a .mdb file. How
do I connect to this .adp file from Visual Basic? Can I distribute this
file easily like Access? Please help, I have no idea what to do with
MSDE and how to connect DSN-LESSly to this file.
regards,
Steve"Steve" <stephen@.anu.edu.au> schrieb im Newsbeitrag
news:%23dm9qBUUFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I just upsized my Access 2000 database to MSDE (which I intend to upsize
> to SQL Server in a few w

s) and got a .adp file from a .mdb file. How do
> I connect to this .adp file from Visual Basic? Can I distribute this file
> easily like Access? Please help, I have no idea what to do with MSDE and
> how to connect DSN-LESSly to this file.
>
> regards,
> Steve|||You can easily share that file, but would i make sense ? (There must be
alway a connetion between ADP and aAccess to get the data from the MSDE)
Wouldnt that be a better way just to make your way directly to SQL Server ?
Or do you need some special features (Form, Reports) from Access ?
Jens Suessmeyer.
"Steve" <stephen@.anu.edu.au> schrieb im Newsbeitrag
news:%23dm9qBUUFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I just upsized my Access 2000 database to MSDE (which I intend to upsize
> to SQL Server in a few w

ADP dont shows table from MSDE

Hi,

I created one blank ADP to connect default tempdb database on MSDE on server ASHFAQUEPC (name of my msde server). Tested the connection was successful.

I created a table called Testtable from command prompt (MSSQL/Binn) in default tempdb of MSDE using following lines:

Binn>osql/U sa mypassword

1> use tempdb
1> go
1> Create table Testtable (Cnum integer NOT NULL PRIMARY KEY, City Varchar(15))
1> go

Table Created successfully then I added 2-3 records using following lines;

1> INSERT INTO testable (cnum, city)
1> values (1, abc)
1> go

1 Record affected and I did same to add 2-3 more records. Then I used following lines to see all the records I entered.

1> select * from testable
1> go

And a list of records displayed at command prompt. After exit from command prompt I found a Testtable appeared in the above ADP.

But next day when I opened my pc and opened ADP, I did not found the Testtable even though I re-connected to tempdb on MSDE from File menu. I reached then to command prompt to see whether the table itself is existing. Strange it was not there.

Can somebody tell me how can I get connected to testable automatically when I open my ADP? Where might have gone wrong? Do I need to create table again and input records?
I want to make sure before I do anything with my real dbs.

Any help would be highly appreciated.

With kind regards,
Ashfaque :confused:tempdb is recreated everytime sql server restarts. msde restarts when the last connection is closed. create a database with the create database command and create your table in there.|||Thanks Thrasymachus,

IC,
I will create my own db from scratch.

Thanks again....

Wiith kind regards,
Ashfaque|||You should definitely not be mucking around in TEMPDB, MASTER, or MSDB. Even MODEL should not be modified without good cause. Leave the system databases alone, and I'd advise you to read the manual BEFORE trying to fly the plane.|||What the blind dude is saying (with his usual grace and elegance), is that you really need to create a datbase for your purposes, and create all of you objects in there.

tempdb is a critical part of the sql server architecture that allows it to manage your "temporary" objects you can create. Temp tables, derived tables, etc. You should not interfere with it's operation.

btw, have you had a look at SQL Server Express and XM Manager?

It's still beta, but it should be coming out full time soon.

http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm|||Thanks blindman & Brett Kaiser for your advice.

I was not aware of behavior of these 4 default models on MSDE.

I upsized my MDB and it successfully created ADP and found steady. I can now go on to take further actions over it. I am close to mid-level carrier in handling MDBs in form of FE and BE. But as a little advance step, I used MSDE and I hope I could deal with it successfully with the help you Genius guys.
btw, have you had a look at SQL Server Express and XM Manager?
No, I did not take a look at SQL Server Express and XM Manager but I will do so.

Thanks again for you people..

With kind regards,
Ashfaque|||One more question...

I upsized the tables on MSDE successfully keeping the current db as MDB.

Why I dont know , the record in my subform is appearing but I can not enter new records any more..(Be is on MSDE). If my tables are on local (Not on MSDE server) it works fine.

Any idea ...?

With kind regards,

Ashfaque|||Could be a permissions issue with your login. Do you get any error messages?

ADP connection to SQL

I would like any advice on connecting an Access Project to SQL Server 2000. Authentication is in mixed mode and user enter a name and password. When he click cancel twice, it will appear error - he enter without check a name and password. How I can prevent that?Any chance of changing the authentication to Windows only?|||

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

ADP connection to SQL

I don't understand what is happening in your scenario - can you explain with more details how you set up your project to connect to SQL Server?

Thanks
Laurentiu

Tuesday, March 6, 2012

adp connection to msde and exclusive rights

I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
but I get the following error when trying to create a view:
"You do not have exclusive access to the database at this time. Your design
changes will not be saved."
how can I get ability to create views ?
I'd recommend not using Access as a development tool for SQL Server
objects. Use the Query Analyzer or Enterprise Manager instead. The
error message you're getting is an Access error, not a SQL Server
error.
--Mary
On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:

>I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
>(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
>but I get the following error when trying to create a view:
>"You do not have exclusive access to the database at this time. Your design
>changes will not be saved."
>how can I get ability to create views ?
>
|||Hi Mary:
Thanks for replying (I have your book, Developer's guide to SQL Server).
Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so I
was hoping to use access as a front end.
is there any way access can be a front end to msde ?
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com... [vbcol=seagreen]
> I'd recommend not using Access as a development tool for SQL Server
> objects. Use the Query Analyzer or Enterprise Manager instead. The
> error message you're getting is an Access error, not a SQL Server
> error.
> --Mary
> On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
rights[vbcol=seagreen]
design
>
|||MSDE is not a development platform, it's a deployment platform. I would
suggest that you spend the <$50 required to purchase SQL Server Developer
Edition. It contains everything you'll need to develop for the SQL Server
and MSDE platforms (really the same thing).
I'll even go out on a limb and predict that Mary will say the same thing.
:-)
Yes, you can use Access as a front end to MSDE, but you'll likely find the
tools she mentioned easier to use to create and manage your database.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJS" <nospam@.here.com> wrote in message
news:10dm523btum1rd5@.corp.supernews.com...
> Hi Mary:
> Thanks for replying (I have your book, Developer's guide to SQL Server).
> Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so
I[vbcol=seagreen]
> was hoping to use access as a front end.
> is there any way access can be a front end to msde ?
>
>
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com...
2000
> rights
> design
>
|||I just want to create a view...
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OBa0OViWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> MSDE is not a development platform, it's a deployment platform. I would
> suggest that you spend the <$50 required to purchase SQL Server Developer
> Edition. It contains everything you'll need to develop for the SQL Server
> and MSDE platforms (really the same thing).
> I'll even go out on a limb and predict that Mary will say the same thing.
> :-)
> Yes, you can use Access as a front end to MSDE, but you'll likely find the
> tools she mentioned easier to use to create and manage your database.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "TJS" <nospam@.here.com> wrote in message
> news:10dm523btum1rd5@.corp.supernews.com...
so
> I
> 2000
>

Adp And Sql Server 2005

We upgraded SQL Server 2000 to 2005 We have Access 2003 as the user interface. We are having issues with permission now and when I go into the ADP to create a function or a Stored Procedure it tells me that "the version of Microsoft Office Access doesnt support design changes with the version of Microsoft SqL Server your access projet is connected to. See the Mircrosoft Office Update Web site for the latest information and downloas (on the Help menu, click office on the Web). your design changes will not be saved".

And then when I get inside the design view, it says CAUTION: NEW DATABASE FEATURES NOT SUPPORTED. "You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems.

Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected.

You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools."

Can anyone help pleasewell have you checked for an access 2003 service pack? that is where i would start. otherwise you might have to abandon the Access 2003 crutch and do your development directly in SQL Server <gasp>.|||Thank you I dont mind going through development directly its just that before I at least had the option available, I use to do both but now looks like I cant.|||You may be running into the issue described at the end of the quote in post 6:

http://www.dbforums.com/showthread.php?t=1620139

Thursday, March 8, 2012

ADP/ADE Permissions Issue

ADP vs. MDB: Speed

ADP vs. MDB: Speed

Adp sql - help

ADP revision numbers

ADP Report Problems

ADP project on SQL 2000 sp4

ADP project on SQL 2000 sp4

ADP project on SQL 2000 sp4

ADP Problem

ADP Parameterized list box stored proecedure /function

ADP or VS

ADP or VS

ADP or MDB - which is a better front end for SQL Server?

ADP files?

ADP dont shows table from MSDE

ADP connection to SQL

ADP connection to SQL

Tuesday, March 6, 2012

adp connection to msde and exclusive rights

Adp And Sql Server 2005

SQL Adding a User account

Blog Archive

About Me