I was asked to help troubleshoot an Access database that is having data integrity problems.
The database is used by many users (~50 - 100) to track risks and issues on a major corporate software initiative. There are peak times of activity, i.e. 1 hours before the weekly Risks and Issues meeting.
The symptom is that data which appears to be saved is "lost". This is intermittent / recurring but happening even more and more.
Upon investigation, I saw that they have the data and form in one database (no separate be). So I thought that was the issue, i.e., each user clicks on the common source, so it loads "locally", and hence the local application for each user is "separate" from the other.
But in testing it out, I saw that User A could create a risk and it was visible to User B. So I started scratching my head, saying, well, there's something going on that I don't know about - apparently it is Dynamic Data Exchange. btw, the database is "home-grown", but I am pretty sure it started as the "Issues" template provided by Microsoft (it uses all macros, there is hardly any VB code).
So my test proved that multiple users could enter data and see each other's data. So my questions are:
- is this because of Dynamic Data Exchange? I found this link and did a quick perusal,
http://msdn.microsoft.com/en-us/library/ms648774(VS.85).aspx - if it is due to DDE, then am I correct in saying User A has their local copy and User B has their local copy? If so, what is the "central" repository? Is it the source file that each of them clicked on (and loaded to ~temp [local])? When they both close, what happens?
So a couple more questions.
a. given that this solution kind of works with by virtue of DDE (I presume) with a small amount of users, isn't this a reasonable option, especially since performance is fabulous? It would certainly be good to "fix" this solution, it at all possible, since splitting into a be (Sql Server or even Access) is a fair amount of work, and then performance (at least with Access) goes south due the nature of this organization (WAN).
b. Do you have to set anything up to make the d.b. work as I describe (one source, but multiple users can update at the same time, by virtue of DDE)?
c. Based on the symptom of users' data getting "lost", I'm assuming that the combined fe / be solution (with DDE) is just not holding up due to too many users. Also, it hasn't been compacted in ages, I compacted a copy and it went from 45mb to 13mb.
Start Free Trial
