Access vs. Visual Basic

[Thomas Kroljic]

I currently have an Access 2000 application with 20 - 25 users accessing the
application via Terminal Services with each user getting their own frontend
(the backend is data only). I've enjoyed using Access over the last 5 years.
The application has a lot of VBA coding behind each form (over 375 forms,
130 tables, 500 queries, 100 reports, and 9 large VBA modules).

I'm at a point where I would like to re-architecture the application and I'm
not sure if I should maybe use Visual Basic 2005 instead of Access (2007 or
some other version)

The data itself is roughly about 1gb. I was thinking of moving this to our
MS SQL server because of the ability to handle transaction better and
backups.

So, I'm looking for any input or suggested readings that might help me make
a decision. I am interested in what other people looked at when they came to
the same bridge.

Any suggestions would be greatly appreciated.

Thank you,
Thomas

 

[Van T. Dinh]

I certainly think the move to MS SQL Server BE is an excellent idea.
However, I can't see any reason to spend time and resources porting to
Visual Basic 2005 an Access application that has served you well.

Note that there will be a fair bit of work moving to MS SQL Server BE to
take advantage of the MS-SQL Server features. For examples, codes that
involve Recordsets will have to be changed, possibly to ADO code istead of
DAO code, porting Queries to Pass-Through Queries / Views or Stored
Procedures and general changes to be more "client-server oriented"

 

[Guest]

If you are going to port to anything, you might as well
start the port while retaining Access.

If you are changing to a SQL Server backend, one thing you
will want to do is get rid of any DAO transactions. (DAO
transactions are broken against SQL Server). That means rewriting
any transactions as stored procedures, run using ADO.

That will be more or less independent of Access or VB -
you are moving the code out of the front end -.

If you keep moving down that path, eventually you will find
that you have moved everything interesting or complex out
of the front end.

At which point, conversion of the FE may be an option.

(david)

 

[Albert D. Kallal]

The data itself is roughly about 1gb.

Is the above that size after a compact and repair?
(and, is there any picture data???).

If your data is that size after a compact, and you don't have any image data
in the database, then
that is quite amazing, and sounds like you done a really great job.

I certainly think that when the back end database size approaches 1 gig, you
are well on your
way to be using sql server.

I was thinking of moving this to our MS SQL server because of the ability
to handle transaction better and backups.

Again, a great idea for considering sql server. It is also more reliably
..

So, I'm looking for any input or suggested readings that might help me
make a decision. I am interested in what other people looked at when they
came to the same bridge.

Well, first, I think some in this posts miss-read that you were considering
"old" vb6, and reading your post again you were talking about the "new"
version, or so called .net version of vb. So, sure, moving to Vb is
possible.

However, moving from ms-access to vb or whatever has a large cost to it.
Likely perhaps a year, or more of cost (that means your costs are perhaps
more then $75,000 worth of work into that project). And, even by normal
standards, often these other development tools take 2 or more times the
effort to product the same results (so, now we talking as much as $150,000,
or more of work).

Only you can really decide, but after you spend $150,000 and have the same
features as what you have now...what exactly have your accomplished?

About the only real reasons I can see migrating this whole system to the
..net environment is future considering for building a web based system.

on the other hand, you could move the back end data to sql server, and keep
the front end. Once you make that bridge crossing, at least your data is now
able to be used by web developers, or .net developers for the next gen
software. (and, only you can decide if you need the next gen software - you
ONLY want to do this for reasons of increased connectively, or as mentioned,
a march towards some type of web based system). do you want your customers
to deal with this information on-line? Do your customers need to "interface"
some way to this data? (web services for example - ups parcel tracking
anyone for example!!!).

So, just converting to vb.net is not really much a gain here for you
*unless* you have future goals to use some of these technologies.

However, moving that data to sql server likely is a good first step for
future proofing this application, even if you continue to use ms-access as
the front end interface for the foreseeable future........

 

[Rick Brandt]

Out of interest, which "tool" do you think has been "consigned to the
trash": MS SQL server, Visual Basic 2005 or Access?

Jamie.

Be honest. Most people that want to redo functioning apps in a different
development environment do so because they feel the one being currently used is
a technilogical dead end. I have no problem with someone who uses classic VB
and wants to continue developing apps in classic VB as long as they do the job.
Heck, I'm still happy with Access 97.

However; I would never suggest completely re-writing an existing non-Access 97
app in Access 97. What would be the point? The same applies to vb 2005.
Whether the reasons are legitimate or not, vb 2005 is a dead end at this point.

 

[Rick Brandt]

Porting Access apps aside (on which I agree with you), are you saying
VB.NET has been "consigned to the trash heap of the development world"
and is a "dead end" generally? I prefer C# myself but I know that
VB.NET is the favoured .NET language of Excel VSTO developers e.g. for
its support for missing optional procedure parameters, the equivalent
C# code being much more verbose. Also, Visual Studio provides tools to
migrate code from VB class to VB.NET, which can be a quick may to get
your code base to .NET. Such consideration are not so relevant to
Access users, perhaps, but still it makes VB.NET an attractive option
for some in the wider "development world", don't you think?

Jamie.

VB.Net is not what was being discussed. The OP said VB 2005.

In my opinion VB.Net is far enough removed syntax wise from VB that anyone
migrating to dot-net would be better off just going to C#.

 

[Rick Brandt]

Isn't VB 2005 the same as VB.NET? Or should I ask, what do *you*
consider VB 2005 to be?

I have never heard of VB.Net referred to as anything besides VB.Net. If VB
2005 is the same thing then I retract that part of what I said. I assumed
that VB 2005 was the last version of VB "classic".

 

[Thomas Kroljic]

Thank you very much for everyone's input. When I mentioned VB 2005, I am
under the impression with my limited knowledge of this software package that
it works under .net and is not the "classic" vb development language of past
years.

I think the advice that I should move the BE database to MS SQL is good
advice. Just moving the data from the JET database to SQL server will be a
big enough project in itself. Once this is completed and running OK, then
and only then, I can decide what future functionality the FE should have.

What are your thoughts on upgrading my FE database (Access 2000) to the
latest version: Access 2007.

Thanks to all.
Thomas

 

[David W. Fenton]

Seriously, why would you want to re-invent a working app and on
top of that do so with a tool that will be several times more work
than using Access? And on top of that again use a tool that has
already been consigned to the trash heap of the development world.

The main question is:

What do you hope to gain by using something other than Access? Is
that benefit worth the 3X development work?

 

[David W. Fenton]

What are your thoughts on upgrading my FE database (Access 2000)
to the latest version: Access 2007.

It would be very foolish to do so. Keep it in 2000 and it will run
in A2K7 (with some notable exceptions).

 

[Thomas Kroljic]

David,
Are you suggesting that I just port all the forms/queries/modules...over
to the A2K7 environment and go from there? Or are you suggesting that I just
keep the A2K running?

Thomas

 

[(PeteCresswell)]

Per Thomas Kroljic:

I currently have an Access 2000 application with 20 - 25 users accessing the
application via Terminal Services with each user getting their own frontend
(the backend is data only). I've enjoyed using Access over the last 5 years.
The application has a lot of VBA coding behind each form (over 375 forms,
130 tables, 500 queries, 100 reports, and 9 large VBA modules).

I'm at a point where I would like to re-architecture the application and I'm
not sure if I should maybe use Visual Basic 2005 instead of Access (2007 or
some other version)

The data itself is roughly about 1gb. I was thinking of moving this to our
MS SQL server because of the ability to handle transaction better and
backups.

So, I'm looking for any input or suggested readings that might help me make
a decision. I am interested in what other people looked at when they came to
the same bridge.

Two separate issues/two separate judgments: front end and back end.
---------------------------------------------------------------------------
Back End: whatever floats your boat - especially in the light of mission
criticality and the judgment of IT.


Front End: If you have a record of the man hours you put into the current
version, I'd multiply them by a minimum of 3 to estimate a version using
VB6.

That's my experience from doing two apps that way in VB6. Others have opined a
factor of five.

For one fairly large app that I wrote, and which IT converted to an Oracle back
end and a DotNet front end - while adding maybe 40% new functionality, the
factor was a little over 100. ($225,000 vs 23 Mil)

 

[Van T. Dinh]

I think what David said was that you can keep your Front-End as it is,
(.i.e. the Front-End MDB file in A2000 file format) and you can use Access
2000, Access 2002, Access 2003 and Access 2007 software to run this
Front-End.

You are aware that each user should have a separate copy of the Front-End,
of course.

 

[David W. Fenton]

I think what David said was that you can keep your Front-End as it
is, (.i.e. the Front-End MDB file in A2000 file format) and you
can use Access 2000, Access 2002, Access 2003 and Access 2007
software to run this Front-End.

That's precisely what I was suggesting.

I'm not sure I'd recommend it, though, as there are a host of things
that are completely broken in A2K7.

 

[Thomas Kroljic]

Each user has their own copy of the FE database.
They only share the backend database.

 

[Thomas Kroljic]

<<host of things that are completely broken in A2K7>>

What major items are you talking about?

 

[Thomas Kroljic]

WOW!!!

Per Thomas Kroljic:

Two separate issues/two separate judgments: front end and back end.
---------------------------------------------------------------------------
Back End: whatever floats your boat - especially in the light of mission
criticality and the judgment of IT.


Front End: If you have a record of the man hours you put into the current
version, I'd multiply them by a minimum of 3 to estimate a version using
VB6.

That's my experience from doing two apps that way in VB6. Others have
opined a
factor of five.

For one fairly large app that I wrote, and which IT converted to an Oracle
back
end and a DotNet front end - while adding maybe 40% new functionality, the
factor was a little over 100. ($225,000 vs 23 Mil)

 

[David W. Fenton]

<<host of things that are completely broken in A2K7>>

What major items are you talking about?

Allen Browne is very thoughtfully keeping an article on the subject
up-to-date on his website:

http://allenbrowne.com/Access2007.html

He doesn't mention the issue with replication (i.e., DAO direct
synchs that work just fine and dandy in A2K3 and all previous
versions fail in A2K7 and MS acknowledges it and says you have to
switch to JRO, even though they've been deprecating ADO and its
stepchildren in favor of DAO for Jet data for a couple of years now;
a hotfix is being contemplated), though that's only relevant to
those who are using replication in their apps already, I guess.

 

[(PeteCresswell)]

Per Thomas Kroljic:

WOW!!!

If you're reacting to the $225,000 vs 23 Mil; it may not be quite as bad as it
sounds.

I developed the original app as a sort of one-man gang: no red tape, nobody
telling me what to do, no rules, no regulations - just gallons of coffee and
some really smart and demanding-but-reasonable users. A purely tactical effort.

The replacement was a big IT project in a company that gone far, far, far down
the strategic path: hugely-detailed specs, thousands upon thousands of pages of
requirements, test plans, documentation, formal acceptance tests, signoffs, and
so-forth.

At the times I saw numbers, the team was around 50 people - but over the 4 or so
years the project ran people kept coming and going so there was substantial
slippage in getting newbies up to speed and it seemed to me like the team was
top-heavy: too many coordinators, project managers, and supervisors for my own
taste.

Also, now that I think of it, who am I to say that the new system only had 40%
more functionality? I'm probably biased...

But I keep coming back to two observations:
--------------------------------------------------------------------------------
1) The new system definitely doesn't have 100 times the functionality, nor does
it have fifty... or twenty-five.... In fact I can't see *anybody* claiming
more than ten.

2) The users didn't want the new system. They were happy with the old one right
up to cutover day and they're still not what I'd call ecstatic over the new
one.
--------------------------------------------------------------------------------

Some people in some big IT departments tend to knock guys like me - and I have
to say that in my case they have some legitimate complaints when users say "Why
can't you deliver as fast as that guy?"

But all-in-all, that particular experience made me feel pretty good about my
economic worth as a freelance tactical developer.