A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would be better to stay with a
seasoned MDB file than to switch to an ADP.
The database in question was inherited by me from someone else who
implemented things in a less than ideal way, and he, himself, converted it
from an existing database in another application. The database needs to be
revamped, and we are looking to rebuild it from the ground up with
additional functionality. So now the question is not staying with a seasoned
MDB as opposed to a new ADP; but, rather, which would be better for working
with a SQL Server back end, an MDB or ADP file?
Obviously if we go with an MDB file we can reuse many of the DAO routines,
as appropriate. But the main concern here is performance: which one will
provide the better performance, better data access, fewer strange anomalies,
and so on. Development time isn't the main concern.
The database currently relies in part on DAO and in part on stored
procedures. Obviously moving more towards stored procedures would be better,
but there will probably still be a significant amount of code in the front
end.
Any thoughts re. MDB vs. ADP or anything else related to this would be
appreciated.
Thanks,
Neil 25 2631
On Tue, 18 Jan 2005 07:38:50 GMT, "Neil Ginsberg" <nr*@nrgconsult .com> wrote: A while back I posted a message re. using an ADP file with a SQL Server back end as opposed to MDB file with linked tables, thinking that the ADP file would be less problematic. The input I got was that the ADP would be just as, if not more, problematic; that ADO is far more limited than DAO, requiring a lot of workarounds; and that it would be better to stay with a seasoned MDB file than to switch to an ADP.
The database in question was inherited by me from someone else who implemented things in a less than ideal way, and he, himself, converted it from an existing database in another application. The database needs to be revamped, and we are looking to rebuild it from the ground up with additional functionality. So now the question is not staying with a seasoned MDB as opposed to a new ADP; but, rather, which would be better for working with a SQL Server back end, an MDB or ADP file?
Obviously if we go with an MDB file we can reuse many of the DAO routines, as appropriate. But the main concern here is performance: which one will provide the better performance, better data access, fewer strange anomalies, and so on. Development time isn't the main concern.
It's not just the develompment time, it's the unpredictabilit y of runtime
behavior. It's just fastly harder to deliver verifiably high quality code
with ADPs. They're too much of a hodge podge of hastily patched together
kludges. ADPs and ADO also introduce at least as many performance bottlenecks
as performance improvments, and well-designed MDB client/server apps have been
known to perform really well.
The database currently relies in part on DAO and in part on stored procedures. Obviously moving more towards stored procedures would be better, but there will probably still be a significant amount of code in the front end.
That's obvious, but not necessarily correct. Access works quite well using
forms bound to DAO recordsets on linked tables. if you deviate from that, you
might as well scrap Access altogether and implement custom GUIs in something
like C# or Java - of course, that might really be your best answer if time is
not an issue.
With respect to stored procedures performing universally better than Access
queries of linked tables, it's simply not the case. In most cases, an Access
query generates a prepared statement on the server side which functions much
like a stored procedure would. When the server sees the same prepared
statement signature again later on, it will realize it can use the same query
plan it computed for the statement previously - just like a stored procedurem
only without the headaches of being unable to update via an Access bound form.
Any thoughts re. MDB vs. ADP or anything else related to this would be appreciated.
It's easier to get higher reliability and good performance with an MDB than an
ADP - period. If time is not an issue, scrap Access altogether and write
hand-optimized code in a "real" programming language.
Steve Jorgensen wrote: It's not just the develompment time, it's the unpredictabilit y of
runtime behavior.
[snip] It's easier to get higher reliability and good performance with an
MDB than an ADP - period.
Steve,
What are the issues you're talking about here? Or can you point me to a
link that talks about some of them (if they've been discussed
extensively)? I haven't been around the CDMA 'hood for a while; I know
there are plenty of people who argue, and I agree with them, that
learning ADO because it's new doesn't make any sense, but I don't
remember reading much that talks about ADPs being inherintly more prone
to falling over at run time, or inherintly requiring more development
time (other than learning ADO).
This is in no way a troll or a challenge. I've been working on a
project that uses a MS SQL Server back end and an ADP front for the
last seven months, and I've run into very few things that I hadn't seen
in an MDB. Or at least that's my impression--maybe I'm just seeing
trees, though.
Jeremy
PS: I will tell you about the coolest run-time bug I've ever seen in
Access, though. It only happened in a2k3, and only with one particular
datasheet-viewed subform. And only very rarely and unpredicatbly. But
on one of our forms, if you got lucky, you could resize one of the
subforms by grabbing the scroll bar. At run time. We have a lot of
people using the database, and we've never gotten a report of anyone
finding this particular bug. But it sure scared the heck outta me when
I saw it!
On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.c om wrote: Steve Jorgensen wrote: It's not just the develompment time, it's the unpredictabilit y of runtime behavior. [snip] It's easier to get higher reliability and good performance with an MDB than an ADP - period.
Steve,
What are the issues you're talking about here? Or can you point me to a link that talks about some of them (if they've been discussed extensively) ? I haven't been around the CDMA 'hood for a while; I know there are plenty of people who argue, and I agree with them, that learning ADO because it's new doesn't make any sense, but I don't remember reading much that talks about ADPs being inherintly more prone to falling over at run time, or inherintly requiring more development time (other than learning ADO).
This is in no way a troll or a challenge. I've been working on a project that uses a MS SQL Server back end and an ADP front for the last seven months, and I've run into very few things that I hadn't seen in an MDB. Or at least that's my impression--maybe I'm just seeing trees, though.
Well, for one thing, ADPs have a habit of making certain multi-table queries
uneditable. The first version of a query may be editable, but after some
random and seemingly trivial change in the query itself or afger the next
Access service pack or MDAC upgrade, it becomes uneditable again, and the
tweaking process starts all over again. I've finally gotten to the point
where I don't even try to use multi-table queries on bound forms in an ADP.
Either, I use combo boxes as lookups for data in related tables (uh yeah,
that'll help performance), or have to use separate forms for adding/editing
which is more clicks and work hassle for the user as well as more duplication
of form design elements.
For another thing, there is a bug that was supposedly fixed, but actually
isn't in which sometimes an ORDER BY clause in a subform or subreport causes
an error. To fix it, it is necessary to remove the table name qualifier from
the expression in the ORDER BY clause. Of course, the query builder always
puts the table qualifiers on, and it's never obvious what's broken without
some digging.
In ADPs, any time you do anything slightly out of the ordinary in a
server-side query, the ADP gets confused. For instance, in my MDBs, sometimes
I like to have a view that references a table in another database on the
server. I do this, for instance, to put tables frequently used to hold
temporary data in a database with TRUNCATE LOG ON CHECKPOINT so my backups
aren't filled mostly with irrelevant adding and deleting of temporary data.
When you try to bind an ADP to this, it gets really confused beacause ADO is
smart enough to try to look at the underlying tables, but not smart enough to
look at them in another database on the server.
The ADP looking behind your stored procedures and views also makes it hard to
do things that work fine in an MDB such as using a view for security where the
user has permissions granted via the view that they don't haveon the tables
directly. If you know the issue and are -very- careful, you can work around
this. This is in the category of ADPs trying to think for you and thus making
it very hard to use what should be typical rule enforcement on the back-end.
I can't remember all the other issues right now, but I remember that there are
several more.
Steve Jorgensen wrote: On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.c om wrote:
<snip>
The ADP looking behind your stored procedures and views also makes it hard to do things that work fine in an MDB such as using a view for security where the user has permissions granted via the view that they don't haveon the tables directly. If you know the issue and are -very- careful, you can work around this. This is in the category of ADPs trying to think for you and thus making it very hard to use what should be typical rule enforcement on the back-end.
I haven't had any problems implementing row level security (eg. a
manager can only see the employee they manage as defined in a table).
Can you elaborate what problems you encountered?
Br@dley
Neil Ginsberg wrote: Any thoughts re. MDB vs. ADP or anything else related to this would
be appreciated.
If you bind your forms then your users must have SQL table permissions.
If they have table permissions what is to prevent them from using these
permissions directly on the table, in your database or another?
One can prevent this by using application roles. Unfortunately ADPs and
application roles combined are a cruel joke, because ADPs have various
connections for various interfaces and one cannot predict what
connection will be used where.
And if you don't bind your forms why use Access, ... for its limited
forms, its bizarre idiosyncracies, its memory glut, its archaic
scripting language? Maybe for its reports? MAYBE!
Access is dead. It's fine with JET; but JET has been retired and is
rusting out slowly on one of those big California desert airfields. As
a front end for other db engines it's a joke, a bad joke.
What are those lines from "Red Rubber Ball"?
"I bought my ticket with my tears,
That's all I'm gonna spend." ly******@yahoo. com wrote: Neil Ginsberg wrote: Any thoughts re. MDB vs. ADP or anything else related to this would be appreciated.
If you bind your forms then your users must have SQL table permissions. If they have table permissions what is to prevent them from using these permissions directly on the table, in your database or another?
Always use views/stored procedures.
One can prevent this by using application roles. Unfortunately ADPs and application roles combined are a cruel joke, because ADPs have various connections for various interfaces and one cannot predict what connection will be used where. And if you don't bind your forms why use Access, ... for its limited forms, its bizarre idiosyncracies, its memory glut, its archaic scripting language? Maybe for its reports? MAYBE!
Access is dead
The system I converted from MDB to ADP is going great. There were a few
hurdles but I've managed to work around them all (esp. using A2000 which
was released before SQL2000 and so won't allow updatable views unless
you specify WITH VIEW_METADATA)
Br@dley
There seem to be indications that Jet isn't quite "rusting away" despite it
having once been declared "in maintenance mode" -- it has been updated since
that declaration and Jet-ODBC-Server is now recommended over
ADP-ADODB-Server by knowledgeable Microsoft insiders.
In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO
was being "deprecated " in the next version. But I have not personally seen
such a statement from Microsoft.
The old crystal ball is cloudy, at best, but it's clear from what Microsoft
has publicly stated that there is going to be another release of Microsoft
Office and Access is going to be part of it.
Larry Linson
Microsoft Access MVP
<ly******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Neil Ginsberg wrote: Any thoughts re. MDB vs. ADP or anything else related to this would be appreciated.
If you bind your forms then your users must have SQL table permissions. If they have table permissions what is to prevent them from using these permissions directly on the table, in your database or another? One can prevent this by using application roles. Unfortunately ADPs and application roles combined are a cruel joke, because ADPs have various connections for various interfaces and one cannot predict what connection will be used where. And if you don't bind your forms why use Access, ... for its limited forms, its bizarre idiosyncracies, its memory glut, its archaic scripting language? Maybe for its reports? MAYBE!
Access is dead. It's fine with JET; but JET has been retired and is rusting out slowly on one of those big California desert airfields. As a front end for other db engines it's a joke, a bad joke.
What are those lines from "Red Rubber Ball"? "I bought my ticket with my tears, That's all I'm gonna spend."
On Wed, 19 Jan 2005 12:08:32 +1100, <Br@dley> wrote: Steve Jorgensen wrote: On 18 Jan 2005 05:41:56 -0800, al*****@yahoo.c om wrote:
<snip>
The ADP looking behind your stored procedures and views also makes it hard to do things that work fine in an MDB such as using a view for security where the user has permissions granted via the view that they don't haveon the tables directly. If you know the issue and are -very- careful, you can work around this. This is in the category of ADPs trying to think for you and thus making it very hard to use what should be typical rule enforcement on the back-end.
I haven't had any problems implementing row level security (eg. a manager can only see the employee they manage as defined in a table). Can you elaborate what problems you encountered?
Br@dley
If you use the WITH VIEW METADATA option, then using views for security works,
but only assumning there is only one table behind the view. If there is more
than one table behind the veiw, then either no key fields are reported, or too
many key fields are reported. The first case slows down the recordsets and
ensures that they are not editable even if you have an INSTEAD OF trigger that
should make it so. If too many key fields are reported, then rows can seem to
disappear after editing. Either way, it's screwey.
The work-around is to use only single-table views for security, then make
multi-table views that query from those views if necessary. This still
thwarts any attempts to use INSTEAD OF triggers on the multi-table view
because since that view does not have WITH VIEW METADATA, the ADP will go
around it and try to update the single-table views directly.
Well, Steve... for a "nice little desktop database", Access has given us a
"pretty good ride"... it was first released in October 1992, so it has
lasted over 12 years, so far, and it's not nearly dead yet.
But, even if it were... 12 years is a significant lifetime for a software
product.
(And, another thing, I made a good living just out of Access for most of
those 12 years. That, too, is impressive for a "nice little desktop
database". I am convinced it was so much better than expected that it
surprised even its developers.)
Larry
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:cg******** *************** *********@4ax.c om... On Tue, 18 Jan 2005 23:25:13 -0600, "Larry Linson"
<bo*****@localh ost.not> wrote:
There seem to be indications that Jet isn't quite "rusting away" despite
ithaving once been declared "in maintenance mode" -- it has been updated
sincethat declaration and Jet-ODBC-Server is now recommended over ADP-ADODB-Server by knowledgeable Microsoft insiders.
In fact, one poster in CDMA said that Microsoft had told him that ADP-ADO was being "deprecated " in the next version. But I have not personally
seensuch a statement from Microsoft.
The old crystal ball is cloudy, at best, but it's clear from what
Microsofthas publicly stated that there is going to be another release of
MicrosoftOffice and Access is going to be part of it.
Larry Linson Microsoft Access MVP My concern is that since it seems clear that Access will lag the rest of
MS Office in terms of implementation as a .NET base app, it will get
increasingly orphaned by developers migrating from VB/VBA development as advances in
the .NET UI technology simultaneously narrow the functionality gap with Access
in terms of things like easy reporting and continuous bound forms.
If that is the trend, it might be better to be leading it than following
it. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dag |
last post by:
Is there a python module that includes functions for working with prime
numbers? I mainly need A function that returns the Nth prime number and
that returns how many prime numbers are less than N, but a prime number
tester would also be nice. I'm dealing with numbers in the 10^6-10^8 range
so it would have to fairly efficient
Dag
|
by: Greg Brunet |
last post by:
In doing some testing of different but simple algorithms for getting a
list of prime numbers, I ended up getting some results that seem a bit
contradictory. Given the following test program (testPrimes.py) with
two algorithms that both check for primes by testing only odd numbers
using factors up to the square root of the value, where Primes1...
|
by: AshifToday |
last post by:
this was my and my frineds little project in earlier classes,
the program seperates the composite and prime numbers in two sections
of the screen
=====================
/*
This program has been made by A & A Group.
Muhammad Ali: Roll # 1462 Class A-2 , B.Sc.(Hons.) in C.S.
|
by: CMM |
last post by:
I am so disappointed in VS2005. The "little things" wrong with it are just
mind-boggling. Boy, I'll be so mad if I have to wait a year for the ".1"
release that fixes all the bugs in this obvious beta product.
This (received today) is just one of the many examples of why ASP.NET 2.0
was totally not ready for prime time....
|
by: Joel Mayes |
last post by:
Hi All;
I'm teaching myself C, and have written a prime number generator. It is
a pretty inefficient implementation of the Sieve of Eratosthenes to
calculate primes up to 1,000,000. If anyone has time to critic and offer
my some feedback I'd be grateful
Thanks
Joel
| |
by: zacariaz |
last post by:
Once again i have tryed my lyck with classes and it seem that im
getting the hang of it, but before i get too self confident, i would
like to get an expert oppinion.
I have made a working primeclass, it isnt finished, but it should show
if im making any major mistakes and errors.
@code start:
#include <vector>
|
by: rhle.freak |
last post by:
Here is my code to generate prime numbers.It works absolutely fine when
the range is
*not very large*. However on initializing i with a large integer it
produces erroneous results
(some numbers ending in 5 ..which obviously cannot be prime numbers)
can anyone please help me out with the reason??
/*Generate Prime Numbers*/...
|
by: newstips6706 |
last post by:
1, 2, 3, 5, 7... PRIME Numbers
________________________________
Definitions
What is a PRIME Number ?
|
by: Ron Garret |
last post by:
The wsgiref module in Python 2.5 seems to be empty:
$ python
Python 2.5 (r25:51908, Mar 1 2007, 10:09:05)
on darwin
Type "help", "copyright", "credits" or "license" for more information.
So... is wsgi considered ready for production use, or is it still on the
bleeding edge? And if the former, which implementation should one use?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |