MDB or ADP??
Tony
Access project file as opposed to using a standard .MDB
with ODBC linked tables.
The backend in each case will be MS SQL Server 2000.
I need to design an App that will have a backend of 7GB
with max concurrent users' of 20. Total users = 50. The
App will be Read-Only.
TIA
Tony
Lyle Fairfield
@posting.google.com:
it might be helpful if you were to read all this series:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnapg/html/apg01.asp
(to follow the link you need to remove the line break, of course)
--
Lyle
Albert D. Kallal
certainly makes sense to use a ADP project. You get a nice table manager,
and also the ER tools in ms-access will thus work with SQL server. That
alone is worth the price.
Further, you have more ability to use and setup stored procedures.
The only real advantages of using a mdb is that you can work with local
tables, and that is only an advantage if your application needs it. (in
other words, with a adp, you are forced to come with designs that don't need
local tables).
The only real reason to use a mdb to sql-server via odbc is if you have a
existing application. The other issue is perhaps the learning curve to use a
adp over a mdb also.
If you have a choice, and you are asking which road to take? I would
certainly vote for the adp project. You should go with office xp if you make
this jump.
If you had a existing application in production with 20 users, and you
wanted to expand the user base, and upgrade to SQL-server from JET, then you
kind of have to go ODBC. It is less work. Re-writing to a ADP could then
perhaps be a goal, but most find the performance via odbc so good anyway.
So, the real key here is what you have already. Since you can choose from
the start, then a adp seems the way to go. Since a free copy of SQL-server
is available on the office cd, then you have all the tools you need to start
a very scalable application. You can develop on your pc, and then deploy to
the real big SQL server, and everything will be 100% compatible. I have to
applaud the fact that MS gives this free data engine with office. It really
is a incrediable feaure.
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn
David W. Fenton
<%wh0a.195007$H7.73...@news2.calgary.shaw.ca>:
>If you are building the application around SQL server, then it
>most certainly makes sense to use a ADP project. You get a nice
>table manager, and also the ER tools in ms-access will thus work
>with SQL server. That alone is worth the price.
>
>Further, you have more ability to use and setup stored procedures.
>
>The only real advantages of using a mdb is that you can work with
>local tables, and that is only an advantage if your application
>needs it. (in other words, with a adp, you are forced to come with
>designs that don't need local tables).
>
>The only real reason to use a mdb to sql-server via odbc is if you
>have a existing application. The other issue is perhaps the
>learning curve to use a adp over a mdb also.
I believe that Steve Jorgensen would have some things to say about
all of the above. He has been reporting all sorts of gotchas with
MDBs (he's been doing SQL Server for years, so these all have to do
with problems with ADPs in particular, not with his lack of
understanding of how to work with SQL Server). He has said that he
can't see much advantage to an ADP.
Albert, how many ADP apps have you built?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Tom van Stiphout
wrote:
I agree with your overall recommendation, but let there be no doubt
about it: A2K is a very poor design-time client for SQL Server 2000.
Views don't work properly - and you will get a message about that, and
I have had a hard time with some stored procedures as well, especially
if I use dbo.MyProcedureName as the procedure name.
The stored proc editor is pathetic - not even a single level of undo -
worse than a standard edit control. Unfortunately the one in SQL
Server Enterprice Manager is not much better. A modal procedure editor
- aarrrcchhhh - Fire that MSFT program manager.
There is a lot to be desired for tools support.
-Tom.
>If you are building the application around SQL server, then it most
>certainly makes sense to use a ADP project. You get a nice table manager,
>and also the ER tools in ms-access will thus work with SQL server. That
>alone is worth the price.
>
<clip>
Lyle Fairfield
news:9319E28F6df...@24.168.128.78:
My grandfather's name was Albert. (AL-BEAR) ... so may I answer? Yes? ...
Great! Seven for pay. Others for play. And I love 'em all!
--
Lyle
John Winterbottom
news:6lo34vsolcthor2s2...@4ax.com...
> On Wed, 05 Feb 2003 23:56:43 GMT, "Albert D. Kallal" <kal...@msn.com>
> wrote:
>
> I agree with your overall recommendation, but let there be no doubt
> about it: A2K is a very poor design-time client for SQL Server 2000.
> Views don't work properly - and you will get a message about that, and
> I have had a hard time with some stored procedures as well, especially
> if I use dbo.MyProcedureName as the procedure name.
> The stored proc editor is pathetic - not even a single level of undo -
> worse than a standard edit control. Unfortunately the one in SQL
> Server Enterprice Manager is not much better. A modal procedure editor
> - aarrrcchhhh - Fire that MSFT program manager.
Query Analyzer is the best of the MS query editing tools IMO.
Albert D. Kallal
>
> Albert, how many ADP apps have you built?
None that I have built are in clients hands.
There has certainly been some posts on both sides of the ADP issue. The
general feed back is that a2000 is quite bad for ADP projects. The XP
version is much better.
Thus, my comments are mostly based on what I have read here....
Steve Jorgensen
The idea of an ADP was to use ADO as the sole access mechanism
skipping ADO, JET, and ODBC, and to create a tighter integration
between Access and SQL Server for better performance, etc. Tables are
now directly accessed from the back-end, queries you create are now
Views or Stored procedres on the back-end, etc. To a great extent,
they've succeeded, but I have found the following issues abong
others...
1. Access ADPs force you to go through a special ADO provider on your
way to the actual SQL Server provider. This extra provider layer
essentially adds back one of the layers that we're supposed to be
eliminating. Furthermore, this provider is buggy as all damn hell,
has different bugs every single release, has undocumented new features
that totally change behavior from one version to the next causing old
code to misbehave, etc. In short, ADP behavior is still quite
unstable and unpredictable from one version or service pack to the
next.
2. Currently, ADPs have a bug that prevents editing through a view
that joins 2 or more tables with TIMESTAMP columns. This means that,
although ADPs supposedly improve performance, you can't currently use
one of the most important performance-improving techniques with them.
Actually, I think this one is OK with the most recent A2K service
pack, but not with any service pack of Access XP.
4. ADPs insist on making extra round trips to retrieve information
about back-end objects for almost everything you do since information
about those objects is not cached in front-end connection objects as
it would be in an MDB, and ADO and access both want to know everything
they can about these objects.
5. ADP forms always load recordsets of Updateable Snapshot type.
Sometines that's a good thing, but often it's a very bad thing. For
instance, in an MDB, if you have a form based on a record source with
an OLE field, Access only reads the field for rows that are actually
displayed or read from code. In an ADP, it's going to read all of
them into RAM! If you are, for instance, getting a company logo for
invoices by joining to a customers table, you are in for a nasty wait.
Although ADPs also automatically limit the size of some recordsets by
doing subform filtering on the server side, not the client, etc. This
can still be a performance hassle you may well have to work around.
6. Security best practices are a bear to work out, and you can't
really do it right at all unless you are using SQL Server 2000. You
basically have to create a single wrapper view with the WITH VIEW
METADATA option set for each back-end table (never joins of tables) so
you can use instead-of triggers for security, then you can use views
with joins of those. Since Access insists on trying to go around your
views and updating the tables behind them, this is the only way to
trick it and not cause errors. Using a view WITH METADATA on a join
of 2 ore more tables results in metadata that makes it unusable for
editing by ADO since it always reports either too many PK columns or
noe at all. Note that you don't have to jump through these hoops if
you insist on doing all updates via stored procedures, but then there
is no obvious reason why you would think of using an ADP.
7. Over all, AXP is not better for ADPs than A2K. In some ways it's
better, and in some it's much worse. Some of the new bugs that have
made my life difficult have been introduced newly in the ADO provider
used as a wrapper by Access XP (such as not trapping SQL errors
properly if warnings are also generated first). Many of the bugs are
not even acknowledged by Microsoft, though I know they are aware, or
some were previously reported as fixed in A2K service packs and have
reappeared in XP.
8. I had expected that ADPs would take care of the issue with MDBs
where a second connection to the server must be opened for some
operations. Actually, an ADP opens 2 connections right away and keeps
them open.
9. In an MDB, you have a query engine ready to go in the front-end
for doing heterogeneous joins of data sources that may include
entities other than those via your primary server connection - it's
not so easy in an ADP. It has been pointed out that you can ask SQL
Server to query other data sources, but you must have appropriate
permissions, you must specify the path to the data relative to the
server, not your workstation, and the proper driver must be loaded
onto the server by an administrator.
On the good side:
1. ADPs are a great prototyping tool, especially for those just
getting started with Microsoft SQL Server. Although the query editor
is useful only for very simple stored procedures and views, that's
about 80 percent of the ones you'll write, and by looking at the SQL
generated, you'll learn how it's done. You'll also see some important
details such as always including SET NOCOUNT ON at the beginning of
any stored procedure other than a single select statement returning
rows to the client (ADO does not handle errors correctly otherwise -
by design, not a bug - similar with DAO). I believe that Access 11 is
supposed to have color coding and auto-sense for writing SQL.
2. A few client-server best practices are embedded into the design
such as automatically limiting the maximum number of rows that can be
returned by a recordset and querying only relevant rows in correlated
subforms.
3. If you are using SQL Server login security, you don't get
occasionally asked to re-enter the login information as you would with
an MDB unless you store the name/password in your links and
pass-through queries.
4. It's somewhat easier for multiple developers to work together on a
project since updates to queries and table design are immediately
available to others without relinking tables ore importing new
querydefs.
5. Passing parameters to stored procedures and other SQL Queries was
a major hassle in MDBs and DAO, but it's no big deal at all in ADPs
and ADO. ADO automatically handles problematic issues such as
properly delimiting arguments, escape coding of special characters,
proper indication of nulls, etc. This can be handled by writing
procedures an religeously using them in MDBs, but many less
experienced developers do not know this, and bugs and security
weaknesses are the result (also a problem with ASP, so always using
ADO from ASP is a good idea).
CDB
Clive
"Steve Jorgensen" <nos...@nospam.nospam> wrote in message
news:uvv44v49a3vvt3qr8...@4ax.com...
> Responding to your post and to replies so far...
>
> The idea of an ADP was to use ADO as the sole access mechanism
> skipping ADO, JET, and ODBC, and to create a tighter integration
> between Access and SQL Server for better performance, etc. Tables are
> now directly accessed from the back-end, queries you create are now
> Views or Stored procedres on the back-end, etc. To a great extent,
> they've succeeded, but I have found the following issues abong
> others...
...
David W. Fenton
>"David W. Fenton" <dXXXf...@bway.net
>>
>> Albert, how many ADP apps have you built?
>
>None that I have built are in clients hands.
>
>There has certainly been some posts on both sides of the ADP
>issue. The general feed back is that a2000 is quite bad for ADP
>projects. The XP version is much better.
>
>Thus, my comments are mostly based on what I have read here....
Have you read Steve's comments? He was using A2K2, not A2K.
His problems seem quite significant to me, and your recommendation
of ADPs does not account for those kinds of problems.
Tony
After having read all the pro's and con's from the learned users', I
get the distinct feeling that ADP is very much a "suck and see"
product. My time frame for development of the App is tight and the
deadline is set in concrete. Therefore MDB sounds like the safer
option, given my experience with developing ADP is nill.
Thank you very much for your excellent feedback.
Tony Paolone
Steve Jorgensen <nos...@nospam.nospam> wrote in message news:<uvv44v49a3vvt3qr8...@4ax.com>...
Ted Theodoropoulos
>
> way to the actual SQL Server provider.
Are you refering to the MSDataShape provider?
> 2. Currently, ADPs have a bug that prevents editing through a view
> that joins 2 or more tables with TIMESTAMP columns. This means that,
> although ADPs supposedly improve performance, you can't currently use
> one of the most important performance-improving techniques with them.
What technique are you referring to? I know how to use TIMESTAMPS for
row versioning but I have a feeling you're referring to something
else.
> 4. ADPs insist on making extra round trips to retrieve information
> about back-end objects for almost everything you do since information
> about those objects is not cached in front-end connection objects as
> it would be in an MDB, and ADO and access both want to know everything
> they can about these objects.
Are you referring to the ADP querying the sysobjects table at start up
and various other times? I agree that it's lame that ADPs do that but
since Jet isn't in the picture, how would Access store and retrieve
that metadata? I guess it could store it in a local XML recordset or
something but these roundtrips are usually pretty inexpensive. I
suppose they could add up for a large number of users but IMO ADPs
aren't extremely scalable anyway.
It's humorous that ADPs go after metadata by querying the sysobjects
table directly instead of using the INFORMATION_SCHEMA like MS
recommends. Querying the sysobjects table is discouraged by MS
because it's structure may change from version to version so they
recommend you use the INFORMATION_SCHEMA but they don't follow their
own advice. You'll notice they do the same thing in SQL when you have
it script an object.
> 5. ADP forms always load recordsets of Updateable Snapshot type.
> Sometines that's a good thing, but often it's a very bad thing. For
> instance, in an MDB, if you have a form based on a record source with
> an OLE field, Access only reads the field for rows that are actually
> displayed or read from code. In an ADP, it's going to read all of
> them into RAM! If you are, for instance, getting a company logo for
> invoices by joining to a customers table, you are in for a nasty wait.
Are you referring to a form with just a table name in the datasource
property or do ADPs do that even if you specify column names in the
view (or SQL statement) your form is based on?
> 6. Security best practices are a bear to work out, and you can't
> really do it right at all unless you are using SQL Server 2000. You
> basically have to create a single wrapper view with the WITH VIEW
> METADATA option set for each back-end table (never joins of tables) so
> you can use instead-of triggers for security, then you can use views
> with joins of those. Since Access insists on trying to go around your
> views and updating the tables behind them, this is the only way to
> trick it and not cause errors. Using a view WITH METADATA on a join
> of 2 ore more tables results in metadata that makes it unusable for
> editing by ADO since it always reports either too many PK columns or
> noe at all.
I agree that you are pretty much forced to give users read/write
permissions to the tables. It is recommeded that users do not have
update permissions to the tables and instead update thru views or SPs
(which is even better). I haven't done this yet but I would think
that using application roles and giving the application the
permissions to the tables and restricting access to the db window
isn't a bad alternative. Thoughts on that?
The one gotcha in that scenario (and this one really pisses me off) is
that even if you use an unbound ADP and set your connection at run
time and specify the application name in the connection string to
"Ted's Application", Access creates add'l connections and changes the
application name back to "Microsoft(R) Access." As a result, you are
forced to give Access the permissions to the tables. I would assume,
of course, that not just anyone with Access could connect to the db
and gain access to the application role because app roles require a
password.
> Note that you don't have to jump through these hoops if
> you insist on doing all updates via stored procedures, but then there
> is no obvious reason why you would think of using an ADP.
Indeed. You would be in an unbound environment in that scenario and
ADPs do lose some of there appeal.
> 7. Over all, AXP is not better for ADPs than A2K. In some ways it's
> better, and in some it's much worse. Some of the new bugs that have
> made my life difficult have been introduced newly in the ADO provider
> used as a wrapper by Access XP (such as not trapping SQL errors
> properly if warnings are also generated first). Many of the bugs are
> not even acknowledged by Microsoft, though I know they are aware, or
> some were previously reported as fixed in A2K service packs and have
> reappeared in XP.
I haven't done anything with AXP yet so I don't know anything about
ADPs there. Am I safe to assume that you're not a fan? Are A2K ADPs
less buggy?
> 8. I had expected that ADPs would take care of the issue with MDBs
> where a second connection to the server must be opened for some
> operations. Actually, an ADP opens 2 connections right away and keeps
> them open.
That does suck but I've never seen one open more than three. Have
you? QA also opens 2 connections if you use the Object Browser.
> On the good side:
> 1. ADPs are a great prototyping tool, especially for those just
> getting started with Microsoft SQL Server. Although the query editor
> is useful only for very simple stored procedures and views, that's
> about 80 percent of the ones you'll write, and by looking at the SQL
> generated, you'll learn how it's done. You'll also see some important
> details such as always including SET NOCOUNT ON at the beginning of
> any stored procedure other than a single select statement returning
> rows to the client (ADO does not handle errors correctly otherwise -
> by design, not a bug - similar with DAO).
Is there a KB article on this? Is this what you're referring to?
http://support.microsoft.com/default.aspx?scid=kb;en-us;195491
> I believe that Access 11 is
> supposed to have color coding and auto-sense for writing SQL.
Intellisense would make life much easier when typing T-SQL. Color
coding is already available. I have a buddy at MS who wan't sure
about the Intellisense option but said that XDocs (now called
InfoPath) are pretty cool.
http://www.microsoft.com/office/preview/infopath/default.asp
Steve Jorgensen
Theodoropoulos) wrote:
>Steve Jorgensen <nos...@nospam.nospam> wrote in message
>>
>> 1. Access ADPs force you to go through a special ADO provider on your
>> way to the actual SQL Server provider.
>
>Are you refering to the MSDataShape provider?
In Access 2000, it's the MSDataShape provider. In Access 2002, it's a
provider specific to Access/ADP and is not the MSDataShape provider.
>
>> 2. Currently, ADPs have a bug that prevents editing through a view
>> that joins 2 or more tables with TIMESTAMP columns. This means that,
>> although ADPs supposedly improve performance, you can't currently use
>> one of the most important performance-improving techniques with them.
>
>What technique are you referring to? I know how to use TIMESTAMPS for
>row versioning but I have a feeling you're referring to something
>else.
No, I'm referring to using them for row versioning.
>
>> 4. ADPs insist on making extra round trips to retrieve information
>> about back-end objects for almost everything you do since information
>> about those objects is not cached in front-end connection objects as
>> it would be in an MDB, and ADO and access both want to know everything
>> they can about these objects.
>
>Are you referring to the ADP querying the sysobjects table at start up
>and various other times? I agree that it's lame that ADPs do that but
>since Jet isn't in the picture, how would Access store and retrieve
>that metadata? I guess it could store it in a local XML recordset or
>something but these roundtrips are usually pretty inexpensive. I
>suppose they could add up for a large number of users but IMO ADPs
>aren't extremely scalable anyway.
It seems to do it a LOTS of other times. Metadata could be stored
locally in links much like DAO does, though ADO would have to be
enhanced to handle this. If this were done, one could avoid having to
retrieve the info from the server repeatedly, and one could override
problematic behavior such as having the Clustered index always used as
if it were the primary key.
...
>> 5. ADP forms always load recordsets of Updateable Snapshot type.
>> Sometines that's a good thing, but often it's a very bad thing. For
>> instance, in an MDB, if you have a form based on a record source with
>> an OLE field, Access only reads the field for rows that are actually
>> displayed or read from code. In an ADP, it's going to read all of
>> them into RAM! If you are, for instance, getting a company logo for
>> invoices by joining to a customers table, you are in for a nasty wait.
>
>Are you referring to a form with just a table name in the datasource
>property or do ADPs do that even if you specify column names in the
>view (or SQL statement) your form is based on?
I'm speaking of cases where you -do- want the OLE data, but you don't
want it loaded up-front for all records because you won't necessarily
be viewing all the records. I'm not saying ADPs load data not
indicated in the query.
>> 6. Security best practices are a bear to work out, and you can't
>> really do it right at all unless you are using SQL Server 2000. You
>> basically have to create a single wrapper view with the WITH VIEW
>> METADATA option set for each back-end table (never joins of tables) so
>> you can use instead-of triggers for security, then you can use views
>> with joins of those. Since Access insists on trying to go around your
>> views and updating the tables behind them, this is the only way to
>> trick it and not cause errors. Using a view WITH METADATA on a join
>> of 2 ore more tables results in metadata that makes it unusable for
>> editing by ADO since it always reports either too many PK columns or
>> noe at all.
>
>I agree that you are pretty much forced to give users read/write
>permissions to the tables. It is recommeded that users do not have
>update permissions to the tables and instead update thru views or SPs
>(which is even better). I haven't done this yet but I would think
>that using application roles and giving the application the
>permissions to the tables and restricting access to the db window
>isn't a bad alternative. Thoughts on that?
I suppose it could work, but many DBAs would balk at that. The
security of the database, at that point is relying on code in a
front-end that the DBA does not control. The code must be trusted not
to expose the application password and not to have security holes
allowing users to do things they shouldn't.
I have reason to be suspicious of the reliability of this approach
without some pretty good testing first. For each form, one would have
to open a recordset in code using a connection opened in code using
the same provider that Access does, then set the form's recordset to
that. Supposedly, this works, but based on my experiments with
disconnected recordset, I'm not sure it would be reliable. For
instance, often when you try to filter a form based on a disconnected
recordset, Access will reconnect it! (not .ServerFilter, but .Filter)
If Access does the same with any recordset not connected to the
default connection, that would be a problem.
>The one gotcha in that scenario (and this one really pisses me off) is
>that even if you use an unbound ADP and set your connection at run
>time and specify the application name in the connection string to
>"Ted's Application", Access creates add'l connections and changes the
>application name back to "Microsoft(R) Access." As a result, you are
>forced to give Access the permissions to the tables. I would assume,
>of course, that not just anyone with Access could connect to the db
>and gain access to the application role because app roles require a
>password.
I assumed you meant an application pseudo-user, not the application
itself. I didn't know you could grant permissions based on the
application string, but it I don;t see how that would be very secure
anyway since there's no password associated with an application name.
>> Note that you don't have to jump through these hoops if
>> you insist on doing all updates via stored procedures, but then there
>> is no obvious reason why you would think of using an ADP.
>
>Indeed. You would be in an unbound environment in that scenario and
>ADPs do lose some of there appeal.
>
>> 7. Over all, AXP is not better for ADPs than A2K. In some ways it's
>> better, and in some it's much worse. Some of the new bugs that have
>> made my life difficult have been introduced newly in the ADO provider
>> used as a wrapper by Access XP (such as not trapping SQL errors
>> properly if warnings are also generated first). Many of the bugs are
>> not even acknowledged by Microsoft, though I know they are aware, or
>> some were previously reported as fixed in A2K service packs and have
>> reappeared in XP.
>
>I haven't done anything with AXP yet so I don't know anything about
>ADPs there. Am I safe to assume that you're not a fan? Are A2K ADPs
>less buggy?
At this point, the latest service pack of A2K appears to be less
buggy, though XP adds some neat new capabilities. For new apps, I'd
think about XP if implemetation is a way off, and MS will probably
have fixed more bugs and quirks. I definitely would -NOT- upgrade a
working ADP app to A2K2. You'll spend months doing regression testing
and changing to things work right with the differing bugs, quirks, and
undocumented features (features that apply whether you asked for them
or not).
>> 8. I had expected that ADPs would take care of the issue with MDBs
>> where a second connection to the server must be opened for some
>> operations. Actually, an ADP opens 2 connections right away and keeps
>> them open.
>
>That does suck but I've never seen one open more than three. Have
>you? QA also opens 2 connections if you use the Object Browser.
No, but I haven't spent much time looking into that.
>> On the good side:
>> 1. ADPs are a great prototyping tool, especially for those just
>> getting started with Microsoft SQL Server. Although the query editor
>> is useful only for very simple stored procedures and views, that's
>> about 80 percent of the ones you'll write, and by looking at the SQL
>> generated, you'll learn how it's done. You'll also see some important
>> details such as always including SET NOCOUNT ON at the beginning of
>> any stored procedure other than a single select statement returning
>> rows to the client (ADO does not handle errors correctly otherwise -
>> by design, not a bug - similar with DAO).
>
>Is there a KB article on this? Is this what you're referring to?
>
>http://support.microsoft.com/default.aspx?scid=kb;en-us;195491
That article is so poorly worded that I can't tell. Basically, as
soon as you execute a statement in a SQL batch that returns a count of
rows affected, ADO thinks this indicates a recordset being returned.
Any errors generated after that are presumed to apply to some
additional recordset, so they are associated with that recordset, not
with the command. Thus you won't get an error raised unless and until
you retrieve a recordset or retrieve the next recordset. Always
putting SET ROWCOUNT OFF at the beginning of the procedure or batch
avoids this issue.
Ted Theodoropoulos
> No, I'm referring to using them for row versioning.
Do you manage your own concurrency in bound ADPs? How do TIMESTAMPS
improve performance?
> I suppose it could work, but many DBAs would balk at that. The
> security of the database, at that point is relying on code in a
> front-end that the DBA does not control. The code must be trusted not
> to expose the application password and not to have security holes
> allowing users to do things they shouldn't.
>
If you have a Network Nazi...I mean DBA....that you have to get
approval from then it's unlikely that this is an option. I've always
been granted at least dbo permissions and most of the time I manage
the whole server so that's not usually an issue for me.
> I have reason to be suspicious of the reliability of this approach
> without some pretty good testing first. For each form, one would have
> to open a recordset in code using a connection opened in code using
> the same provider that Access does, then set the form's recordset to
> that. Supposedly, this works, but based on my experiments with
> disconnected recordset, I'm not sure it would be reliable. For
> instance, often when you try to filter a form based on a disconnected
> recordset, Access will reconnect it! (not .ServerFilter, but .Filter)
> If Access does the same with any recordset not connected to the
> default connection, that would be a problem.
Why would it be necessary to open the recordset in code and bind the
form to it? I'm talking about forms directly bound to directly tables
and views.
> I assumed you meant an application pseudo-user, not the application
> itself. I didn't know you could grant permissions based on the
> application string, but it I don;t see how that would be very secure
> anyway since there's no password associated with an application name.
That's my mistake. It's not based on the connection string. You
connect to the database with a standard login and then execute
sp_setapprole which removes all the permissions for the login and
assumes the permissions for the application role.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp
I'm seriously considering doing this for an ADP app that I'm just
about to implement. I'll let you know how it turns out.
Ted Theodoropoulos
> wrote:
>
> I agree with your overall recommendation, but let there be no doubt
> about it: A2K is a very poor design-time client for SQL Server 2000.
> Views don't work properly - and you will get a message about that, and
> I have had a hard time with some stored procedures as well, especially
> if I use dbo.MyProcedureName as the procedure name.
> The stored proc editor is pathetic - not even a single level of undo -
> worse than a standard edit control. Unfortunately the one in SQL
> Server Enterprice Manager is not much better. A modal procedure editor
> - aarrrcchhhh - Fire that MSFT program manager.
>
> There is a lot to be desired for tools support.
>
> -Tom.
I'm not sure what the licensing requirements are for the SQL Server
Client Tools but I would assume that if any of your clients have a SQL
Server that you're licensed to access then you can legitamately
install the Client Tools on your machine. I don't know the ins and
outs of this so someone might know differently.
Could you explain the trouble you're having with the SP names? I
haven't had any trouble saving SPs with dbo as the owner from within
A2K. I agree that designing the whole app from within Access sucks.
If there's any way to get your hands on the SQL Client Tools, life is
much easier.