Access database with multiple front ends - records "disappearing" 
Author Message
 Access database with multiple front ends - records "disappearing"

Hi. Someone in my department has written an app. He's gone and I have to
support it. Groan. ;)

Anyway, it is has an MS Access database which has an ASP front end and a VB
front end. The ASP uses ADO, of course. The VB app uses DAO 3.51.  The
database is completely not normalized. Totals are stored in a table and
detail in another table. The app tracks vacations time (among other things).
So if you have 100 hours of vacation time allotted for the year that is
stored in a totals table. OK so far. If you have taken 30 hours in various
chunks this year, the individual vacation days are stored in individual
records and the 30 total taken is stored in the totals table.

Here is my situation. I am seeing examples in which a person has requested
to take 8 hours of vacation. The total table is showing that taken = 8. But
there is no detail record. The code that adds the record adds the record
before updating the totals. I cannot see a place in code which is failing to
work properly in any case that a I can reproduce.

This app is used intermittently by ~400 people. I have heard that MS Access,
when put under load, can start having weird and erratic behavior. But has
anyone every seen anything as weird as disappearing records?

Thanks

S



Tue, 12 Oct 2004 22:02:18 GMT  
 Access database with multiple front ends - records "disappearing"
When you have that many users accessing an Access database, you are bound to
have problems. Access was not designed to handle anywhere near that load.
Your best bet is moving to an enterprise level DBMS like SQL Server. As for
the missing records, it is strange, but I have seen things like that happen.
In your code, make sure that the routine that adds up the hours is calling
the update method of your ADO or DAO recordset after it writes to the totals
table. Also, make sure that the update method is being called each time a
leave time transaction is written to the detail table. If you can verify
that the records are getting written and the database updated correctly, so
you are certain that the saved record exists in the database, if you then
lose the record at a later time, this could be either data corruption (which
is fairly common in Access, especially with 400 users!) , or it could be
some code you have that is deleting records and you don't realize it. (Scan
your code for delete queries and .delete methods), or it could be something
that is being done by a user through the back door. Can your users access
the database through MS Access? You may want to evaluate your DB security.

If the problem is data corruption, you will most likely experience other
symptoms (error messages, unexpected behavior, record locking errors, etc.)

Good luck.

HTH

Brian

Quote:
> Hi. Someone in my department has written an app. He's gone and I have to
> support it. Groan. ;)

> Anyway, it is has an MS Access database which has an ASP front end and a
VB
> front end. The ASP uses ADO, of course. The VB app uses DAO 3.51.  The
> database is completely not normalized. Totals are stored in a table and
> detail in another table. The app tracks vacations time (among other
things).
> So if you have 100 hours of vacation time allotted for the year that is
> stored in a totals table. OK so far. If you have taken 30 hours in various
> chunks this year, the individual vacation days are stored in individual
> records and the 30 total taken is stored in the totals table.

> Here is my situation. I am seeing examples in which a person has requested
> to take 8 hours of vacation. The total table is showing that taken = 8.
But
> there is no detail record. The code that adds the record adds the record
> before updating the totals. I cannot see a place in code which is failing
to
> work properly in any case that a I can reproduce.

> This app is used intermittently by ~400 people. I have heard that MS
Access,
> when put under load, can start having weird and erratic behavior. But has
> anyone every seen anything as weird as disappearing records?

> Thanks

> S



Tue, 12 Oct 2004 23:17:43 GMT  
 Access database with multiple front ends - records "disappearing"
Also, make sure when the job is done you add a record to your name with at
least 80 hours vacation time... :-)

A.



Quote:
> Hi. Someone in my department has written an app. He's gone and I have to
> support it. Groan. ;)

> Anyway, it is has an MS Access database which has an ASP front end and a
VB
> front end. The ASP uses ADO, of course. The VB app uses DAO 3.51.  The
> database is completely not normalized. Totals are stored in a table and
> detail in another table. The app tracks vacations time (among other
things).
> So if you have 100 hours of vacation time allotted for the year that is
> stored in a totals table. OK so far. If you have taken 30 hours in various
> chunks this year, the individual vacation days are stored in individual
> records and the 30 total taken is stored in the totals table.

> Here is my situation. I am seeing examples in which a person has requested
> to take 8 hours of vacation. The total table is showing that taken = 8.
But
> there is no detail record. The code that adds the record adds the record
> before updating the totals. I cannot see a place in code which is failing
to
> work properly in any case that a I can reproduce.

> This app is used intermittently by ~400 people. I have heard that MS
Access,
> when put under load, can start having weird and erratic behavior. But has
> anyone every seen anything as weird as disappearing records?

> Thanks

> S



Wed, 13 Oct 2004 17:35:03 GMT  
 Access database with multiple front ends - records "disappearing"
This would work since the "audit trail" the guy "designed" into the app is a
joke.

S

Quote:
> Also, make sure when the job is done you add a record to your name with at
> least 80 hours vacation time... :-)

> A.



> > Hi. Someone in my department has written an app. He's gone and I have to
> > support it. Groan. ;)

> > Anyway, it is has an MS Access database which has an ASP front end and a
> VB
> > front end. The ASP uses ADO, of course. The VB app uses DAO 3.51.  The
> > database is completely not normalized. Totals are stored in a table and
> > detail in another table. The app tracks vacations time (among other
> things).
> > So if you have 100 hours of vacation time allotted for the year that is
> > stored in a totals table. OK so far. If you have taken 30 hours in
various
> > chunks this year, the individual vacation days are stored in individual
> > records and the 30 total taken is stored in the totals table.

> > Here is my situation. I am seeing examples in which a person has
requested
> > to take 8 hours of vacation. The total table is showing that taken = 8.
> But
> > there is no detail record. The code that adds the record adds the record
> > before updating the totals. I cannot see a place in code which is
failing
> to
> > work properly in any case that a I can reproduce.

> > This app is used intermittently by ~400 people. I have heard that MS
> Access,
> > when put under load, can start having weird and erratic behavior. But
has
> > anyone every seen anything as weird as disappearing records?

> > Thanks

> > S



Sat, 16 Oct 2004 21:27:11 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. "Disappearing" Records in Access 2000

2. "Disappearing" Records in Access 2000

3. Recording a "record" in a database

4. Recording a "record" in a database

5. Ending an application without "End Sub"?

6. Linking multiple Access backends with a single front end

7. Exporting Access records to MS Word via VB6 front end app

8. Exporting Access records to MS Word vis VB6 front end app

9. "Record is Deleted" - (#3167) Unusable database

10. Access database corruption using vb front ends

11. Visual Basic Front Ends For Access Databases

12. Help - using VB as front end for Access Database

 

 
Powered by phpBB® Forum Software