Migrating away from MS-Access

[John Casey]

Re: Migrating away from MS-Access



MS-Access/VBA developer since 1991.

*** Sent via Developersdex http://www.developersdex.com ***

[John Casey]

Re: Migrating away from MS-Access

In my experience, if you convert your Access tables directly over to SQL
Server and do not modify the front end code, you will end up with a
slower application. Regular SQL queries will still need to pull all the
data down from the server before it can process it. All of the existing
SQL queries will need to be converted to stored procedures to see any
type of performance gain.

Also, if the entire Access database resides on the server, this will
cause a performance issue. First try and split front end from backend
(data) and place the front end on the users desktop. I am sure you can
find several possible solutions to update the users front end
automaticaly as modifications are made to it. Later try and pull as many
not core data tables out of the back end and into the front end as they
are found. These tables are usually static tables used for drop downs,
temporary data handling, etc...

Next try and do as much query processing on the front end as possible.
For example if you are running a complex account report lets say, first
just pull down all required fields from all of that account records for
the report. Then do the remaining queries on the front end...



*** Sent via Developersdex http://www.developersdex.com ***

[(PeteCresswell)]

Re: Migrating away from MS-Access

Per John Casey:

>In my experience, if you convert your Access tables directly over to SQL
>Server and do not modify the front end code, you will end up with a
>slower application.

I've had similar experiences: client mandates that we move tables
to client-server DB and just ODBC and keep the same queries. We
say "That's a bad idea". They say "Just do it".

Performance in 3 cases went from sub-second response time to
several seconds.

What upset me in those cases is that my name was on the app as
the last guy to touch it.
--
PeteCresswell

[David W. Fenton]

Re: Migrating away from MS-Access

John Casey <john_casey@hot mail.comwrote in
news:47188bc2$0 $3581$815e3792@ news.qwest.net:

In my experience, if you convert your Access tables directly over
to SQL Server and do not modify the front end code, you will end
up with a slower application. Regular SQL queries will still need
to pull all the data down from the server before it can process
it.

This is just not true. Jet is very smart, and hands off many, many
of the queries to the server, and just returns the results. If you
look at the Jet SHOWPLAN, it shows exactly that. And if you use
Profiler on your SQL Server, you'll see that SQL Server then uses
the generic SP to make the query passed by Jet into a SP dynamically
and automatically.

Certain types of queries (updates, deletes, inserts) can be *very*
inefficient, though, because, for instance, an insert of 100 records
will get sent by Jet as 100 separate 1-record inserts (each of which
is then run through the generic SP). This is annoying to the
developer, but good behavior on Jet's part, since it allows the SQL
Server to serialize each line, rather than it being taken over by a
long action query. In that case, it's usually better to either use a
passthrough or a stored procedure.

All of the existing
SQL queries will need to be converted to stored procedures to see
any type of performance gain.

Not at all true. *Some* things will need to be converted. Some will
not.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[(PeteCresswell)]

Re: Migrating away from MS-Access

Per David W. Fenton:

>This
>is pretty high-level and efficient, since it's vector-based (partly)
>and not bit-mapped. So it's very fast.
>
>Given how well it works across 28.8. dialup, I can't imagine why it
>would be a problem running on any decent WAN.

I didn't want to say anything, but now I've got to chime in to
support David's assertion.

I do most of my development work via Remote Desktop Connection
over VPN tunnels.

I've got Verizon's most el-cheapo FIOS service: touted at 5mbps,
but I'll bet it never gets over 4.... yet my perceived
performance is good enough to work all day and never be conscious
of being on Remote Desktop. There's occasionally a little bit
of mouse delay, but that's it.

One client has a WAN pipeline from Penna to their London office
and I'm going to try VPN-ing across that as soon as I can. Guys
from London that are visiting here use it all day and claim "no
problem".
--
PeteCresswell

[NewsGuy]

Re: Migrating away from MS-Access

ugg, just realised my posting details have changed...I used to read as
"John" <nospam@nospam. com...now im
"NewsGuy" <john@nospam.co m.auwrote in message
news:ffcepe0d1n @news5.newsguy. com...

Ive only just returned to this group after a few years absense, I notice
there is another guy called John...thats not me....:) I wonder if thats
confusing...

Installed Vista on my home puta......yes, Im asking for more pain....but so
far.....mostly good....

I guess I should mention that I do realise VB.net will take longer....its a
case of weather its worth it keeping in mind a large system that it may one
day grow into...

