Migrating away from MS-Access

[John]

Hello there, Im cursing my place of employment...and its taken me a month to
realise it...

The scenario:
Ive just stepped into a role to migrate an access database to VB.Net. The
access database runs on terminal services and supports approximatly 25-30
users. It is crapping out big time, corrupted data, changes to the front end
are difficult for someone unfamiliar with the system (me), the table
structure is bad...really bad....there is a website attached to the backend
aswell...

At some stage we have to migrate the access backend to SQL Server. The
problem is that its all in use and we cannot have downtime.

What approach is best? How can I even fix this dodgy design?

I could just run with it and build dodgyness over dodgyness...fark...haha
god help the next guy if i did that...
I could convince management to hire someone else to develop in parrallel on
both systems so the backend is fixed?
I could develop the new VB.net front end with changes in mind, then fix the
database structure when we migrate
I could migrated access backend to sqlServer backend and continue to use the
Access front end, I very much suspect the front end will fall into a big
pile of crap...

I guess Im not really looking for answers, just venting and curious to see
what other people have delt with...
Thanks for reading!
John

[NewsGuy]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

>It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they dont
really want that though, they want people to go to dotnet..

What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a long time ago I
researched it and only ever read bad things, my brother did it once and I
believe he had nothin but trouble...

At current for this project it would be unnecessary to deploy it...

I was discussing with the old guy today, he said that PDF's via terminal
services are chugging things up (We have alot of PDF's attached)....weather
thats a reason for deployment I dont know...Im unsure if downloading the PDF
would be quicker than going term services...apparently users like to click
the scrolls up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at this
stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

John

[NewsGuy]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

>What kind of instances would you use BeforeUpdate and AfterUpdates
for? Why do I feel like a noob asking that :) Maybe I am! Hmm nah
my memory has somewhat faded...

BeforeUpdate, at the form or control level, allows you to validate
data before it is saved and inform the user that there's a problem.
This is crucial in avoiding tying up the server with the validation.

AfterUpdate is essential for doing actions after the update is
completed. For example, you might want to requery another open form
or rowsource-bearing control in the AfterUpdate event.

These are essential events for all database programming, and you
don't have to do anything but write the code specific to your app
when using Access. With .NET, you have to do it all yourself, right?

Ahh ok..good explanation, makes sense...You;d think id be familiar with
that....thinking back I hardly even remember how I did validation in my old
project...

Yeah, In terms of doing the validation with .net....I just have a validation
procedure that I set to handle the onValidating event of every control on a
form which i find works well and is very easy...Im yet to do complicated
validation tho, I have not tried it with datagrids...sooo..... :) yeah id
imagine it will get rather complicated...

We have developer express components which have been heavily used. We use
their DXerrorProvider, which is kinda cool.......as for that whole
toolset....its chuggy, has a gazillion properties, methods....it looks
pretty...:) Might take me 2 years to learn how to use it
all....haha...yer....it doesnt sound good...in the beginning I spent alot of
time just familiarising with these tools...I dont think theyre a good
idea...but I guess thats a gaping hole showing my inexperience....shoulda
looked at the backend straight away...

I did manage to convince the old guy that dotnet is gonna take alot longer,
but its the boss who will have to make the decision. Old guy is still hell
bent on proceeding along his original path tho...

One good thing about dotnet is that when ya change a dataset column name, it
immediatly shows everywhere that needs to be fixed...Thats the quickest most
reliable way of tidying badly named tables/fields (better than
speedferreting access).....but hmm thats a nonissue really...thats just
something i learned today..

John

[NewsGuy]

"(PeteCresswell)" <[e-mail] in message
news:pa********************************@4ax.com...

Per David W. Fenton:

>>What problems do you have with Access deployment?

Biggest one I see is the prospect of a new release of MS Access
breaking my apps - however briefly.

Next biggest is IT's aversion to MS Access - however unfounded
and based on a faulty understanding of what MS Access is.
--
PeteCresswell

