By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,483 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,483 IT Pros & Developers. It's quick & easy.

Converting database to MSDE

P: n/a
I am in the process of cleaning up a database that our company uses to
track jobs, time and expense, and customer information.

We are running Windows 2000 Server with approximately 20 terminals
(Each running 2000) logging in each day. Four of these terminals
access the server via Citrix.

Currently the database is about 60MB, but it grows to 150 and larger
each week. I am constantly having to compact it to keep it running
smoothly. Also at least once a day, the terminals accessing the
database via Citrix lock it into exclusive mode and they (the Citrix
users) have to exit and reenter in order for anyone else to get in.
This doesn't happen everytime they are in and I am not sure what the
trigger is to make this happen. I had thought that possibly it has to
do with the number of concurrent users, but I have no way of tracking
who is in at any given time. (or do I?)

At any rate, in my limited research to clean up this database, I ran
across some info suggesting MSDE in lieu of Jet. I am fairly well
versed in Access and have taught myself some sql stuff by looking at
the queries that are built through the wizards.

My question(s) (finally!) is:

- Is MSDE a better choice for what we are running? I have read the
info on file vs. client server, but admit I don't understand it.
- If we move to the MSDE, do I need to install the sql server to each
terminal?
- Is there going to be a noticable change as far as the users are
concerned? (I have some users that are moving kicking and screaming
into the digital age and any change is seen as traumatic)
- If I convert my database to the MSDE, am I going to be able to
continue to make changes to the forms, reports and tables without
learning a new program? Not afraid to learn a new program, but want
the transition to be fairly seamless for the users. I would want to
practice prior to the transition.
- Will I be able to make changes to the forms and reports while
others are accessing the data? (Currently, I find it necessary to do
my maintaining and changes after hours because I am unable to save my
work while anyone is in the database.)
- And finally, is MSDE going to end up being the default engine? Is
this change inevitable?

I appreciate all thoughts and advice, but remember, I am a novice and
don't understand computer speak. :)

Rosy Moss
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Rosy Moss" <RO******@COX.NET> wrote
At any rate, in my limited research to clean
up this database, I ran across some info
suggesting MSDE in lieu of Jet.
What, exactly, was it that this info indicated would make this a better
choice? You do know that the current incarnation of MSDE (aka SQL Server
Desktop Edition) deliberately adds delays after 5 concurrent "batch
processes" are running inside it, and that it has the same DB size limit,
2GB, as does a Jet DB. That is, they purposely, make it slower as you add
more users... And, it lacks the excellent administrative tools that full
SQL Server provides.

If, on the other hand, you have only twenty users each day, and they are not
all logged in at the same time, you may have no problem. In fact, if that is
the case, you probably shouldn't have any problem with a Jet database
either.

You might save yourself a good deal of difficulty if you visited MVP Tony
Toews' site, http://www.granite.ab.ca/accsmstr.htm and took a look at the
information and links he has on Access-Jet multiuser applications. If I were
a bettin' man, I'd wager you might just find some relatively easy things to
do that would give your users acceptable performance. And, for a general
overview, you can download my presentation on this subject from
http://appdevissues.tripod.com/downloads.htm.

- Is MSDE a better choice for what we
are running? I have read the info on file
vs. client server, but admit I don't under-
stand it.
We have seen reliable reports of Access-Jet multiuser, where all factors are
near perfect of 100+ concurrent users. I have never seen a report of more
than 25 concurrent users with MSDE.
- If we move to the MSDE, do I need
to install the sql server to each terminal?
No, but unless you intend to start over from scratch and redo your
application into an ADP ("Access Project") using ADO to access MSDE, you
will have to install the proper ODBC drivers on each user's machine.
- Is there going to be a noticable change
as far as the users are concerned? (I have
some users that are moving kicking and
screaming into the digital age and any
change is seen as traumatic)
Not if it is done well. You will have to evaluate whether, without
client-server experience, you can expect to do it well on your first try.
- If I convert my database to the MSDE,
am I going to be able to continue to make
changes to the forms, reports and tables
without learning a new program? Not afraid
to learn a new program, but want the transi-
tion to be fairly seamless for the users. I would
want to practice prior to the transition.
Yes, if you use ODBC and link the tables. You'll have to take a different
view of some things, but the learning curve won't be like "a new program".
I'd say the changes are going to be significant if you opt to redo from
scratch and replace your MDB with an ADP.
- Will I be able to make changes to the
forms and reports while others are access-
ing the data? (Currently, I find it necessary
to do my maintaining and changes after hours
because I am unable to save my work while
anyone is in the database.)
This indicates to me that you have multiple users logging in to the same
copy of the "front-end" or monolithic database. And that, in turn, leads me
to believe that you need to spend some time reading up on Access in the
multiuser environment and fixing what you have. My guess is, that with a
little help, you can overcome the performance problems, and, likely, the DB
bloat problem.

A good headstart on eliminating the bloat is to move any temporary files to
a temporary database, so that when you are done with them, you can just
unlink and then use the Kill statement to delete the temporary database.
And, yes, Tony has an example of this technique, too.

Split your database, if you haven't already, and put the shared tables and
data on a shared file on a server in your network. Give each user his/her
own copy of the front-end (queries, forms, reports, macros, modules). Do
your work on a "development copy", separate from the production database,
then roll out a new copy to each user when you have completed a project/fix.
- And finally, is MSDE going to end up
being the default engine?
I am not privy to Microsoft's plans, but I would be absolutely astonished if
MSDE were the "default engine" at any time in the future. In fact, it has
been stated that the Longhorn operating system, now in development (early
Beta) will have a file system based on SQL Server -- I'd guess the native
Longhorn file system will be the data store when Longhorn is released (a
couple of years down the pike, the pundits speculate).
Is this change inevitable?


Change is inevitable. A change to MSDE as the default engine for Access is
not only _evitable_, but highly unlikely, IMNSHO.

* BTW, none of the speculation above is
based on any "insider info" -- it's all drawn
from what I've read in publicly published
information.

Larry Linson
Microsoft Access MVP

Nov 12 '05 #2

P: n/a
A few things:

It is assumed that you are running a split mdb. That means the data mdb is
on the server, and EACH USER gets their own copy of the front end. That
front end should be a mde file. There is not access developer worth their
salt that will recommend any other possible type of setup. This setup also
applies to the 4 Citrix users (that means they each get their own logon, and
they EACH GET THEIR OWN COPY of the front end). So, the FE/BE split applies
to all users. Simply put, doing this will likely eliminate many of your
problems. Also, this approach allows you to freely work on your copy while
others work also (thus, you solve the problem of not being able to work
during the day). Hence, you work on the development version of the mdb front
end (FE). When you get it all working, tested, then you create a mde and
distribute that to each user. The FE is linked to the data mdb on the
server.

Further, with proper designs, this split concept can also eliminate much of
the file growth (or bloat as some call it). In addition, since each user now
gets a mde front end, you are free to develop all day long on a new version.
So, spend a few minutes reading the following two articles about splitting.
Regardless, you can't really develop and deploy ms-access applications any
other way.

http://www.microsoft.com/accessdev/a...ers/ba15_3.htm

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

More comments follow:

Currently the database is about 60MB, but it grows to 150 and larger
each week.
This probably means that the developers and designers of the application did
not pay much attention to using temp tables. They really need to be avoided
when possible (so often, lots of temp tables is due to poor designs, or
simply lazy developers). Another solution is to move the "temp" tables out.
Using a split mdb also makes this a lot easier.
I am constantly having to compact it to keep it running
smoothly. Also at least once a day, the terminals accessing the
database via Citrix lock it into exclusive mode and they (the Citrix
users) have to exit and reenter in order for anyone else to get in.
Likely, again, you are not following recommend practices, and you need to
make sure each user gets their own copy of the front end (FE).
My question(s) (finally!) is:

- Is MSDE a better choice for what we are running? I have read the
info on file vs. client server, but admit I don't understand it.
The MSDE is the SAME AS sql server. So, MSDE is 100% compatible with sql
server. However, it is free, and thus its performance is limited to about 5
users. If you outgrow the MSDE, then you supposed to upgrade to sql server
WITH NO CHANGES required. So, if for example you need to write 100%
compatible systems for sql server, then the MSDE is a good choice.
- If we move to the MSDE, do I need to install the sql server to each
terminal?
No, sql server, Oracle, Sysbase, MySql are server based products. They are
systems that can dish out data. Server systems are simply programs that run
on a server. So, for example to host a web site, you could run a internet
services program on that server. That server can then dish out web pages to
users to request them. Sql server (or the MSDE) is the same idea:
You send the server sql commands, and it dishes out data back to you.
This is what we call client to server. That "client" can be a VB program,
ms-access, or even a web server. So, in the case of ms-access and JET, well
you don't have to install ms-access, or anything on the server do you? (it
is just plain old windows file). No processing, or software needs to be
installed on the server side when you use ms-access with a JET file share.
This tends to make ms-access quite simple.
- Is there going to be a noticable change as far as the users are
concerned?
No, but from a programming and development point of view there is likely to
be. I mean, with a well written application, I sure you have custom menus,
and all of the ms-access interface was hidden a long time ago...right? Also,
you are now considering using a sql server based product, and you don't even
have the basic setup for ms-access correct right now! I mean, this stuff is
not too hard, but when someone has their current setup all wrong, and you
are talking about moving up to the next level in complexity, you are already
getting in over your head.
- If I convert my database to the MSDE, am I going to be able to
continue to make changes to the forms, reports and tables without
learning a new program?
Most of your forms, reports will remain the same. Changing tables requites
you to use the sql server based software to do this. You can also consider
using what we call a ADP project in ms-access, but your existing
applications will have to be converted, and it is not a seem less process.
- Will I be able to make changes to the forms and reports while
others are accessing the data?
If you had a proper setup in the first place, then you can do this with a
JET file share (but, the answer is yes...you can also do this when using sql
server assuming that each user gets their own access FE).
- And finally, is MSDE going to end up being the default engine? Is
this change inevitable?


Not likely, there is still a market for file based systems as opposed to
"server" based systems. However, server based systems are catching on since
they are becoming cheaper and cheaper, and they allow better connectivity
then does a file share. They are also MUCH more reliable, and as the cost
drops, they make more and more sense.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.