Solved

MSDE 2000 low maintenance?

Posted on 2003-10-23
17
255 Views
Last Modified: 2010-05-01
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?
0
Comment
Question by:AirResourceSpec
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 9

Accepted Solution

by:
dancebert earned 84 total points
ID: 9607784
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
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9608068
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
 
LVL 1

Assisted Solution

by:TwistD_PissR
TwistD_PissR earned 83 total points
ID: 9608069
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
 
LVL 1

Expert Comment

by:TwistD_PissR
ID: 9608115
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
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9608240
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
 
LVL 1

Expert Comment

by:TwistD_PissR
ID: 9608269
So are you using the "slow phone" lines as a means to retrieve the data?
0
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9608286
Yes we retrieve a small amount of data.
0
 
LVL 9

Expert Comment

by:dancebert
ID: 9608313
>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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:TwistD_PissR
ID: 9608405
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
 
LVL 1

Assisted Solution

by:GenericUserName
GenericUserName earned 83 total points
ID: 9608652
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
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9609148
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9617789
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
 
LVL 27

Expert Comment

by:planocz
ID: 10173580
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
 
LVL 9

Expert Comment

by:dancebert
ID: 10178582
Suggest no refund because he got much of the info he was looking, i.e., that MSDE is not low maintance.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10178684
Suggest you split the points among: dancebert, TwistD_PissR and  GenericUserName
0
 
LVL 27

Expert Comment

by:planocz
ID: 10183301
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now