When should you upgrade access 97/vba applications?

[Terry Bell]

We've had a very large A97 app running fine for the last seven years.
I've just converted to SQL Server backend, which is being tested, but
meanwhile the JET based version, running under terminal server, has
suddenly started running very slowly. The network engineer has thrown
up his hands and said "It's Access 97". I've checked out lots of
things including the Oplocks setting and other stuff from this NG, and
I think I've done everything right. Since the App performs fine
outside of TS I challenged the network guy on this, asking him where
he got the idea it was A97. He emailed the boss with this paper from
someone's Blog.
What I'd like is some comments from the group on this paper, and some
suggestions as to when it would be wise to upgrade to A2003 or later,
or whether the Access application could/should be "ported" to VB.NET.
I stress this is a very large application which runs the whole
business and a functional rewrite would probably be very expensive.
TIA
TERRY BELL

Start Blog When should you upgrade Access97/VBA Applications?
Posted 11/10/2004 | by Lisa Cesnick | Comments (1) | TrackBacks
(0)

The company I'm working for has a multitude of Access97/VBA
applications that are used for everything from quality control and
reporting to shipping and inventory.

I pointed out to my client that some of the computers on the plant
floor are still running Windows 98 and should be upgraded. We then
began a dialogue on when the company should begin looking away from
Access 97/VBA applications.

This entry details the advice I offered my client.

Why Upgrading Is Necessary
"There were two options to consider when designing Visual Basic .NET
-- retrofit the existing code base to run on top of the .NET
Framework, or build from the ground up, taking full advantage of the
..NET Framework. To deliver the features most requested by customers
(for example, inheritance, threading), to provide full and
unconstrained access to the .NET Framework, and to ensure that Visual
Basic moves forward into the next generation of Web applications, the
right decision was to build from the ground up."
The text above is taken directly from Microsoft Developers Network at
the link http://msdn2.microsoft.com/library/t6b8wa5e.aspx.

Further, if you look the article "Help for Visual Basic 6.0 users" on
MSDN at the link http://msdn2.microsoft.com/library/kehz1dz1.aspx,
there are over 20 articles outlining the changes between the old VB
architecture and VB.net.

VB.NET is essentially a new language built on a new architecture with
a completely new paradigm. VB.NET is an object oriented language. VBA
and VB are primarily structured programming languages.

Current Access 97 applications are based on Visual Basic 5.0, and
therefore unsupported by Microsoft. So, there will come a time in the
near future when these applications will no longer run on Microsoft
Operating Systems.

Microsoft has made their intent clear, VBA will be replaced by the
..NET framework languages even in the Office Suite of applications
beginning with Office 2003. Microsoft has already discontinued support
for Access 97 and Visual Basic 6.0.

So, the question is not if Access 97 application should be ported to
VB.NET (or C#) but when should the applications be ported to VB.NET.
Access 2003 already uses the VB.NET language as the code behind Access
2003. Access 2003 will currently run VBA code just as the first
version of VB.NET ran VB 6.0 code. The latest version of VB.NET will
run "some" VB6.0 code, but not all. This is the avenue VBA will take.

If a company intends to stay with supported Microsoft Operating
Systems they will be required to move to .net and in some cases, SQL
Server depending on the application.

Noted Changes between VB.NET and VBA:
• VB.NET doesn't support vba default properties.
• VB.NET uses zero based arrays, vba uses 1 based arrays.
• VB.NET doesn't support using the Set keyword for variable
assignment.
• VBA UserForms cannot be copied or imported into Visual Studio .NET.
In most cases, you will need to recreate your forms as Windows Forms.
• Data binding to a Data Access Object (DAO) or Remote Data Object
(RDO) data source is not supported in Microsoft Visual Basic .NET.

• vba constants are different than vb.net constants (a null string in
vba is represented by the vbNullString constant. In vb.net it is
represented by Nothing) There are about a dozen of these kinds of
constants.End Blog

[Rick Brandt]

Terry Bell wrote:

[snip]
Current Access 97 applications are based on Visual Basic 5.0, and
therefore unsupported by Microsoft. So, there will come a time in the
near future when these applications will no longer run on Microsoft
Operating Systems.
[snip]

A does not follow from B. "Unsupported" does not mean "will no longer run".

Could this happen *eventually*? Perhaps. Certainly not "in the near future".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Justin Hoffman]

"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92**************************@posting.google.c om...

We've had a very large A97 app running fine for the last seven years.
I've just converted to SQL Server backend, which is being tested, but
meanwhile the JET based version, running under terminal server, has
suddenly started running very slowly. The network engineer has thrown
up his hands and said "It's Access 97". I've checked out lots of
things including the Oplocks setting and other stuff from this NG, and
I think I've done everything right. Since the App performs fine
outside of TS I challenged the network guy on this, asking him where
he got the idea it was A97. He emailed the boss with this paper from
someone's Blog.
What I'd like is some comments from the group on this paper, and some
suggestions as to when it would be wise to upgrade to A2003 or later,
or whether the Access application could/should be "ported" to VB.NET.
I stress this is a very large application which runs the whole
business and a functional rewrite would probably be very expensive.
TIA
TERRY BELL

Start Blog When should you upgrade Access97/VBA Applications?
Posted 11/10/2004 | by Lisa Cesnick | Comments (1) | TrackBacks
(0)

The company I'm working for has a multitude of Access97/VBA
applications that are used for everything from quality control and
reporting to shipping and inventory.

I pointed out to my client that some of the computers on the plant
floor are still running Windows 98 and should be upgraded. We then
began a dialogue on when the company should begin looking away from
Access 97/VBA applications.

This entry details the advice I offered my client.

Why Upgrading Is Necessary
"There were two options to consider when designing Visual Basic .NET
-- retrofit the existing code base to run on top of the .NET
Framework, or build from the ground up, taking full advantage of the
.NET Framework. To deliver the features most requested by customers
(for example, inheritance, threading), to provide full and
unconstrained access to the .NET Framework, and to ensure that Visual
Basic moves forward into the next generation of Web applications, the
right decision was to build from the ground up."
The text above is taken directly from Microsoft Developers Network at
the link http://msdn2.microsoft.com/library/t6b8wa5e.aspx.

Further, if you look the article "Help for Visual Basic 6.0 users" on
MSDN at the link http://msdn2.microsoft.com/library/kehz1dz1.aspx,
there are over 20 articles outlining the changes between the old VB
architecture and VB.net.

VB.NET is essentially a new language built on a new architecture with
a completely new paradigm. VB.NET is an object oriented language. VBA
and VB are primarily structured programming languages.

Current Access 97 applications are based on Visual Basic 5.0, and
therefore unsupported by Microsoft. So, there will come a time in the
near future when these applications will no longer run on Microsoft
Operating Systems.

Microsoft has made their intent clear, VBA will be replaced by the
.NET framework languages even in the Office Suite of applications
beginning with Office 2003. Microsoft has already discontinued support
for Access 97 and Visual Basic 6.0.