I dunno, also no one really wants to work with access is the general vibe I
get...some people scoff at me when I tell them Im an access developer...but
yeah I guess only fools listen to fools....I wonder how the employment
market is for access developers and if/when we get another if that has any
merit for analysis...

John

[David W. Fenton]

Re: Migrating away from MS-Access

"NewsGuy" <john@nospam.co m.auwrote in
news:ffcepe0d1n @news5.newsguy. com:

must admit Im still slightly inclined to go the vb.net route,
(dispite me
agreeing with everything everyone has said so far, maybe im
crazy),

I'd say going the VB.NET route would be crazy, yes.

I think
once we start having to do things like unbound forms and massive
rewrites, while it would still be quicker to go access, I dont
consider vb.net to be hugly more expensive.

But you *don't* have to do unbound forms in Access. All you have to
do to make it perform well is to properly filter the data you're
retrieving to meet the immediate needs of the user. That does *not*
require creating unbound forms.

For what it's worth, in 11 years of professional Access development,
I've implemented unbound forms less than a dozen times. But I've
implemented lots of forms that load a single record or a small group
of records.

I do think that it is definitly the case the front end needs a
70-80% rewrite to work with sqlserver

Well, first, how much work would it take to make the Jet back end
properly structured and to adapt the front end to work with that?
That part should *not* be considered part of the SQL Server
conversion at all. Once youv'e figured that out, then you'd want to
estimate what it would take to convert the back end to SQL Server,
and my bet is that it's relatively trivial (though it's very hard
without experience to forecast exactly which parts of the app will
need to be adapted/moved to the SQL Server).

There must be reasons why people develop in
VB.net.

I think it's because they don't know any better. VB.NET *is* better
for a VB developer who wants to do Internet-enabled apps. But for
Access developers, it lacks a lot.

This is how I think of it;
>
Access Advantages
Developing a data access layer is where all the work is for
VB.Net. Access has already done this, so there is no work.

I think you're underestimating a lot of what Access has to offer.

VB.NET
The user interface is much quicker to develop robustly in VB.Net
utilising inheritance and user controls.

Huh? Since when? Access has a whole host of controls and objects
specifically designed for use with databases, most of which
VB/VB.NET lack. Sure, VB.NET is better with databound controls than
VB, but still there is nothing to compare to an Access continuous
form for ease of use. And the event models are much poorer for the
tasks of data editing than the Access form/control event model.

Data validation is easier

Howso?

Im just reading a post by Albert D Kallal, lol and David W Fenton,
and all the guru's, lol, I think is a good thread that has alot of
merit.

http://groups.google.com/group/comp.databases.ms-access/browse_thre

ad/thread/8053a3b03a7162c 6/879440056ce94e1 b?hl=en&lnk=st& q=vb.net+v
s+access#879440 056ce94e1b

That thread certainly makes the point about unbound forms not being
required to create a good app in Access.

right now thats taking a long time to read :) I think it may boil
down to a decision as to weather the project is big enough to
warrent developing the data access layer....but clearly I need to
do more research....as those 3 things were the only I could think
of off the top of my head....

I would vote against the data access layer. But, of course, I'm
operating without first-hand information about the app.

I think that many people from the C/S side of things overengineer
their apps, and a separate data access layer to an app that is
currently operating with a Jet back end sounds like complete
overkill. If a data access layer is necessary, then the app couldn't
work at all with a Jet back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[(PeteCresswell)]

Re: Migrating away from MS-Access

Per NewsGuy:

I think
>once we start having to do things like unbound forms and massive rewrites,
>while it would still be quicker to go access, I dont consider vb.net to be
>hugly more expensive.

I keep hearing that from people who probably know, but for the
one app that I took the time to develop in both VB6 and MS Access
the ratio was somewhere between three and five to one (got it
recorded somewhere... but can't recall exactly).

But based on the dollars billed by a large company's IT
department when they replaced two of my MS Access apps with
net-centric (I know for sure one was .Net... not sure about the
other) I'd expect the .Net ratio tb more like 10:1.

Been meaning to master enough of .Net to prove or disprove that
suspicion for about five years now... but never got around to it.

Maybe you can report back if/when you do it.

Also... there are those who would argue that in the whole life
cycle of an application, the initial development time is
chickenfeed anyhow.
--
PeteCresswell

[NewsGuy]

Re: Migrating away from MS-Access


"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns99CFA13 32AAA8f99a49ed1 d0c49c5bbb2@216 .196.97.142...

"NewsGuy" <john@nospam.co m.auwrote in
news:ffcepe0d1n @news5.newsguy. com:
>

> must admit Im still slightly inclined to go the vb.net route,
> (dispite me
>agreeing with everything everyone has said so far, maybe im
>crazy),

>
I'd say going the VB.NET route would be crazy, yes.
>

lol :) Perhaps...im in a flip flop mood today...im going round in circles a
bit....need to take a break to get some objectivity... right now I'm
thinking yeah, Access is much faster, if I just did a complete rewrite of
the access front end it would be dandy, but still thers a "but..." in the
back of my mind....

