MS Access database to gradually become a server based database - which one ?

[AriRio]

Hello

I am a person with some MS Access experience but new in server based
applications.

A) I have a large MS Access 2003 database. It consists of front-end and back-
end. It was used by about 15 people on LAN. After two years it became quite
huge - plenty of queries (about 150), forms (about 100), reports (about 60)
and huge VBA code in FE and large amount of data in BE (about 80 tables).

B) I would like to convert it gradually from the filebased system to server
based system. For the gradual conversion I got an idea to make a middle step.
It would be a conversion of the BE only into some server based system (free
or very cheap) like MySQL or MS SQL Server 2005 Express Edition. Then connect
the present FE with new server based BE.

C) I have tried an example (about 20 tables from the current database) -
conversion to MySQL. It was relatively easy. I put it on the server and
linked it via ODBC. It started work. But then problems raised - connection
speed firstly.

I am looking for the solution of which way I shall focus to continue. Going
straight into MS SQL Server or Oracle is very expensive way for me now. Also
I have not any experience with it, it would last ages to develop the database
again into the same level probably and I need to keep it working and
developing. So therefore I think about gradual conversion and about a middle
step - conversion and putting the BE only on the server as a server based
system. Then set the connection between MS Access FE and new BE. I am ready
to make changes in the FE and new BE - from reading about this problem on
internet forums I have realized these will be mainly - transferring queries
on a server side to minimaze data flow through net, place procedures and
functions on the server side, etc.

The questions are:
1) Is that a good idea to make a middle step (only BE conversion and FE
update) or completaly bad ?
2) Once only the BE converted into server based system - is it at least
somehow practical / possible to keep the database user friendly and securily
running + still easy for further updating and developing ?
3) What system (middle step) would you recommend me to convert the BE into ?
Would it be MySQL, MS SQL Server 2005 Express Edition or any other ?
4) What system would you suggested to be the final step of conversion (both
FE and BE converted) - Oracle or MS SQL Server or any other ?

I would welcome some guidance which way to focus.
Thanks very much if you have any to share.
Ari

 

[Clifford Bass]

Hi Ari,

A few thoughts. Others are more than welcome to disagree.

This may be a case of you get what you pay for. Free or little cost
may not get you very far. I am not totally sure, but to do SQL Server with
15 people you probably will need the Workgroup Edition. Likewise, with
Oracle, Sybase, SQLBase by Unify and others you probably will need whatever
version they sell appropriate to that number of people.

Definitely converting all of the data to a back end would be a good
first step. I would probably want to do it all at once because Access does
send queries in their entirety to the back end, where possible, so it does
cut down on the network traffic. And the back end can execute the queries
efficiently. A number of the companies provide trial versions for testing.
No matter which system you go with there will be adjustments you will need to
make in order to get things to work. SQL Server may provide the smoothest
transition. You should be able to keep Access as a front end, at least until
something else is created. But, again, it would be good to test that,
preferrably with a number of concurrent users to see how it behaves.

One thing to consider currently is if you can break up the current
front end into several discreet parts. Are there groups of users who only
use certain forms and reports that others do not and that do not use the ones
that the others use? If there are a few forms that every one uses, you could
include copies in each front end. Not ideal because of the dual maintanence
when those change, but it may help get a handle on things. Another thing to
consider regardless of what you do is that Access's forms often load entire
recordsets. This is a heavy load on the back end and the network that is
often not necessary because people often only need to look at current or
recent information--not stuff from five years ago. So it may make things
better with your forms to implement some kind of filtering and/or
single/recent-only-record retrieval.

One issue with Access VBA is that it uses what is called interpreted
code. This by its nature is pretty slow. So ultimately it probably would be
good to migrate to something that can create a compiled .exe program. Yes, I
know you can "compile" the VBA in Access. But it is not producing code that
can be run directly without Access; it still requires Access's interpreter.
Visual Basic .NET may be a good choice for that since you already know VBA.
If you make the shift away from Access for the front end, you could do it in
stages also. Maybe start with the commonly used forms and processes and
those reports that require the presence of those forms. Then the lesser used
forms and processes. And then finally any other reports you want to run from
the new system. We use Access for most of our reports, with only a few
running out of the non-Access programs.

My $2.00 (inflation you know) worth; hope it helps,

Clifford Bass

 

[John W. Vinson]

Hello

I am a person with some MS Access experience but new in server based
applications.

A) I have a large MS Access 2003 database. It consists of front-end and back-
end. It was used by about 15 people on LAN. After two years it became quite
huge - plenty of queries (about 150), forms (about 100), reports (about 60)
and huge VBA code in FE and large amount of data in BE (about 80 tables).

How big is the backend (compacted) in MBytes? Are you pushing the 2GByte
limit? Are you having performance issues, security issues? What's motivating
the conversion? It's probably a good idea but you should know what you're
getting into... and why!

Check into http://www.granite.ab.ca/access/sqlserverupsizing.htm for a
thorough discussion.

B) I would like to convert it gradually from the filebased system to server
based system. For the gradual conversion I got an idea to make a middle step.
It would be a conversion of the BE only into some server based system (free
or very cheap) like MySQL or MS SQL Server 2005 Express Edition. Then connect
the present FE with new server based BE.

Either will work but SQL/Server Express at least has the advantage that it's a
Microsoft product, and Access (2003 and even more so 2007) is designed with it
in mind.

C) I have tried an example (about 20 tables from the current database) -
conversion to MySQL. It was relatively easy. I put it on the server and
linked it via ODBC. It started work. But then problems raised - connection
speed firstly.

Yep. I've been underwhelmed by MySQL's performance. As Tony's website says,
you may not gain much in terms of performance from SQL either - its advantages
are more in the area of security, 24/7 uptime, hot backups, data logging and
so on - all of which are great things to have but which also require more
administrative work.

I am looking for the solution of which way I shall focus to continue. Going
straight into MS SQL Server or Oracle is very expensive way for me now. Also
I have not any experience with it, it would last ages to develop the database
again into the same level probably and I need to keep it working and
developing. So therefore I think about gradual conversion and about a middle
step - conversion and putting the BE only on the server as a server based
system. Then set the connection between MS Access FE and new BE. I am ready
to make changes in the FE and new BE - from reading about this problem on
internet forums I have realized these will be mainly - transferring queries
on a server side to minimaze data flow through net, place procedures and
functions on the server side, etc.

Your intermediate step may end up being... your end result! Access makes an
excellent frontend to a well-designed SQL database. You'll want to review your
forms and queries to be sure that they're well designed for server-based
design; if you have forms based directly on tables, or on many-record queries,
consider restructuring them to be abased on single- or few-record queries.

The questions are:
1) Is that a good idea to make a middle step (only BE conversion and FE
update) or completaly bad ?

A step in a good direction if you're aware of the implications and the extra
work.

2) Once only the BE converted into server based system - is it at least
somehow practical / possible to keep the database user friendly and securily
running + still easy for further updating and developing ?

For a long time to come you'll still be using the same frontend. If it's user
hostile now it won't be any better... but surely you're not in THAT trap!

3) What system (middle step) would you recommend me to convert the BE into ?
Would it be MySQL, MS SQL Server 2005 Express Edition or any other ?

Either, with a bit of a preference for SQL Server. Others may disagree.

4) What system would you suggested to be the final step of conversion (both
FE and BE converted) - Oracle or MS SQL Server or any other ?

I wouldn't change the frontend at all unless there were a VERY good reason to
do so. You could duplicate the functionality (most of it) of Access using
Visual Studio but it would be a HUGE and expensive effort; very little of the
work you've put into Access could be salvaged... and there would be (IMHO)
little benefit to doing so. If you were starting from scratch with an
enterprise level solution and an ample budget, you might want to bypass Access
and go right to SQL/Visual Studio, but even there I'd suggest that Access
would allow much faster and less expensive development.

I would welcome some guidance which way to focus.
Thanks very much if you have any to share.

Read Tony's thoughts in the website cited above, and perhaps check out the
resources at http://www.mvps.org/access as well.

 

[Tony Toews [MVP]]

This may be a case of you get what you pay for. Free or little cost
may not get you very far. I am not totally sure, but to do SQL Server with
15 people you probably will need the Workgroup Edition.

Even with the SQL Server 2005 Express edition with throttling there
were reliable reports of 50 or 75 users using the same backend. My
understanding is that throttling no longer exists in SQL Server 2008.

