Re: Access to Visual Basic Conversion???

anonymous_at_discussions.microsoft.com
Date: 10/20/04 

Date: Wed, 20 Oct 2004 07:56:55 -0700

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.

>-----Original Message-----
>"Brian" <anonymous@discussions.microsoft.com> wrote in
message
>news:139301c4b5ef$ae0a2c50$a501280a@phx.gbl...
>
>>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.htm
l
>
>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/U
serFriendly.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.
>
>
>--
>Albert D. Kallal (Access MVP)
>Edmonton, Alberta Canada
>pleaseNOOSpamKallal@msn.com
>http://www.attcanada.net/~kallal.msn
>
>
>
>.
>

Loading