So, the question is not if Access 97 application should be ported to
VB.NET (or C#) but when should the applications be ported to VB.NET.
Access 2003 already uses the VB.NET language as the code behind Access
2003. Access 2003 will currently run VBA code just as the first
version of VB.NET ran VB 6.0 code. The latest version of VB.NET will
run "some" VB6.0 code, but not all. This is the avenue VBA will take.

If a company intends to stay with supported Microsoft Operating
Systems they will be required to move to .net and in some cases, SQL
Server depending on the application.

Noted Changes between VB.NET and VBA:
. VB.NET doesn't support vba default properties.
. VB.NET uses zero based arrays, vba uses 1 based arrays.
. VB.NET doesn't support using the Set keyword for variable
assignment.
. VBA UserForms cannot be copied or imported into Visual Studio .NET.
In most cases, you will need to recreate your forms as Windows Forms.
. Data binding to a Data Access Object (DAO) or Remote Data Object
(RDO) data source is not supported in Microsoft Visual Basic .NET.

. vba constants are different than vb.net constants (a null string in
vba is represented by the vbNullString constant. In vb.net it is
represented by Nothing) There are about a dozen of these kinds of
constants.End Blog

Terry
That guy sounds like an idiot. "...a new language built on a new
architecture with a completely new paradigm...", "To deliver the features
most requested by customers (for example, inheritance, threading)..."
For Christ's sake, most customers require that their database functions
properly, some require it to be quick, but no customer has ever come to me
with requirements that it be built on a new architecture with a completely
new paradigm!
Sure, moving it to .net will make the coding language more modern, but what
concrete benefits will that bring the users and how much will it cost? The
immediate problem sounds like users are unhappy with the speed and I guess
this can be fixed without any re-coding. After all it used to work. I
guess he doesn't know where to start looking.

[Albert D. Kallal]

Gee, you should at least try and break up the number of questions,a nd
issues into separate posts here.

The first thing I would look at is the performance issue of:

, but
meanwhile the JET based version, running under terminal server, has
suddenly started running very slowly.
Gee, "suddenly" started running slowly. This implies that the terminal
server application at one time was running fast? So, you are telling me that
NOTHING was changed on the server? No upgrade of hardware occurred? No
upgrade of OS occurred? Nothing changed...but now the terminal services
application 'STARTED" running slowly? I would expand a bit on the above. I
suspect that perhaps the OS was changed, or something was changed
here. Are you absolute sure that the terminal services users had good
performance, and then all of a sudden the remote desktop users now have a
performance problem? (and NOTHING was changed?...hum, that simply does not
make sense).

I would perhaps try using a persistent connection from the FE (front end)
database, to the back end database. (and, I assume you are competent here,
and of course always give each user a separate copy (preferably a mde). This
rule of a separate copy also applies when using TS. So, each user login gets
their own space,and THEIR OWN COPY of the FE. further, you should try the
persist connection trick, as it usually fixes most split database
problems. This split "delay" problem generally occurs on newer OS (such as
window xp, and windows 2000 server).
The network engineer has thrown
up his hands and said "It's Access 97". I've checked out lots of
things including the Oplocks setting and other stuff from this NG, and
I think I've done everything right. Since the App performs fine
outside of TS I challenged the network guy on this, asking him where
he got the idea it was A97.
In fact, while users are working on the application, walk up to the server,
logon as a user, and now try the application. In this example, there is NO
terminal services running here! And, I will bet a million dollars, the
application will STILL RUN slow. (the reason for this is that terminal
services is
simply a remote desktop connection to the server!!...the fact of TS, or the
remote desktop connection is going to have ZERO influence on the performance
issue here!). So, walk to the server room, long on, and run the application.
(make sure other users are working, and USING the application (via TS)
when you do this
test). Like I say, you can guarantee that the application will run the same
(ie: slow for you).
I challenged the network guy on this, asking him where
he got the idea it was A97.
Well, he is a network guy, and can't possibility know what ANY COMPETENT
ms-access developer will know!! That 'BASIC' competency I am talking about
is
that you must give each user their OWN copy of the front end, and further,
99% of the time, making sure your application keeps a persistent connection
will fix the performance problems (You can Google on this issue..but it kind
of like stating that humans breath air!). An excellent reference to check
for
performance, and one that outlines the persistent connection trick can be
found here:

http://www.granite.ab.ca/access/performancefaq.htm

Check the above, and also of course make sure you installed the updates and
bug fixes to office97 (that is sr1, sr2b, and VERY important to install the
jet update of jet35sp3.exe. (and, once again: just because you are suing TS,
you STILL MUST GIVE each front end user a SEPARATE copy of the FE,a nd
PREFERABLY this should be a mde).

So, there will come a time in the
near future when these applications will no longer run on Microsoft
Operating Systems.
Oh really? Ask your self the following question:

are people stupid, or did Microsoft get successful because they are
smart?

It is very important to note here that Apple Computer on several occasions
has forced developers to throw out their code.

Microsoft on the other hand says we are going to work VERY hard to keep your
old code running. What this means is that overtime a competitor to Microsoft
forces developers and business to THROW OUT their software when a new OS
comes also, those customers as a general rule migrated OUT of that platform,
and into Microsoft hands. In other words, one of Microsoft secrete weapons
for their success has been that they PRESERVE your software investment.

There is nothing stopping you from writing code with ms-access version 2.0
(what 1994??). In fact we still frequently get questions here about that
version. There is nothing stopping you from writing software using FoxPro
2.0. That is also a dos based "text" system, and is also about 1992.

Microsoft has the best track record in the industry in this regards. You can
still run all that old software. You can even run most of it in windows2000,
which really does not even has dos anymore.

Apple, and good many of the other vendors out there have forced numerous
upgrades upon their users. For example, all of the old applications for the
old Apple Macintosh (such as Mac-paint etc). DO NOT work on the new
platforms. In other words, just about all of the 1980' applications (and
early 90's code) for the Apple Mac do not work anymore. They WERE FORCED to
upgrade. ALL OF MY "dos" from the early 1980's still runs fine on the
newest windows box. Microsoft does not have a policy of "breaking", or not
allowing the old code to run. They have the best track record in the
industry by far in this regard.

So, really, I don't see any problem here. Nothing is stopping you from using
the 1992 version of dos based Reflex is there? (I have some clients still
running that)

If you want, you can jump over to Dan Bricklins site, and download the
ORIGINAL spread sheet for the ibm pc. It still works today! Do any of you
remember VisiCalc? It is only a 27k download for a whole spread sheet!.
Simply amazing. By the way, this spreadsheet is from the original 1981
VisiCalc disk, and it still runs on Windows today! (heck, the average GIF on
Just how old of code are trying to run here? We can't go back before 1981,
since the Pc did not exist. Hence, I can't help you with code before that.
On the other hand, there are good number of Atari, and Apple II sites with
great emulators that let you that lots of code from the 1970's.

So, if you want to run some software that is MORE THEN 20 years old, then
Go to dan's site, and download the ORIGINAL spreadsheet for the pc!!
Here is the link:
http://www.bricklin.com/visicalc.htm

By the way, that spreadsheet still runs on a brand new windows xp box.

Anyone who does not realize the INCREASABLE track record of Microsoft, and
the
compatible record simply has not a clue as to why (one reason) MS got so
successful.....and I can tell you it is not because people are stupid!!

But, if you want to run access 1.0, or Visual Basic 3.0...you can...
Access 2003 already uses the VB.NET language as the code behind Access
2003.
This is 100% DEAD WRONG. Ms-access continues to use VBA, and will
so for the future.
Access 2003 will currently run VBA code just as the first
version of VB.NET ran VB 6.0 code.
Utter rubbish. VB.net does NOT run VB 6.0 code at all!!
The latest version of VB.NET will
run "some" VB6.0 code, but not all. This is the avenue VBA will take.

Sorry, just not the case. Ask your self if Microsoft is willing to torture,
and have 400+ million uses of Excel through out all their spreadsheets, and
start over? (remember, macros in Excel is VBA!!!). Anyone with a function
brain will realize that VBA support will continue for a very long time
indeed. If MS throws out Excel, and starts over, then you will have a
situation JUST like apple computer, where each time Apple did this, they
lost customers. I really am very sorry, but Microsoft is in fact a VERY well
run company..and is far more intelligent that the garbage that poster
is spooning out. Once can argue that some of the posters info is in fact
Ms marketing stuff..but the fact remains that MS cares about preserving your
software, and their track record BEATS the competitors by a COUNTRY MILE in
terms of their systems sporting 5, 10 or even 20 year old software!!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Terry Bell]

Albert thanks for wading through all this.

"Albert D. Kallal" <ka****@msn.com> wrote in message news:<vqc_d.708057$6l.440252@pd7tw2no>...

Gee, you should at least try and break up the number of questions,a nd
issues into separate posts here.
Yep. Will do. Sorry.
The first thing I would look at is the performance issue of:

meanwhile the JET based version, running under terminal server, has
suddenly started running very slowly. Gee, "suddenly" started running slowly. ... So, you are telling me that
NOTHING was changed on the server? .....

The network has had lots of maintenance on it, new servers and system
software upgraded, around the time the slowness was noticed. I can't
obtain from them anything more substantial than that. There is no
doubt that the environment changed.
I would perhaps try using a persistent connection from the FE (front end)
database, to the back end database. (and, I assume you are competent here,

Yes. There is a "de facto" persistent connection by way of a "home
form" which is based on a recordset of Orders. However occasionally a
user will close this screen to run another function. I'll open a ...
global defined rs in autoexec and leave it open for the whole session,
right?
and of course always give each user a separate copy (preferably a mde).
Yes. That is being done, and in TS too. I have an auto front end
updater similar to one I saw somewhere.

The network engineer has thrown
up his hands and said "It's Access 97". I've checked out lots of
things including the Oplocks setting and other stuff from this NG, and
I think I've done everything right. Since the App performs fine
outside of TS I challenged the network guy on this, asking him where
he got the idea it was A97.

In fact, while users are working on the application, walk up to the server,
logon as a user, and now try the application. In this example, there is NO
terminal services running here! And, I will bet a million dollars, the
application will STILL RUN slow. (the reason for this is that terminal
services is
simply a remote desktop connection to the server!!...the fact of TS, or the
remote desktop connection is going to have ZERO influence on the performance
issue here!). So, walk to the server room, long on, and run the application.
(make sure other users are working, and USING the application (via TS)
when you do this
test). Like I say, you can guarantee that the application will run the same
(ie: slow for you).

Thanks, will try this.

I challenged the network guy on this, asking him where
he got the idea it was A97.

Well, he is a network guy, and can't possibility know what ANY COMPETENT
ms-access developer will know!!

I don't mind him being incompetent when it comes to ms-access, it's
advising the client to get rid of Access 97 and rewriting everything
in .Net that pressed my button. And then trying to justify his
arguments by quoting someone's blog.
That 'BASIC' competency I am talking about is
that you must give each user their OWN copy of the front end, and further,
99% of the time, making sure your application keeps a persistent connection
will fix the performance problems (You can Google on this issue..but it kind
of like stating that humans breath air!). An excellent reference to check
for
performance, and one that outlines the persistent connection trick can be
found here:

http://www.granite.ab.ca/access/performancefaq.htm
Check the above, and also of course make sure you installed the updates and
bug fixes to office97 (that is sr1, sr2b, and VERY important to install the
jet update of jet35sp3.exe.
SR2 is OK I believe, and I'll check TS for Jet
PREFERABLY this should be a mde).
Haven't seen that before ... Yes I do know mde's ... will try after
other options. (I run /decompile occasionally, but that's not the
same?)

So, there will come a time in the
near future when these applications will no longer run on Microsoft
Operating Systems.

Oh really? Ask your self the following question:

are people stupid, or did Microsoft get successful because they are
smart?

It is very important to note here that Apple Computer on several occasions
has forced developers to throw out their code.

Microsoft on the other hand says we are going to work VERY hard to keep your
old code running. What this means is that overtime a competitor to Microsoft
forces developers and business to THROW OUT their software when a new OS
comes also, those customers as a general rule migrated OUT of that platform,
and into Microsoft hands. In other words, one of Microsoft secrete weapons
for their success has been that they PRESERVE your software investment.

There is nothing stopping you from writing code with ms-access version 2.0
(what 1994??). In fact we still frequently get questions here about that
version. There is nothing stopping you from writing software using FoxPro
2.0. That is also a dos based "text" system, and is also about 1992.

Microsoft has the best track record in the industry in this regards. You can
still run all that old software. You can even run most of it in windows2000,
which really does not even has dos anymore.

Apple, and good many of the other vendors out there have forced numerous
upgrades upon their users. For example, all of the old applications for the
old Apple Macintosh (such as Mac-paint etc). DO NOT work on the new
platforms. In other words, just about all of the 1980' applications (and
early 90's code) for the Apple Mac do not work anymore. They WERE FORCED to
upgrade. ALL OF MY "dos" from the early 1980's still runs fine on the
newest windows box. Microsoft does not have a policy of "breaking", or not
allowing the old code to run. They have the best track record in the
industry by far in this regard.

So, really, I don't see any problem here. Nothing is stopping you from using
the 1992 version of dos based Reflex is there? (I have some clients still
running that)

If you want, you can jump over to Dan Bricklins site, and download the
ORIGINAL spread sheet for the ibm pc. It still works today! Do any of you
remember VisiCalc? It is only a 27k download for a whole spread sheet!.
Simply amazing. By the way, this spreadsheet is from the original 1981
VisiCalc disk, and it still runs on Windows today! (heck, the average GIF on
Just how old of code are trying to run here? We can't go back before 1981,
since the Pc did not exist. Hence, I can't help you with code before that.
On the other hand, there are good number of Atari, and Apple II sites with
great emulators that let you that lots of code from the 1970's.

So, if you want to run some software that is MORE THEN 20 years old, then
Go to dan's site, and download the ORIGINAL spreadsheet for the pc!!
Here is the link:
http://www.bricklin.com/visicalc.htm

By the way, that spreadsheet still runs on a brand new windows xp box.

Anyone who does not realize the INCREASABLE track record of Microsoft, and
the
compatible record simply has not a clue as to why (one reason) MS got so
successful.....and I can tell you it is not because people are stupid!!

But, if you want to run access 1.0, or Visual Basic 3.0...you can...

Access 2003 already uses the VB.NET language as the code behind Access
2003.

This is 100% DEAD WRONG. Ms-access continues to use VBA, and will
so for the future.

Access 2003 will currently run VBA code just as the first
version of VB.NET ran VB 6.0 code.

Utter rubbish. VB.net does NOT run VB 6.0 code at all!!

The latest version of VB.NET will
run "some" VB6.0 code, but not all. This is the avenue VBA will take.

Sorry, just not the case. Ask your self if Microsoft is willing to torture,
and have 400+ million uses of Excel through out all their spreadsheets, and
start over? (remember, macros in Excel is VBA!!!). Anyone with a function
brain will realize that VBA support will continue for a very long time
indeed. If MS throws out Excel, and starts over, then you will have a
situation JUST like apple computer, where each time Apple did this, they
lost customers. I really am very sorry, but Microsoft is in fact a VERY well
run company..and is far more intelligent that the garbage that poster
is spooning out. Once can argue that some of the posters info is in fact
Ms marketing stuff..but the fact remains that MS cares about preserving your
software, and their track record BEATS the competitors by a COUNTRY MILE in
terms of their systems sporting 5, 10 or even 20 year old software!!

OK Albert that was exactly what I was looking for - I'll show that to
the client.

Some other things:

I did try one other thing - refreshing links to the backend on
startup. I used to just leave the links as is when the new front ends
were copied down. Anyway didn't make any difference.

The Oplocks setting is switched OFF.

There was one machine (not connected through TS) that we found was
corrupting the back end (incidentally by following advice from this
group about inspecting the ldb file). Got the engineer to take it
away (faulty NIC, cable, whatever) and that's OK now, but the slowness
remained.

I also set up this application on my piddly littlehome network, with
three computers - P4 2.4 W2K, P3 666 W2K, P3 450 laptop Win 98. Set
the backend up on the P4 2.4, connected all 3 to the backend and ran
one of the main functions that was taking forever at the site, at the
same time committing two Orders on the other two machines. It ran
flawlessly and quickly, which makes me think it's the environment at
the site.

There's other stuff that might prompt further comments from you Albert
but I guess the main thing I was looking for was the critique of the
blog.

Thanks very much when I get to the bottom of this I'll let you know.

Terry Bell

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:vqc_d.708057$6l.440252@pd7tw2no:

Nothing is stopping you from using
the 1992 version of dos based Reflex is there? (I have some
clients still running that)

Actually, because of its non-standard graphics usage, it might not
run on modern graphics cards/drivers.

It was a great program, though -- my first db of all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Albert D. Kallal]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Albert D. Kallal" <ka****@msn.com> wrote in
news:vqc_d.708057$6l.440252@pd7tw2no:

Nothing is stopping you from using
the 1992 version of dos based Reflex is there? (I have some
clients still running that)

Actually, because of its non-standard graphics usage, it might not
run on modern graphics cards/drivers.

yes, I have encountered that problem! There was a startup switch (/g, /t,
or something..can't remember what the /switch parameter was, but I do
remember this allowed it to run on win95 + winxp boxes in a dos window).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal