Database corruption Network or Program?

[Guest]

I currently have a database that that is used by 4 users over a network. The
four users are connected via winterms. The database is being accessed by
these users simultaneously and each user has his own form that he updates.
Each form also has a sub form and all the data is stored in two tables. The
database isn’t split due to the winterm issue.
The problem is that every so often the network starts to slow down and my
database becomes corrupt. Sometimes just the relationships are deleted and
other times I have to restore a backup copy. Our IT department insists that
the problem is with the database but I feel it is the network. We also have
the same problems with excel and other programs that we use. The IT guys tell
me that Access isn’t meant to be used for multiple users and this is the
problem. The database is in .mdb form and is stored on our local network. IT
tells me that we do have a SQL server available but say this would not help
the problem. Currently they are wanting me to convert my database and use
Infopath which I am hesitant to do. I am told that the reason this is wanted
is because it is more user friendly and can reside on the SQL server. I
guess I want to know what does it take to run a database from a SQL server
and do you think this would help? My data base is simple to use and the
reports are important in our production facility. Any advice would be
appreciated.

 

[Mastercafe - Juan]

I don't recommend you use WinTerm with access MDB.
The best solution is MDB on server and MDE/MDB on the end user terminal,
link tables and ready to use.
The problem with MDB is left open any table or process, normally create a
corrupted MDB.
If you want to continue using MDB with WinTerm you need change your
application to retrieve only 1 registry, or recover more to snapshot format.
Similar operation to ASP uses with an MDB.
MDB permit much options to use on network, but need some specifications that
convert the traditional MDB in an application closed to ASP.
Other option is convert MDB to SQL server and try to connect.

--
Saludos desde Oviedo (Asturias)

Juan Menéndez
Mastercafe S.L.
www.mastercafe.com
(e-mail address removed)

Si la información recibido te ha servido indicalo con otro post.
En caso de resolverlo por otros medios, indica la solución usada
ayudaras a otros y aprenderemos todos.

 

[Sylvain Lafontaine]

I don't know what the database isn't split but unless you really cannot do
this with Winterm, this is probably a bad decision and could be one of the
source of your corruption problems.

When you say that relationships are deleted, do you mean that records with
foreign keys are missing or that the database schema have really been
changed?

Unless you are using some very, very strange VBA code, you should be OK
using SQL-Server as the backend and this would probably stop your corruption
problems. In many cases, a basic conversion of the backend to SQL-Server
using linked tables will take at most a few hours (however, I cannot tell
for sure in your case as I don't know your stuff). I don't understand why
your IT department say that this would not help.

My opinion is that one your IT guys is willing to make his hands on Infopath
with some project and is searching an excuse for doing so. This is usually
a bad combination when your database becomes a study project for someone
else.

 

[Guest]

The database isn’t split and all users use the same file to enter the
information. I was told that it would not make a difference with the winterm
if I split it. If I did split it the backend would be on the local server and
the front end would reside on the corporate server in our “My Documentsâ€
folder. I assume with a split database each user would take a copy of the
front end and store it in his “My documents†folder. With this set up would
I need to make changes to the “master†front end then instruct each person to
make a new copy after the changes? Would I split the database then upsize
just the back end for the SQL server? If I did this then could the front end
reside on the local server and each person use the same front end?

When I said the relationships are deleted I mean that when I go to the
relationship view the links between them are gone and in some cases the
primary key has been deleted. I must go back in and re-establish the
relationships and reset the Key.

The VBA code that I use is straight forward and not to technical.

You talk about the SQL conversion, is this done by using the “Upsizing
Wizard�
If so, do you just upsize the backend for storage on the SQL server?

As far as my IT department goes they have never like the database that we
use. Part of it is that they do not have a clear understanding of how access
works. I was told in the beginning that they would not support it. IT keeps
telling me that ACCESS is geared for a single user and was never intended for
multiple users using the same database. Everything that I have read is quite
the opposite of this.

Our company has recently upgraded to Sharepoint and has been pushing InfoPath.
I agree with them that InfoPath has some great possibility but I do not feel
that it can compete with ACCESS as far as data collection and reporting.

As you can tell I am still struggling with ACCESS myself. This project was
dumped in my lap about three years ago and it has been an up hill battle. I
have learned a lot from this community and can’t tell you enough how much I
appreciate people like yourself and all the others that take the time to
answer our questions and help us out.

 

[Sylvain Lafontaine]

In a perfect world, splitting or not a database wouldn't make any
difference. However, before Access can use or run a form, a module or a
query, it must compile it and this process seems to be extremely sensitive
to any disturbance or to any difference of versions of Windows and Service
Packs between your users. Even when all of your users have the same version
of Windows because they are using Winterm or Citrix/TS, it has always been
suggested in this newsgroup that each user should have his own copy of the
frontend in order to minimize the risk of corruption and this apply whether
the backend is a MDB file or SQL-Server.

Of course, this rise the problem of updating these copies when there is a
new version but this is a small problem in comparaison of having corruption
on a regular basis. Search Google or this newsgroup for « FE AutoUpdater »
to find an easy solution for this.

For the upsizing process, yes, you can use the Upsizing Wizard and you
should be done at the end in most cases. If you are using a lot of DAO, you
will have to add the option dbSeeChanges here and there but Access will tell
you everywhere you have missed it when you will try to run the code; so it's
easy to correct the situation.

InfoPath can be a good solution for collecting data; however, it's
bi-directional communication with the database is very weak so if you have
to go further than just collecting data, it's not the ticket for you.
Besides, if you want to use InfoPath, you will have to set up your database
on SQL-Server, so I don't see the point of not trying this first with
Access.

 

[Guest]

Thanks so much for the information. I work shift work and will not be able to
try this until a week from Monday 6/12/06 but I will try it and I will post
how it goes. Again Thanks for the advice.