I think the aversion comes because there ARE limits on access.....there is
no limit on .NET, if it cant be done in .NET then ya probably cant do
it....well except for time....:)

Quiet probably a naive comment...but I guess coming from a naive perspective
is generally what most people think like....

John

[(PeteCresswell)]

Per NewsGuy:

>I think the aversion comes because there ARE limits on access.....there is
no limit on .NET, if it cant be done in .NET then ya probably cant do
it....well except for time....:)

My experience is that IT views MS Access applications and the JET
database back end as one and the same: basically a toy and not
suitable for any serious development. What they usually fail
to grasp is that "MS Access" is really a front end development
tool analogous to VB6 or VB.Net or PowerBuilder and the JET back
end is just one of many back ends that it can connect to.

I see a number of legitimate limitations on the use of MS Access
as a front end development tool - but the IT people I've known
who object to MS Access are not thinking of them; they're just
focused on the incorrect notion that a JET back end is part of
the deal for all MS Access apps.

Most of the reasons I tend to think of are "horses for courses"
kinds of things. Not ironclad reasons to avoid MS Access front
ends; but rather tradeoffs that one has to consider.

BTW this is from somebody who can't even do "Hello World" in
VB.net.
--
PeteCresswell

[(PeteCresswell)]

Per Rick Brandt:

>Common sense would dictate that those that
produce the fastest results that accomplish the required tasks for the least
cost would almost always be the ones chosen. Reality indicates otherwise.

I've heard it argued that the initial programming cost of a
system is only a small fraction of it's lifetime cost.

Don't know enough to judge that assertion - but it was being made
by people that management held in high regard.

I used to think that my target market was people who wanted the
most bang for the buck.

But now I've come around to believing that my real appeal is
being able to deliver a system that:
-----------------------------------------------------------------
1) Didn't require a lot of front-end time from the users.

2) Can be readily evolved as users discover their real needs
(without going before a committee...)

3) Can be delivered as release 1.0 - no matter how
rudimentary - within a certain calendar timeframe.
------------------------------------------------------------------

That's not to say that my hours billed are insignificant to the
clients... however the guys I'm doing a system for right now are
using me as an alternative to a 2.5 million dollar off-the-shelf
bond trading system... and if I ever bill more than 70k for this
thing I will be surprised....

So I wouldn't expect it tb a big deal to them if it took me three
or even five or six times the man hours to create the system
because I believe that what they *really* want is for Pete to
figure out the details of what they need; provide same without
their having to sign a thousand pages of specs in blood; and
evolve the system as needed with the least pain possible..
--
PeteCresswell

[David W. Fenton]

"NewsGuy" <jo**@nospam.com.auwrote in
news:fg*********@news1.newsguy.com:

I think the aversion comes because there ARE limits on
access.....there is no limit on .NET, if it cant be done in .NET
then ya probably cant do it....well except for time....:)

Quiet probably a naive comment...but I guess coming from a naive
perspective is generally what most people think like...

The difference is only between a specialized tool tuned for a
specific task and a general tool that is usable for any task. Access
has all these database-specific features that have to be
custom-built in .NET (or implemented using add-in classes). It's
designed for one purpose, and it's really unequalled at that task in
terms of suitability to purpose and speed of development.

Granted, you wouldn't use it to program a word processor.

But that would just show stupidity in not choosing the right tool.

I have yet to see an objection to Access-as-front-end that is both a
true drawback of Access and not also easily resolvable in some
fashion. Most of the objections are from those who are simply
ignorant.

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

[David W. Fenton]

"NewsGuy" <jo**@nospam.com.auwrote in
news:fg*********@news1.newsguy.com:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

>"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

>>It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they
dont really want that though, they want people to go to dotnet..

What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a long
time ago I researched it and only ever read bad things, my brother
did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

At current for this project it would be unnecessary to deploy
it...

I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with Access
deployment, since it's an outside issue entirely.

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