One thing to consider currently is if you can break up the current
front end into several discreet parts. Are there groups of users who only
use certain forms and reports that others do not and that do not use the ones
that the others use? If there are a few forms that every one uses, you could
include copies in each front end. Not ideal because of the dual maintanence
when those change, but it may help get a handle on things.

Why? This would be a significant PITA and I can't think of any good
reason why.

One issue with Access VBA is that it uses what is called interpreted
code. This by its nature is pretty slow. So ultimately it probably would be
good to migrate to something that can create a compiled .exe program. Yes, I
know you can "compile" the VBA in Access. But it is not producing code that
can be run directly without Access; it still requires Access's interpreter.
Visual Basic .NET may be a good choice for that since you already know VBA.
If you make the shift away from Access for the front end, you could do it in
stages also. Maybe start with the commonly used forms and processes and
those reports that require the presence of those forms. Then the lesser used
forms and processes. And then finally any other reports you want to run from
the new system. We use Access for most of our reports, with only a few
running out of the non-Access programs.

With all due respect this is wrong. Or rather the speed difference
between VBA and .Net is minuscule for 99.9% of database operations.
It's the speed of the network IO that makes by far the biggest
difference.

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/

 

[Clifford Bass]

Hi Tony,

Please see responses inline.

Clifford Bass

Even with the SQL Server 2005 Express edition with throttling there
were reliable reports of 50 or 75 users using the same backend. My
understanding is that throttling no longer exists in SQL Server 2008.

Useful to know. My experience and knowledge of SQL Server is pretty
limited.

Why? This would be a significant PITA and I can't think of any good
reason why.

Because he was saying that his front end was becoming "quite huge". If,
and I do not know if it is true for his situtation or not; so if it is
causing front-end performance issues, or other issues of some sort, then it
does make sense. My opinion: There is no point in giving everyone everything
all the time if they do not use it. My experience: When people's tasks do
not overlap, there is no significant difference to me as the developer in
separating out each group's things into separate systems. In fact, it is
desireable. Who wants to see twenty menus of stuff if you only use three?

With all due respect this is wrong. Or rather the speed difference
between VBA and .Net is minuscule for 99.9% of database operations.
It's the speed of the network IO that makes by far the biggest
difference.

That may depend on several factors such as whether or not you use
linked tables when doing those tasks or a more direct connection, such as you
might use in VB .NET. Correct me if I am wrong, but Access only lets you
link tables using ODBC when the back end is something like SQL Server. A
quick test to read 3,342 rows from a table in a back end (not SQL Server)
gives me this:

Using Seconds
Linked tables in Access 5
OLEDB provider in Access 3
OLEDB provider in VB .NET 3
Native .NET provider in VB .NET 2

The timings are from the second of two runs in each so as to minimize
the differences that could be caused by a first run vs. succeeding runs.
Typically I would not use an OLEDB provider in Access because that creates
either the need to ask for the credentials each time or the need to store
them somewhere, which is a potential security hole, even if it is only in
memory.

Then there is the issue of quite what his "huge VBA code" is doing. Is
it mostly database access or does it include a lot of other heavy
non-database processing? Ultimately it is a "your mileage may vary" kind of
thing and only by testing can you really determine which is better.

Clifford Bass

 

[Tony Toews [MVP]]

Because he was saying that his front end was becoming "quite huge".

Looking back. 150 queries, 100 forms, 60 reports is not huge. I
was working on a system with 1200 queries, 450 forms and 350 reports
in A97. That I could qualify as huge.

My opinion: There is no point in giving everyone everything
all the time if they do not use it. My experience: When people's tasks do
not overlap, there is no significant difference to me as the developer in
separating out each group's things into separate systems. In fact, it is
desireable. Who wants to see twenty menus of stuff if you only use three?

So what we did was turn off menu items if your job description didn't
warrant it. But there were still super users who needed to see
everything if they had fix things.

That may depend on several factors such as whether or not you use
linked tables when doing those tasks or a more direct connection, such as you
might use in VB .NET. Correct me if I am wrong, but Access only lets you
link tables using ODBC when the back end is something like SQL Server. A
quick test to read 3,342 rows from a table in a back end (not SQL Server)
gives me this:

Using Seconds
Linked tables in Access 5
OLEDB provider in Access 3
OLEDB provider in VB .NET 3
Native .NET provider in VB .NET 2

Right. You've illustrated the 0.01% situation. Running through a
recordset in code doesn't happen that often. By far the vast
majority of CPU time in Access is going to be loading forms,
validating data and printing reports. And the perceptible
difference between 10 milliseconds and 6 milliseconds is negligible.

Then there is the issue of quite what his "huge VBA code" is doing. Is
it mostly database access or does it include a lot of other heavy
non-database processing? Ultimately it is a "your mileage may vary" kind of
thing and only by testing can you really determine which is better.

Access is a database app. So unless he is doing something like that
game Frogger I don't see what the point of that is. Seriously. a guy
I know demo'd the Frogger game he'd done is MS access moving squares
around the form.

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/

 

[Clifford Bass]

Hi Tony,

Then I guess our situation is unusual because both of those things are
frequently done here. Excluding frogger

Clifford Bass

 

[AriRio via AccessMonster.com]

Hi,
thanks for comments. I put the question on more forums. After getting a
feedback from internet discussions I am close to decision to transfer the BE
into the MS SQL Server 2008 Express Edition and keep MS Access 2003 as FE.
But still not sure.

I have still just a general awareness about future problems & work:
- transferring queries and VBA code (procedures) on a server side (if
possible) - ?
- pass-trough queries - ?
- forms based either on small linked tables or views from the server; or use
unbound forms - ?
- ADO connection to be established rather than ODBC - ?
- some stability and security issues (do not know specifically which) - ?

and main question is:
Does MS SQL Server 2008 Express Edition allow users to
programme/run/update/maintain procedures written in VBA or C on the server
side ?

 

[AriRio via AccessMonster.com]

Hi,
the question into which platform to migrate the BE is solved. I chose MS SQL
Server 2008 Express Edition. I found that it allows the stored procedures,
triggers and views.
Some questions still remain - are the listed below all main general problems
which I am going to face ?
- transferring queries and VBA code (procedures) on a server side
- pass-trough queries
- forms based either on small linked tables or views from the server; or use
unbound forms
- ADO connection to be established rather than ODBC
- some stability and security issues (do not know specifically which)

 

[Albert D. Kallal]

Hi,
the question into which platform to migrate the BE is solved. I chose MS
SQL
Server 2008 Express Edition. I found that it allows the stored procedures,
triggers and views.

I think the above is a great choice, and as mentioned for anywhere up to 30
users, the thing should not even break into a sweat performance wise.

Some questions still remain - are the listed below all main general
problems
which I am going to face ?
- transferring queries and VBA code (procedures) on a server side

It does make sense to transfer some of the procedures and coding to the
server side. It's not clear if you have some heavy duty processing routines,
or routines that updates lots of data. For the most part you really don't
need to move a lot of your code to the server side anyway.

- pass-trough queries

The use a pass through queries is a good idea for some of the report's and
other things. Once again for the most part a lot of your quires will run
fine as is.

- forms based either on small linked tables or views from the server; or
use
unbound forms

I think if your tables are fairly small say only having a few million
records each, then bound forms are just fine. Unless you doing some special
processing and your forms work with tens of millions of records, then I see
little advantage if anything for going to unbound forms. on unbound form
does not increase performance at all.

Remember good performance is a result of good designs in your application.

- ADO connection to be established rather than ODBC

Again the above is a not an issue and will gain you almost nothing in terms
of performance for your overall application.

SQL server is indeed a high performance system, and also a system that can
scale to many many users.

If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.

In other words...sql server is rather nice, and is a standard system used in
the IT industry.

However, before you convert..how well does your application run now?

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

The other issue is how well is the database setup?

Further..how well are the forms designed?

How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):

http://www.members.shaw.ca/AlbertKallal/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Don't use quires that require more then one linked table

(this ONLY applies to odbc to sql server...you CAN and are FREE to do this
with a mdb JET file share..and also with ADP projects to sql server).

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc. (again..this does NOT apply to a mdb JET file
share).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right. Just don't open a form to a huge table without
some type of restriction. This likely the #1 reason why ms-access gets
such a bad rap because access developers don't follow this rule where
just about every other developer + platform (vb.net etc) follows. There
is NOT A DIFFERENCE in performance here, but ONLY what you drag
from the server to the form..that is your #1 enemy.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducing the bandwidth
(amount of data) goes hand in hand. So, better applications
treat the users well..and run faster! (this is good news!)