I have an Access app (split into FE and BE) running for some years,
that is now also being used in a second office, connected by a WAN.
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
backend. To justify the costs involved, I'm trying to get a feel for
what spped improvements might reasonably be expected.
After a lot of searching, I've found a great many sites that say it
will (or should) be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
MTIA
18  1864 
I am one of the few proponents of ADPs who posts regularly to this
newsgroup.
I've converted several applications to ADPs. I don't think they run
noticeably faster than a local MDB solution. I think they are less
secure.
They can be faster if your application requires complex Selects which
MAY be effected more efficiently on a Server-Database.
IMO, there is no speed advantage to be gained by going to ADPs (or
MDBS via ODBC) and SQL Server that could come anywhere close to a
rewriting of an existing application by a qualified, experienced
Access Developer. The Developer accounts for 95% of the efficiency of
any Database application.
On Aug 8, 10:09*am, maxhugen <maxhu...@gmail.comwrote:
I have an Access app (split into FE and BE) running for some years,
that is now also being used in a second office, connected by a WAN.
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
backend. To justify the costs involved, I'm trying to get a feel for
what spped improvements might reasonably be expected.
After a lot of searching, I've found a great many sites that say it
will (or should) *be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
MTIA
An Access application split over a WAN would normally be virtually unusable.
If this is what you are experiencing then it doesn't matter how overloaded
or not the remote LAN is, it's gonna run like a three-legged dog anyway
simply because of the limitations inherent in the architecture of Access.
However, if the back-end were SQL Server, the application would usually be
quite usable over a WAN. Noticeably slower than on a LAN, but nonetheless
usable.
So, what you can expect is to improve performance from "hopeless" to "quite
adequate" - which by any metric is probably an improvement of several orders
of magnitude!
"maxhugen" <ma******@gmail.comwrote in message
news:65**********************************@j7g2000p rm.googlegroups.com...
>I have an Access app (split into FE and BE) running for some years,
that is now also being used in a second office, connected by a WAN.
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
backend. To justify the costs involved, I'm trying to get a feel for
what spped improvements might reasonably be expected.
After a lot of searching, I've found a great many sites that say it
will (or should) be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
MTIA
Ah, but the WAN, Lyle, the WAN...
"lyle fairfield" <ly************@gmail.comwrote in message
news:a9**********************************@12g2000h sd.googlegroups.com...
I am one of the few proponents of ADPs who posts regularly to this
newsgroup.
I've converted several applications to ADPs. I don't think they run
noticeably faster than a local MDB solution. I think they are less
secure.
They can be faster if your application requires complex Selects which
MAY be effected more efficiently on a Server-Database.
IMO, there is no speed advantage to be gained by going to ADPs (or
MDBS via ODBC) and SQL Server that could come anywhere close to a
rewriting of an existing application by a qualified, experienced
Access Developer. The Developer accounts for 95% of the efficiency of
any Database application.
Of course, I should also have said that having the remote users run the
application using Terminal Services may well be cheaper than rewriting it!
And if you are determined to go client/server, upsizing using ODBC linked
tables may well be cheaper than rewriting it as an ADP.
"bcap" <bc**@nospam.nowherewrote in message
news:48**********************@news.zen.co.uk...
An Access application split over a WAN would normally be virtually
unusable. If this is what you are experiencing then it doesn't matter how
overloaded or not the remote LAN is, it's gonna run like a three-legged
dog anyway simply because of the limitations inherent in the architecture
of Access.
However, if the back-end were SQL Server, the application would usually be
quite usable over a WAN. Noticeably slower than on a LAN, but nonetheless
usable.
So, what you can expect is to improve performance from "hopeless" to
"quite adequate" - which by any metric is probably an improvement of
several orders of magnitude!
"maxhugen" <ma******@gmail.comwrote in message
news:65**********************************@j7g2000p rm.googlegroups.com...
>>I have an Access app (split into FE and BE) running for some years,
that is now also being used in a second office, connected by a WAN.
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
backend. To justify the costs involved, I'm trying to get a feel for
what spped improvements might reasonably be expected.
After a lot of searching, I've found a great many sites that say it
will (or should) be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
MTIA
Per maxhugen:
>Has any had some actual personal experience in 'upsizing' from Access
to ADP + SQL Server that they could share pls? Or point me to any
URLs?
Ping me in about nine months.
One of my apps had been running front end/user's PC & back
end/LAN server.
Response time (i.e. time it took to load a screen) was pretty
good on day one but has slowly gone down the toilet.
Stopgap fix has been to move the app to a Citrix server, where
multi users get the same (pretty good) performance as I did on my
previous desktop PC.
But we have two processes that are very time consuming and need
tb budded off into some sort of asynchronous processes.
We *could* have a bogus user logged on to the Citrix box all the
time with a special version of the app looking for semaphores
telling it to run said processes, but the users have opted to
migrate the back end to SQL Server.
This works for me bc IT will be doing the migration and then
they'll be one step closer to owning the app when I hit the
lottery and retire to Maui.
Personally, I don't expect a significant improvement in screen
load times.
But maybe I'll be surprised.
Within nine months, I should actually know something...
--
PeteCresswell
"maxhugen" <ma******@gmail.comwrote in message
news:65**********************************@j7g2000p rm.googlegroups.com...
>I have an Access app (split into FE and BE) running for some years,
that is now also being used in a second office, connected by a WAN.
check my article on using ms-access on a wan here. http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
This office has network problems, as it's over-utilized (97% according
to one IT guy!!).
I'm looking into converting the MDB app into an ADP with a SQL Server
Do note that you do NOT necessary have to move to a adp project. You can
keep your application as is now, and simply link the tables to sql server in
place of your back end. This is a recommend approach since then all of your
existing dao reocrdset code will run (very few mods need be made). If you
are staring from scratch, then a ADP is a good choice, but for an existing
applications, is FAR FAR less work to simply go the linked tables via odbc.
For the most part, performance between a odbc linked table and that of an
adp project is not really different. However, to be fair, an adp project is
MORE forgiving when you write bad queries etc since by nature they are all
pass-through.
>I'm trying to get a feel for
what spped improvements might reasonably be expected.
Actually, we see weekly posts here in that after converting to sql server,
things
run slower. So, moving to sql server is not a magic bullet. Your designs
must
be ones that LIMITS the number of records transferred to the form. While you
can get away opening a form bound to large table with a mdb back end, when
using odbc to sql server, you NEVER want to do this. (bound forms are ok,
but
you REALLY want to open up a form with a "where" clause to restrict the
record.
I talk about searching and brining up records here: http://www.members.shaw.ca/AlbertKal...rch/index.html
>
After a lot of searching, I've found a great many sites that say it
will (or should) be faster, but I have yet to find any mention of
anyone doing it, and whether they were actually able to get speed
improvements.
You only get improvements if your designs are sound in the first place. The
mere "act" of moving to sql server often will not speed things up at all.
As mentioned, since you have an existing application, you likely better to
stick with a mdb front end, and using linked odbc tables. It is FAR less
work. (and, about 99% of your existing code should run).
If you move to adp, then you have to dump all of your dao code in your
application Depending on the size of your application, this usually means
it better to stick with mdb + linked tables to sql server and it not worth
the time + effort to go the adp route.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com
This implies that ADP "queries" exist as strings in the Access
application and the strings are passed through to the SQL Server. I'm
sure Albert, that you know that except in the case of dynamic query
strings called from code, this is not the case. The "queries" we see
exist on the SQL-Server as Stored Procedures, Views and Functions,
entirely independent of the Access application.
On Aug 8, 9:05*pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
However, to be fair, an adp project is
MORE forgiving when you write bad queries etc since by nature they are all
pass-through.
"lyle fairfield" <ly************@gmail.comwrote in message
news:bf**********************************@l42g2000 hsc.googlegroups.com...
>This implies that ADP "queries" exist as strings in the Access
application and the strings are passed through to the SQL Server.
No, not really what I trying to imply.
>I'm
sure Albert, that you know that except in the case of dynamic query
strings called from code, this is not the case.
Well, there many other places/cases (where clause to forms, where clause
to reports, filters etc.
The "queries" we see
exist on the SQL-Server as Stored Procedures, Views and Functions,
entirely independent of the Access application.
Sure, but regardless, from "in-line" sql, or existing queries...they all
execute server side 100%....just like a pass-though query does. That is
really the only point I making here...
jet does not always do a table join server side..it can mess them up, and
cause the joins to occur local (both tables come down the pipe). Jet usually
does an ok job, but some joins it does mess up.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com
"bcap" <bc**@nospam.nowherewrote:
>Ah, but the WAN, Lyle, the WAN...
FWIW Tom Ellision, former Access MVP, did a lot of work with ADPs. He was able to
get adequate performance over a 56 kpbs dialup connection after a lot of tuning. Not
good performance but adequate.
That said I don't know if spending the time migrating to ADPs and working with
Access's ADP quirks would give you better performance compared to linked tables,
views and such. However I bow to Lyle and others experience in that area.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Tom Ellison was convinced that using MSDE (stripped down SQL Server) as the
back end, usually on the very same machine as the front end, was the key to
adequate performance in the huge, massive, SQL statements that were required
in the business environment that he served (commercial seed business).
IIRC, he said that MSDE was multi-threaded whereas Jet was not. I didn't
remember him using ADP and dialup, though. I stand in awe of Tom's SQL
abilities.
Larry Linson
Microsoft Office Access MVP
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:7m********************************@4ax.com...
"bcap" <bc**@nospam.nowherewrote:
>>Ah, but the WAN, Lyle, the WAN...
FWIW Tom Ellision, former Access MVP, did a lot of work with ADPs. He
was able to
get adequate performance over a 56 kpbs dialup connection after a lot of
tuning. Not
good performance but adequate.
That said I don't know if spending the time migrating to ADPs and working
with
Access's ADP quirks would give you better performance compared to linked
tables,
views and such. However I bow to Lyle and others experience in that area.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
"bcap" <bc**@nospam.nowherewrote in message
news:48***********************@news.zen.co.uk...
Of course, I should also have said that having the remote users run the
application using Terminal Services may well be cheaper than rewriting it!
And if you are determined to go client/server, upsizing using ODBC linked
tables may well be cheaper than rewriting it as an ADP.
Not only that, but Access MDB/ACCDB <--Jet/ACE <--ODBC <--serverDB is
the configuration for Access clients currently recommended by the Access
team, not ADP.
Larry Linson
Microsoft Office Access MVP
"Larry Linson" <bo*****@localhost.notwrote in message
news:s47ok.652$mP.360@trnddc03...
>
"bcap" <bc**@nospam.nowherewrote in message
news:48***********************@news.zen.co.uk...
>Of course, I should also have said that having the remote users run the
application using Terminal Services may well be cheaper than rewriting
it!
And if you are determined to go client/server, upsizing using ODBC linked
tables may well be cheaper than rewriting it as an ADP.
Not only that, but Access MDB/ACCDB <--Jet/ACE <--ODBC <--serverDB
is the configuration for Access clients currently recommended by the
Access team, not ADP.
Larry Linson
Microsoft Office Access MVP
Hi Larry,
I recall reading a blog entry to that effect when Access 2007 was in
development, but can you point to anything more recent or definitive? I
think there's a lot of puzzled people right now who would like to see some
kind of position statement from Microsoft on ADP's; is there anything you
know of that might help?
Per Larry Linson:
>Not only that, but Access MDB/ACCDB <--Jet/ACE <--ODBC <--serverDB is
the configuration for Access clients currently recommended by the Access
team, not ADP.
Can anybody speculate on why?
On the several apps where I was forced to stay with ODBC against
tables migrated to SQL Server, response time went right down the
toilet.
OTOH, on the one app where I got to do it my way from the bottom
up (ADO, stored procedures for *everything*) I was pleasantly
surprised by how quickly it loaded/saved some fairly heinous
screens - and I don't know diddley about SQL Server, the whole
thing was done on a wing and a prayer.... heaven forbid somebody
who knew what they were doing should have done the DB design and
written the SPs.
--
PeteCresswell
(PeteCresswell) wrote:
Per Larry Linson:
>Not only that, but Access MDB/ACCDB <--Jet/ACE <--ODBC <-->
serverDB is the configuration for Access clients currently
recommended by the Access team, not ADP.
Can anybody speculate on why?
On the several apps where I was forced to stay with ODBC against
tables migrated to SQL Server, response time went right down the
toilet.
OTOH, on the one app where I got to do it my way from the bottom
up (ADO, stored procedures for *everything*) I was pleasantly
surprised by how quickly it loaded/saved some fairly heinous
screens - and I don't know diddley about SQL Server, the whole
thing was done on a wing and a prayer.... heaven forbid somebody
who knew what they were doing should have done the DB design and
written the SPs.
It should be noted that one can use MDB/ODBC and still use "stored
procedures for everything" if one chooses to do so.
I avoided ADP because of the restrictions. I couldn't care less about small
performance differences one way or the other.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
"(PeteCresswell)" <x@y.Invalidwrote in
news:co********************************@4ax.com:
Per Larry Linson:
>>Not only that, but Access MDB/ACCDB <--Jet/ACE <--ODBC <-->
serverDB is the configuration for Access clients currently
recommended by the Access team, not ADP.
Can anybody speculate on why?
Microsoft's own words might be sufficient to explain. From
< http://technet2.microsoft.com/Office...e641e-ba1c-446
a-8ff2-221769a58ba51033.mspx?mfr=true:
(get rid of the word wrap, with no spaces)
The key section:
Access Data Projects (ADPs)
An Access Data Project is an OLE document file, like the .xls
or.doc file formats. It contains forms, reports, macros, VBA
modules, and a connection string. All tables and queries are
stored in SQL Server. The ADP architecture was designed to create
client-server applications. Because of this, there is a limit to
the number of records that Access returns in any recordset. This
limit is configurable, but you typically must build enough
filtering into your application so that you do not reach the
limit.
Access uses OLEDB to communicate with SQL Server. To provide the
Jet-like cursor behavior desired for desktop applications, Access
implements the Client Data Manager (CDM) as an additional layer
between Access and OLEDB.
Because of the layers required to get from Access to SQL Server
in the ADP architecture, it is often easier to optimize MDB/ACCDB
file solutions. However, there are some scenarios where a report
might be generated significantly faster in an ADP file. To add
these performance improvements and retain the flexibility of SQL
Server, you can build the majority of the application in an MDB
or ACCDB file and have the file load reports from a referenced
ADP file.
One advantage that ADP files have over files in MDB or ACCDB
format is the ability to make design changes to SQL Server
objects. ADP files include graphical designers for tables, views,
stored procedures, functions, and database diagrams.
It's not clear exactly why they have given up on ADPs, but it seems
that it likely has something to do with the layers between the ADP
and your SQL Server. Supposedly, ADPs were supposed to be "closer to
the metal," but in fact, they have just as many intermediate layers
in between the ADP and the SQL Server as you have with MDB/ODBC. And
many have reported the problems with ADO guessing how to make
non-editable queries updatable (i.e., going around your views, with
appropriate security settings, and attempting to update the tables
directly). People complained about Jet guessing wrong, but ADO
guessed wrong just as often, but ADPs didn't offer as many options
as workarounds, I guess, since everything goes through OLEDB (i.e.,
no option for a passthrough).
I never thought that ADPs really had much of a reason to exist other
than a superstitious avoidance of Jet.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Since Access 2002 ADP forms and reports have allowed their recordset
to be set to an ADO recordset. This is very powerful, although those
not so experienced with ADO might find it slow and tedious.
Although I have not examined it extensively the recordset setting
seems to enable:
1. Using any database engine for which an ADO provider exists; these
may include Oracle, JET, Ace, text files, http folder members, and XML
files.
2. Avoiding the Client Data Manager layer.
Why was further development on ADPs stopped after Access 2003? I had
two major concerns about ADPs.
One was security. Create a new ADP and you will see and have
everything available to you that is visible and available in your
application ADP, without benefit of its controls. Application roles
will solve this. Application roles in ADPs are disastrous; they won't
work properly and programming them takes forever.
Two was multiple connections. We may think a hundred users of an SQL
db is very slight usage. But what if each of those hundred users' ADP
has ten connections open? Now we have a thousand connections open to
the same db. This isn't efficient, and in my opinion, must, sooner or
later cause locking troubles.
TTBOMK MS was unwilling to assign the resources necessary to solve
these problems; I think major resources would have been required. So
when ADPs did not hit the top of the popularity charts, it may have
been cost effective just to abandon them.
Of course, using an ADO connection created in code and independent of
the ADPs CurrentProject and CodeProject Cnnections (in fact, don't
create these) solves all or almost all of these problems. But I think
very few developers will want to take the time to effect that
solution.
(I believe the security problems are the same with ODBC, but a new MDB
does not slap you in the face with the potential for bad seed
immediately upon creation.)
On Aug 13, 8:01*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
Microsoft's own words might be sufficient to explain. From
<http://technet2.microsoft.com/Office...e641e-ba1c-446
a-8ff2-221769a58ba51033.mspx?mfr=true:
(get rid of the word wrap, with no spaces)
The key section:
* *Access Data Projects (ADPs)
* *An Access Data Project is an OLE document file, like the .xls
* *or.doc file formats. It contains forms, reports, macros, VBA
* *modules, and a connection string. All tables and queries are
* *stored in SQL Server. The ADP architecture was designed to create
* *client-server applications. Because of this, there is a limit to
* *the number of records that Access returns in any recordset. This
* *limit is configurable, but you typically must build enough
* *filtering into your application so that you do not reach the
* *limit.
* *Access uses OLEDB to communicate with SQL Server. To provide the
* *Jet-like cursor behavior desired for desktop applications, Access
* *implements the Client Data Manager (CDM) as an additional layer
* *between Access and OLEDB.
The Recordset property for a form is also fairly disastrous. In Access 2002
I tried to use it, as per the ADH, to create logical transactions spanning
updates to a form and it's subform. Utterly useless: as soon as you try
anything clever with that Recordset property, Access starts crashing right,
left and centre.
On Aug 14, 4:23*am, "bcap" <b...@nospam.nowherewrote:
The Recordset property for a form is also fairly disastrous. *In Access2002
I tried to use it, as per the ADH, to create logical transactions spanning
updates to a form and it's subform. *Utterly useless: as soon as you try
anything clever with that Recordset property, Access starts crashing right,
left and centre.
ADP form recordset updates deletes etc work perfectly for me. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Catalin |
last post by:
How can I make executables with python?
I found some utilities that claim they can do something like that like
Installer and py2exe but they actualy pack the code in a huge arhive!
This solves the...
|
by: Neuruss |
last post by:
It seems there are quite a few projects aimed to improve Python's
speed and, therefore, eliminate its main limitation for mainstream
acceptance.
I just wonder what do you all think?
Will Python...
|
by: Ron Johnson |
last post by:
Hi,
While on the topic of "need for in-place upgrades", I got to think-
ing how the pg_restore could be speeded up.
Am I wrong in saying that in the current pg_restore, all of the
indexes are...
|
by: Joanna Carter \(TeamB\) |
last post by:
Hi gurus.
Does anyone have any experience as to whether .NET 2.0 will be any faster
executing than .NET 1.1 ?
We are looking at starting a new project in .NET and are getting feedback
from...
|
by: Peter |
last post by:
I have the following code:
_transColl is a collection of classes and it contains about 42000 objects
My question is there any way I can make this code run faster?
I have found out that line 2 is...
|
by: bhag |
last post by:
hi all,
I'm writing because I haven't been able to find enough
information at the
book stores and at the MS web site on some of my questions. Perhaps
I'm
asking the wrong questions, but if you...
|
by: vunet.us |
last post by:
Hello,
My AJAX application paints data into about 500 cells with unique ID
every 10 seconds. I am using document.getElementById() to find the
right cell. However, I have noticed that...
|
by: wang frank |
last post by:
Hi,
While comparing the speed of octave and matlab, I decided to do a similar
test for python and matlab. The result shows that python is slower than
matlab by a factor of 5. It is not bad since...
|
by: Peted |
last post by:
Hi,
im hoping someone cane provide or point to a definitive accurate
explantion of dotnet compilation when run and the best way to optimise
peformace when dotnet code is run first time and...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |
