Access to Visual Basic Conversion???

[Brian]

I have spent the better part of the last 3 years writing a
relatively complicated MRP system in MS Access.
Currently, I am using Access 2003, but the database is
saved in Access 2000 format. This database consists of an
Access front end with 33 queries, 101 forms, 51 reports,
and 24 modules using an Access back end with 49 tables.
If I printed all the VB code behind the forms and reports,
it's almost 2,400 pages in a Word document with almost
100,000 lines. I write this so you can imagine the scope
and size of this database.

I am now at the point where I am starting a project to
convert the front end to Visual Basic and the back end to
SQL. And thus, my question. Does anyone know of a
translation package to convert the front end to VB.NET?

I spent some time looking into this an had decided on
AccessToVB from Greenwich Financial Modeling
(www.gfminc.com), but found that the company is apparently
out of business as all e-mail is returned undeliverable
and the phone number is disconnected. So I was hoping
that someone out there might have had this problem before
and knew of software to do this. Needless to say,
rewriting the whole thing from scratch would be incredibly
time consuming.

Thanks in advance to anyone that can help with this!

 

[Lynn Trapp]

Don't rewrite it. Just convert the backend to SQL Server and keep the
frontend in Access.

It's possible that GFMINC is out of business because they weren't successful
getting the utility to convert from Access to VB to work.

 

[Albert D. Kallal]

This database consists of an
Access front end with 33 queries, 101 forms, 51 reports,
and 24 modules using an Access back end with 49 tables.

That is not really a very big application at all. I have produced
applications
with 160 forms, 73 reports, and 181 queries in less then 3 months.
And, there is 27,000 lines of code. So, applications in the 75 to 175 range
are kind of "medium" sized. I always consider applications with over 200
forms
starting to get large. On the other hand, I tend to use a lot of forms for
user interface stuff. You can seem some examples of what I mean here:

http://www.attcanada.net/~kallal.msn/Search/index.html

And, for all reports, I also tend to use additional forms:
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

And, since your application is starting to have a lot of features...then
likely
you have incorporated menus...right? Here is some good examples:
http://www.attcanada.net/~kallal.msn/Articles/UseAbility/UserFriendly.htm

On a typical developer team, developer output can vary as much as 100 times.
So, a good developer in 5 days of work can actually output 50 or more days
of
work that "average" developers can produce. I have meet developers who on a
average day can produce more then 1000 lines of c++ code per day!!

Right now, the resulting above mde is about 6 megs in size, and as mde can
be
zipped into 1.5 megs (it *almost* fits on a floppy disk). So, it not very
big in terms of what large ms-access applications look like.

I am now at the point where I am starting a project to
convert the front end to Visual Basic

Why do you need to convert the application to VB? VB does not have a report
writer that is much good. Also, are you trying to convert to VB.net? (you
are not clear here?). You mention a VB converter tool, and then go on to
mention VB.net? (which are you trying to convert to?). Converting the
application to VB will not get you a easy path to converting to VB.net.

I guess the real question here is why do you need to convert the front end
to VB? You can most certainly convert the back end data part to sql server,
and this is common setup with ms-access (ms-access makes a great front end
to sql server).

So, again, is the goal to use sql server here? (do you need sql server
here?). Perhaps your goal is to both convert to VB (don't know why you need
to do this), and another goal is to use sql server? You can most certainly
use the up-sizing wizard to move the tables to sql sever, and *most* code
should continue to work.

Since VB, and VB.net don't have things like continues forms, and those
environments also don't have the concept of a sub-form, and those tools
don't have multi-column combo boxes (and don't have things like not in list
event), then many "concepts" and design ideas you used will not easily
convert to these environments. And, what do you plan to use for a report
writer?

So, when starting to use a new environments, the designs, the philosophy and
approach to problem solving will be quite different,a nd thus a automated
converting is difficult.

Obviously, your good designs were based on having ms-access as the
development tool. Many of the designs, and how things work would have been
done differently if you were using VB, or vb.net.

Anyway, I guess you need to define the whats, whys etc as to the need to
convert to vb. (I see few, if any advantages gained by converting to vb at
this point -- there are often some good reasons to convert from ms-access to
vb...but it is not clear if these apply to you.). I guess only you can make
this decision.

VB does distribuite somwhat easer then ms-access. However, you *can* build a
reasonalbe stand alone royally free install of a ms-access application.

 

[Guest]

As the number of records in this database has grown, we
have noticed a slow-down over time. Some things that used
to take only a second or two are now taking upwards of 10
seconds. I repair and compact both the front and back
ends on a regular basis and since the slow-down is
gradually happening over time, I can only attribute this
to the increase in records. I have been able to speed
some things up by revising the programming, but overall
it's slower that it was when the number of records were
less that they are now.

This database has been in constant development for the
last 3 years, with some "modules" having been totally
rewritten 5-6 times. The rewrites are based primarily on
user requests. As I am primarily a self-taught Access
developer and VBA programmer (with a few MS courses under
my belt), I rely on outside people who "know" Access to
help when neccesary. One of those people is an individual
who is a part-time instructor/part-time consultant at the
training center where I took my Access VBA course. He
mentioned that I should start looking at converting the
front-end to VB and the back end to SQL to accomodate the
future growth of the system and increase the speed,
security, and stability. After doing a little research, I
decided that this would probably be a good idea,
especially since converting the front end to VB would
allow me to reduce the overhead that Access takes for the
development environment that no one but I use.

As far as my database is concerned, two of those tables
are menus. I have 43 forms which are "Continuous forms"
or what I call Scrolling Forms. The remainder of the
forms are non-bound forms (what I call Data Entry Forms)
in which data is copied from the tables to the forms via
VBA and is saved back to the tables via VBA only after the
user clicks a "Save" button. This is so that the users
have the option of abandoning any changes they have made
to the record.

Most of my Scrolling Forms have their Record Source,
Filter, and OrderBy properties populated by VBA based on
criteria chosen by the user on the Data Entry forms.

I am guessing that this is not the way most people would
have designed a database, but a good chunck of it was
written before I had my first Access course, so I
was "winging" it. And so far, it has proven to be a
relatively stable system. Its just that we are now
looking to add the SQL back-end and believed that a VB
front end would be a natural beginning.

As far as VB vs. VB.Net, when I bought my software, VB6
was no longer available. VB.Net was, so I assumed it was
the natural upgrade to VB6. Is this not right?

I have not yet taken any VB courses, so I do not know what
VB.Net will and will not support in comparison to Access.
Obviously if VB.Net does not support Continuous Forms or
SubForms, I'm going to have a problem there. And not
knowing anything about VB, I wouldn't know how to work
around that yet.

My idea was to buy one of these conversion tools and let
it convert one of my smaller databases to see how it works
and what I must do to "fix" the things it couldn't
convert. With the "demise" of AccessToVB, I found a
couple of others that I might consider, but have more
research to do. (see
http://www.diamondedge.com/products/Convert-Access-to-
VB.html, http://www.iriesoftware.com,
http://www.soft14.com/Software_Development/Utilities/Evolut
ion_5981_Review.html)

I would appreciate any further advice you have.

 

[Albert D. Kallal]

As the number of records in this database has grown, we
have noticed a slow-down over time. Some things that used
to take only a second or two are now taking upwards of 10
seconds.

You don't mention how large your data sets are. A file with 100,000 records
is consider SMALL.

I repair and compact both the front and back
ends on a regular basis and since the slow-down is
gradually happening over time, I can only attribute this
to the increase in records. I have been able to speed
some things up by revising the programming, but overall
it's slower that it was when the number of records were
less that they are now.

Moving to sql server will not fix most performance problems. In fact, there
are weekly posts in the sql server newsgroup about how moving data from a
JET mdb file to sql server actually runs slower (and, in most cases it DOES
run slower). I repeat: sql server is slower then ms-access on the same
hardware box.

The only way you get better performance is to have good designs. If you try
and use sql sever with poor designs, you will NOT get a performance
increase. I cannot stress this point enough:

Using sql server will NOT fix performance problems on it own..you
DESIGNS must take advantage of sql server. SQL server will NOT fix
performance problems on its own!

One of those people is an individual
who is a part-time instructor/part-time consultant at the
training center where I took my Access VBA course. He
mentioned that I should start looking at converting the
front-end to VB and the back end to SQL to accomodate the
future growth of the system

Up to this, the above advice makes sense. But the above ONLY refers to the
data part...it should not refer to ms-access. If that person means that
ms-access is not up to future growth..then that person is dead wrong.

and increase the speed,
security, and stability.

Using VB is NOT any faster nor more secure then using ms-access with sql
server. After all, they are both using sql server...why would there be any
difference in performance? Further, the programming language and compiler
(p-code) for ms-access is the SAME as VB6 anyway. Further, as mentioned,
there is going to be no performance difference here at all. There are
companies right now with 1000 ms-access users all at the same time working
on the same database on sql server. VB on it own will NOT give your more
performance then does ms-access. The only advantage you can get in terms of
performance is if you go to a 3 tried design, and I don't think this is your
case yet.

After doing a little research, I
decided that this would probably be a good idea,
especially since converting the front end to VB would
allow me to reduce the overhead that Access takes for the
development environment that no one but I use.

The runtime package for vb.net is in excess of 20 megs in size also. There
is little difference in terms of the resources, and "size" of stuff
required. The runtime package and deployment kit for ms-access is about 33
megs in size on those pc's that don't have ms-access.

As far as VB vs. VB.Net, when I bought my software, VB6
was no longer available. VB.Net was, so I assumed it was
the natural upgrade to VB6. Is this not right?

Yes, VB.net is the "next" new thing, but it certainly is a complete paradigm
shift from VB6, or ms-access (both use the same syntax and development
approach). VB.net is different, and is not compatible with VB6. VB6 is thus
much closer to ms-access, and in fact as mentioned, shares the same
programming language.

I have not yet taken any VB courses, so I do not know what
VB.Net will and will not support in comparison to Access.
Obviously if VB.Net does not support Continuous Forms or
SubForms, I'm going to have a problem there. And not
knowing anything about VB, I wouldn't know how to work
around that yet.

As mentioned, it is question of a paradigm shift. For example, often people
need a tree like structure (in VB this called a treeView control). You see
this often on the "left" side where you an click on a "+" sign, and the
right side expands (the windows explore has a treeview). So, if you had been
developing in VB, or VB.net..you would used these things...and going to
ms-access, you would go...golly...no treeview...how horrible!. This same
concept and philosophy applies to your loss of continues forms (but, it
true..those other environments don't have that..so they use datagrid
controls).

Also, remember that ms-access is a program and system for data. VB is a
system that lets you write games, and all kinds of things. It is NOT
designed for editing and working just with data. As a result, you CAN user
VB to work with data..but it usually takes about 2 or 3 times as much work.
(thus, your 3 years of work would have taken 9 years).

I talk about what you use in VB vs. Ms-access here in terms of grids:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

By the way, the first screen shot in the above shows what I used in place of
a treevfeiew control. So, you can get the same results...(a drill down), but
have to use a different design approach. I gong to repeat this lesson for
you, else it will cause you the most grief in your life:

You must change your ides and concepts of design when moving from one
development platform to another. You can achieve the same end results, but
the tools, and how you "think of" the design will have to change. So, if you
talk to a average VB, or VB.net developer, they don't miss, nor care much
that no such thing as a continues form is not available..since they never
had them!

Please read the above again to fix this in your mind. The #1 mistake
developers make when moving to a new set of tools is thinking of OLD ideas
and old concepts that they used in the old system will still work, and be
available in the new system. ...this does NOT work. I sure the first time
you learned about a sub-form it was strange concept..now you likely even
forget why it was strange!! Anyway, you don't have sub-=forms in those other
environments...yet all developers make lots of software with those other
tools....so, you have to change your ways...

My idea was to buy one of these conversion tools and let
it convert one of my smaller databases to see how it works
and what I must do to "fix" the things it couldn't
convert.

As mentioned, due to conceptual differences..things don't convert well. And,
converting to VB6 is complete different then is VB.net.

You also have not mentioned the size of some of the larger data tables
(number of records is import here).

So, using VB.net, or VB6 or c++ will not give you any more performance then
will ms-access being used with sql server. In other words, why does the
speed of sql server change (where all the data is) change when you use a web
page, c++, or ms-access to get that data? (answer: it does not change...and
sql server don't even know if you are using ms-access, or a web page....)