>I think
>once we start having to do things like unbound forms and massive
>rewrites, while it would still be quicker to go access, I dont
>consider vb.net to be hugly more expensive.

>
But you *don't* have to do unbound forms in Access. All you have to
do to make it perform well is to properly filter the data you're
retrieving to meet the immediate needs of the user. That does *not*
require creating unbound forms.
>
For what it's worth, in 11 years of professional Access development,
I've implemented unbound forms less than a dozen times. But I've
implemented lots of forms that load a single record or a small group
of records.
>

>I do think that it is definitly the case the front end needs a
>70-80% rewrite to work with sqlserver

>
Well, first, how much work would it take to make the Jet back end
properly structured and to adapt the front end to work with that?
That part should *not* be considered part of the SQL Server
conversion at all. Once youv'e figured that out, then you'd want to
estimate what it would take to convert the back end to SQL Server,
and my bet is that it's relatively trivial (though it's very hard
without experience to forecast exactly which parts of the app will
need to be adapted/moved to the SQL Server).
>

>There must be reasons why people develop in
>VB.net.

>
I think it's because they don't know any better. VB.NET *is* better
for a VB developer who wants to do Internet-enabled apps. But for
Access developers, it lacks a lot.
>

>This is how I think of it;
>>
>Access Advantages
>Developing a data access layer is where all the work is for
>VB.Net. Access has already done this, so there is no work.

>
I think you're underestimating a lot of what Access has to offer.
>

>VB.NET
>The user interface is much quicker to develop robustly in VB.Net
>utilising inheritance and user controls.

>
Huh? Since when? Access has a whole host of controls and objects
specifically designed for use with databases, most of which
VB/VB.NET lack. Sure, VB.NET is better with databound controls than
VB, but still there is nothing to compare to an Access continuous
form for ease of use. And the event models are much poorer for the
tasks of data editing than the Access form/control event model.
>

Yeah, you are right, tho there are certain difficulties to do a few things
on continuous forms...for example say having an editable textbox that shows
tax inclusive/exclusive and binds the exclusive value to the field is a bit
of a mess...

I guess I like the fact that in VB.Net I know it can be done, even if it
takes longer, I know I can get it to do whatever is required, I dont have
that confidence with access....

I think probably this and this reason alone is why alot of people lean
towards VB.net based things...

One instance of this is, say the toolbar, in VB.Net we have it different
depending on the user who is logged in, in Access we just pop up a msgbox
saying you do not have security to use this...its no biggie, but its just
not eligant...weath er the boss wants to pay for that kind of elegance I
guess is what counts...

>Data validation is easier

>
Howso?
>

Well ya can just have inherited validation that just requires one line of
code...in access I've always had a whole lot of procedural based validation
and sometimes alot of if thens...but maybe this is my inexperience
showing...but I guess its not a big thing, certainly nothing to base a
migration on...

>Im just reading a post by Albert D Kallal, lol and David W Fenton,
>and all the guru's, lol, I think is a good thread that has alot of
>merit.
>http://groups.google.com/group/comp....ss/browse_thre
>ad/thread/8053a3b03a7162c 6/879440056ce94e1 b?hl=en&lnk=st& q=vb.net+v
>s+access#87944 0056ce94e1b

>
That thread certainly makes the point about unbound forms not being
required to create a good app in Access.
>

>right now thats taking a long time to read :) I think it may boil
>down to a decision as to weather the project is big enough to
>warrent developing the data access layer....but clearly I need to
>do more research....as those 3 things were the only I could think
>of off the top of my head....

>
I would vote against the data access layer. But, of course, I'm
operating without first-hand information about the app.
>
I think that many people from the C/S side of things overengineer
their apps, and a separate data access layer to an app that is
currently operating with a Jet back end sounds like complete
overkill. If a data access layer is necessary, then the app couldn't
work at all with a Jet back end.
>

You are quiet probably right here also, unfortunatly im out of my depth to
argue any different :(

John

[NewsGuy]

Re: Migrating away from MS-Access


"(PeteCresswell )" <x@y.Invalidwro te in message
news:br8lh3d0gf pdf7gmk8ph6fork 9tniofrjm@4ax.c om...

Per NewsGuy:

>I think
>>once we start having to do things like unbound forms and massive rewrites,
>>while it would still be quicker to go access, I dont consider vb.net to be
>>hugly more expensive.

>
I keep hearing that from people who probably know, but for the
one app that I took the time to develop in both VB6 and MS Access
the ratio was somewhere between three and five to one (got it
recorded somewhere... but can't recall exactly).
>

I guess I consider VB6 and .net a different kettle of fish, mainly cause
inheritence makes alot of things so much quicker to develop with...
Right now, in my feeble state of mind, id say 1:3
access:vb.net.. .........but im in no way qualified to state this, so I
probably shouldnt have written it...

But based on the dollars billed by a large company's IT
department when they replaced two of my MS Access apps with
net-centric (I know for sure one was .Net... not sure about the
other) I'd expect the .Net ratio tb more like 10:1.
>
Been meaning to master enough of .Net to prove or disprove that
suspicion for about five years now... but never got around to it.
>
Maybe you can report back if/when you do it.
>
Also... there are those who would argue that in the whole life
cycle of an application, the initial development time is
chickenfeed anyhow.
--
PeteCresswell

Yeah, I'd agree...200% agree....ya gotta admit though it is difficult to
appreciate that when ya got say 3 months of nobody seeing anything actually
work..."Whats that shy nerdy computer guy do all day, he just sits there and
types and types"....he *must* be crazy lol...

[SmartbizAustralia]

Re: Migrating away from MS-Access

Wow, what a discussion!

I have been developing in access for the last 15 years, not because
that's all I know but it is so easy to create fast useful
applications!
And yes I also develop with sql server and vb.net...
A good access developer can create a system in half the time of a .net
guy.
Even .net applications can be better prototyped in access first.

So, fix the structure and then port to .net if that is what you have
to do....

The biggest issues you see are:

Bad coding and bad database design.

Yes, move the backend to sql server but do this in a smart way.
Use stored procedures wherever you can and don't query huge
recordsets...e. g be smarter by filtering first!
Just read John Viescas's book on building access applications.

So you really need to do a current e-r plan and future e-r plan and
make the necessary steps to achieve it.

If your users mainly want reports, then do these with stored
procedures and reporting services...this reduces the complexity and
enables your users to get their information securely over the
internet....

You will never win against die hard .net coders until they get the
sack for taking so long and not delivering....

One of the biggest frustrations are pure developers who really don't
understand databases and how to get the most out of them which is
quite a large proportion. If that wasn't true, I wouldn't be so busy.

Bottom line, you need a database specialist, not a developer...... .

Regards,
Tom Bizannes
Sydney, Australia

[David W. Fenton]

Re: Migrating away from MS-Access

"NewsGuy" <john@nospam.co m.auwrote in
news:fff2ee02vk 2@news3.newsguy .com:

I guess I consider VB6 and .net a different kettle of fish, mainly
cause inheritence makes alot of things so much quicker to develop
with...

I think you over-rate the value of inheritance.

And in regard to the comparison to Access, VB and VB.NET are really
quite comparable in lacking Access's design that is specifically
architected for database applications.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[(PeteCresswell)]

Re: Migrating away from MS-Access

Per David W. Fenton:

>I think you over-rate the value of inheritance.

I'm with David on this one - again.

The flip side of inheritance (as opposed to cloning) is that
if/when you change the object, you need tb *really* careful of
unforeseen consequences to the object's consumers.
--
PeteCresswell

[Tony Toews [MVP]]

Re: Migrating away from MS-Access

"NewsGuy" <john@nospam.co m.auwrote:

>The table names have primary key's called "ID" for a good 75% of the tables
>which presents a big problem with automated changes...urrgg g...this makes me
>consider a manual rewrite...

Could you expand on this a bit more?

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/

[rkc]

Re: Migrating away from MS-Access

NewsGuy wrote:

>
I guess I consider VB6 and .net a different kettle of fish, mainly cause
inheritence makes alot of things so much quicker to develop with...

Are you talking about customizing GUI widgets here or creating the data
layer that people have told you you don't need?