MSDE 2000 low maintenance?

My company has 40 stations across the States that use a stand-alone PC which run a vb application using an ACCESS database.  These stations are visited by an operator once a month.  We are having maintenance problems such scheduled compact and repair finds the database in a occupy state.  The databases are 30 meg with one table at a million records.

MSDE sounds like a better solution, but I am not finding much documentation or testimonials.  Any comments?
LVL 1
AirResourceSpecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dancebertCommented:
Access may be a toy database, but the maintenance is easy.  MSDE is from the core of Sql Server, an almost-industrial strenght database.  The maintenance is needed less, but is not easy.  I've worked with enough DBAs (Data base administrators) to know that a poor one can setup Sql Server in a way that eventually causes performance or space problems that the DBA can't solve.  But a poor DBA is usually better than no DBA.  Can your firm afford to buy DBA time from a support company?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AirResourceSpecAuthor Commented:
We are not looking to use a support company.  I am looking for documentation and/ or testimonials about the use of MSDE in our situation.
0
TwistD_PissRCommented:
Dancebert is right.  If you were to step up to SQL server or MSDE you will definitly want a DBA.
The biggest issue sounds like the machines not being networked.  Which would minimize the amount of footwork as well as the amount of time.  This would also simplify the creation of a MSDE/SQL Server type of implmentation and the data could be batched from the sites or done in "realtime".  Then database replication would come in real handy, but only if you have a DBA...


TwistD
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TwistD_PissRCommented:
Sure MSDE could be used, but are you any better off then when you had Access?
I guess it depends on what your limiting factors are...

No the less, MSDE can be used in your situtaion.
I have used the MSDE solution between to sites that were located in the same state, but different buildings.
The limiting factor was the inability to get a communication established.
So we started each site with a baseline and from there we just exported the transactions to one another on a weekly basis.  One of the concerns were availability of updated data.  But we worked with what we had...

0
AirResourceSpecAuthor Commented:
The stations only have very slow phone lines, so we will not network.  The only interaction we want is to grab small daily files.
0
TwistD_PissRCommented:
So are you using the "slow phone" lines as a means to retrieve the data?
0
AirResourceSpecAuthor Commented:
Yes we retrieve a small amount of data.
0
dancebertCommented:
>We are not looking to use a support company.  I am looking for
>documentation and/ or testimonials about the use of MSDE in
>our situation.

I guess I didn't make my point clear enough.  Whatever problems are solved by using MSDE instead of Access will be nothing compared to the problems caused by using MSDE without trained DBA support.
0
TwistD_PissRCommented:
Here would be a scenario (granted there are more options and each one will be involved):

- At your primary site install SQL Server (this will enable replication)
- At your multiple target sites you will install MSDE and subscribe each instance to a MERGE replication from the primary

Basically, all the sites will have the same data, based on the transactions occuring and replicating (time based)
This will eliminate the foot issues with the access databases and centralize/backup the sites data.

HTHs!

TwistD
0
GenericUserNameCommented:
I agree with TwistD_PissR, under the assumption that network connectivity is always available and speed is not an issue.

I currently have a database application that utilizes MSDE. From my experience, it seems just as stable as SQL server (though not as feature-rich), and is a definite improvement over Access. Since it works well under network connectivity (with other workgroup users being able to easily access the databases), I see no reason why it would cause problems with a repair.

Good luck finding documentation. There are a few MSDN articles about it, but few people seem to use it, as Access usually solves the problem unless you need SQL Server. Since it seems the sites are modem-connected, I just wouldn't recommend SQL Server as a solution, especially if there is no need for you to have the data at your site. If the data needs to be restricted to just them (think what if your server was hacked), then definitely go with the current Access solution or use MSDE.

I personally have no problems with MSDE. You get the added benefit of command-line interfacing with the database, or connecting through an Access project (.adp) file. If your database is set up, it is very easy to transfer to the users. Many records seem to be no problem.

From your description, however, it seems as if each PC has a standalone database. This may be overkill for MSDE, since it's primarily used for an inter-network solution. However, 30MB is pretty large for one Access database, so you may consider changing over. VB+MSDE applications are easy to develop, and you get the added benefit of migrating easily to SQL Server if you so choose. Overall, it's your decision. I'm not certain about the compact and repair thing, as we do not use that except for on weekends when no one is there (to avoid usage conflicts). Good luck!
0
AirResourceSpecAuthor Commented:
There would be no reason to move to SQL Server.

There are no user for the most part, just once a month.  The computer sits and pull data from dataloggers 24/7.  The station are remotely located in NPS.
0
Anthony PerkinsCommented:
Than stay with what you have, you obviously cannot afford to maintain anything more robust.  It also, should be pointed out that you would have to make significant development changes in order to use MSDE to its full potential.

Anthony
0
planoczCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from AirResourceSpec from 10/24/2003 comment
Delete/Refund is recommend.
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
0
dancebertCommented:
Suggest no refund because he got much of the info he was looking, i.e., that MSDE is not low maintance.
0
Anthony PerkinsCommented:
Suggest you split the points among: dancebert, TwistD_PissR and  GenericUserName
0
planoczCommented:
I will leave a recommendation in the Cleanup topic area that this question is:
No response from AirResourceSpec from 1/22/2003 comment
Award points to dancebert, TwistD_PissR and  GenericUserName recommend.
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.