[Tony Toews [MVP]]

"Rick Brandt" <ri*********@hotmail.comwrote:

>As for the original topic there are a number of reasons why specific development
tools and technologies are chosen. Common sense would dictate that those that
produce the fastest results that accomplish the required tasks for the least
cost would almost always be the ones chosen. Reality indicates otherwise.

IT managers are generally clueless about the details of the technologies and have
drunk the koolaid of all the articles and such.

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/

[NewsGuy]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:fg*********@news1.newsguy.com:

>"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196 .97.142...

>>"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they
dont really want that though, they want people to go to dotnet..

What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a long
time ago I researched it and only ever read bad things, my brother
did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

>At current for this project it would be unnecessary to deploy
it...

I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with Access
deployment, since it's an outside issue entirely.

Well as soon as we deploy it outside of terminal services we'd be running
through a VPN, with sometimes poor/unreliable connections...I guess JET wont
handle that but I suppose itd be feasable with SQL Server?

John

[lyle]

On Oct 29, 3:50 am, "NewsGuy" <[e-mail]:

"David W. Fenton" <[e-mail] in messagenews:Xn**********************************@2 16.196.97.142...

"NewsGuy" <[e-mail] in
news:fg*********@news1.newsguy.com:

"David W. Fenton" <[e-mail] in message
news:Xn**********************************@216.196 .97.142...
"NewsGuy" <[e-mail] in
news:ff*********@news4.newsguy.com:

>>It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they
dont really want that though, they want people to go to dotnet..

>What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a long
time ago I researched it and only ever read bad things, my brother
did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

At current for this project it would be unnecessary to deploy
it...

I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with Access
deployment, since it's an outside issue entirely.

Well as soon as we deploy it outside of terminal services we'd be running
through a VPN, with sometimes poor/unreliable connections...I guess JET wont
handle that but I suppose itd be feasable with SQL Server?

John

I suggest that you convert to JDBC and SQLite with a GLOM front end on
Linux boxes.

Advantages?

To you:
A completely fresh start with technologies about which you are more
knowledgeable.

To me:
This thread is transferred to another newsgroup.
No more "old guy" pejoratives.

[NewsGuy]

"lyle" <ly************@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...

On Oct 29, 3:50 am, "NewsGuy" <[e-mail]:

>"David W. Fenton" <[e-mail] in
messagenews:Xn**********************************@ 216.196.97.142...

"NewsGuy" <[e-mail] in
news:fg*********@news1.newsguy.com:

>"David W. Fenton" <[e-mail] in message
news:Xn**********************************@216.19 6.97.142...
"NewsGuy" <[e-mail] in
news:ff*********@news4.newsguy.com:

>>>It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they
dont really want that though, they want people to go to dotnet..

>>What problems do you have with Access deployment?

>Well I dont have any really, and I have never done it, but a long
time ago I researched it and only ever read bad things, my brother
did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

>At current for this project it would be unnecessary to deploy
it...

>I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

>My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with Access
deployment, since it's an outside issue entirely.

Well as soon as we deploy it outside of terminal services we'd be running
through a VPN, with sometimes poor/unreliable connections...I guess JET
wont
handle that but I suppose itd be feasable with SQL Server?

John

I suggest that you convert to JDBC and SQLite with a GLOM front end on
Linux boxes.

Advantages?

To you:
A completely fresh start with technologies about which you are more
knowledgeable.

To me:
This thread is transferred to another newsgroup.
No more "old guy" pejoratives.

I dunno if there is as helpful people in GLOM newsgroups...

I apologies for the "old guy" pejoratives, I didn't think I was doing that
too bad after the first few posts, but perhaps I am...
John

[David W. Fenton]

"NewsGuy" <jo**@nospam.com.auwrote in
news:fg********@news5.newsguy.com:

>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

>"NewsGuy" <jo**@nospam.com.auwrote in
news:fg*********@news1.newsguy.com:

>>"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
message
news:Xn**********************************@216.19 6.97.142...
"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

It'd be nice if micrsoft made access into a full on
development environment with easier methods of deployment...I
guess they dont really want that though, they want people to
go to dotnet..

What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a
long time ago I researched it and only ever read bad things, my
brother did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

>>At current for this project it would be unnecessary to deploy
it...

I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with
Access deployment, since it's an outside issue entirely.

Well as soon as we deploy it outside of terminal services we'd be
running through a VPN, with sometimes poor/unreliable
connections...I guess JET wont handle that but I suppose itd be
feasable with SQL Server?

Yes, of course. Everyone has told you repeatedly that much of the
problem with the app as you've described it would be best addressed
with a more appropriate back end. Jet can't be used across a WAN,
and anyone with any competence at all in Access would know that.

This is not an *Access* deployment issue -- it's a matter of
choosing the appropriate back end for the app and its deployment
requirements. If WTS isn't working, and you're blocked from other
avenues by the Jet back end, then it's pretty clear that someone
made some bad design decisions somewhere along the line.

But it still isn't an Access deployment issue.

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

[NewsGuy]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:fg********@news5.newsguy.com:

>>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196 .97.142...

>>"NewsGuy" <jo**@nospam.com.auwrote in
news:fg*********@news1.newsguy.com:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
message
news:Xn**********************************@216.1 96.97.142...
"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:
>
>It'd be nice if micrsoft made access into a full on
>development environment with easier methods of deployment...I
>guess they dont really want that though, they want people to
>go to dotnet..
>
What problems do you have with Access deployment?

Well I dont have any really, and I have never done it, but a
long time ago I researched it and only ever read bad things, my
brother did it once and I believe he had nothin but trouble...

I suggest that you do some investigation before making
recommendations on the basis of your hazy emotions about Access
deployment.

At current for this project it would be unnecessary to deploy
it...

I was discussing with the old guy today, he said that PDF's via
terminal services are chugging things up (We have alot of PDF's
attached)....weather thats a reason for deployment I dont
know...Im unsure if downloading the PDF would be quicker than
going term services...apparently users like to click the scrolls
up and down alot, and that chugs it.....i know thats been
discussed earlier in this thread, all I can go on is his word at
this stage...

My feeling is he is probably right...so I might have to look at
deployment...I am unsure....

I really don't see that this has anything at all to do with
Access deployment, since it's an outside issue entirely.

Well as soon as we deploy it outside of terminal services we'd be
running through a VPN, with sometimes poor/unreliable
connections...I guess JET wont handle that but I suppose itd be
feasable with SQL Server?

Yes, of course. Everyone has told you repeatedly that much of the
problem with the app as you've described it would be best addressed
with a more appropriate back end. Jet can't be used across a WAN,
and anyone with any competence at all in Access would know that.

This is not an *Access* deployment issue -- it's a matter of
choosing the appropriate back end for the app and its deployment
requirements. If WTS isn't working, and you're blocked from other
avenues by the Jet back end, then it's pretty clear that someone
made some bad design decisions somewhere along the line.

But it still isn't an Access deployment issue.

Yeah I realise that JET wont do wan, I guess I meant to say we need to break
away from terminal services, and was asking if sqlserver would do ok with
access deployed...which in retrospect is a stupid question and to boot wasnt
worded very well at all...

Thanks for spending your time on me..
John

[(PeteCresswell)]

Per David W. Fenton:

Jet can't be used across a WAN,

Where does LAN end and WAN begin?

I'm thinking of a multi-building campus...
--
PeteCresswell

[David W. Fenton]

"(PeteCresswell)" <[e-mail] in
news:k0********************************@4ax.com:

Per David W. Fenton:

>Jet can't be used across a WAN,

Where does LAN end and WAN begin?

I'm thinking of a multi-building campus...

When bandwidth drops beneath 10Mbps, or when it switches from wired
to wireless.

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