SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 MSDE (2000)
 Problems with Access ADP & MSDE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

browsw
Starting Member

2 Posts

Posted - 06/23/2004 :  11:36:28  Show Profile  Reply with Quote
Hello All,
I have just migrated our school reporting system from access to an access adp & MSDE SQL server.
We have been having problems with people writing a comment in a text field (in the access ADP from) clicking to go onto the next boy to write a comment, and then when they go back the data has vanished from the previous boy's comments.
Everything worked fine in the access MDB, so we have had to move back to that temporarily. I though it may have something to do with the 5 concurrent something limit on MSDE, but not sure. Would appreciate a lot any comments or ideas any has - THANK YOU!

Some technical data about the DB:
Tables that are accessed by the form&subform:
[DATA] (Has RefNo, Name, class etc.)
[R - MATHS] (Subject tables containing RefNo and comment fields)
[MR - Previous] (Previous Class Marks)
[R - Maths_Pr] (Previous Reports)

Stuart Brown

sbt1
Yak Posting Veteran

89 Posts

Posted - 06/23/2004 :  14:36:35  Show Profile  Reply with Quote
I doubt seriously that you're running up against the 5-concurrent user threshold. What it sounds like is that the data in the form isn't dynamically linked to the database. How many users are on the system at the same time? And besides, you can have 400 users on at the same time, only if more than 5 are simultaneously committing changes will the workload limiter even begin to think about slowing things down.

I've not done what you're doing, with Access; our apps use Win32 executables to write to/from SQL Server and MSDE. Those apps always have to save the records to the database.

But, like I said not sure about mixed Access/database projects. Maybe there's something you need to setup so that Access writes all changes to the database as soon as the user changes fields.
Go to Top of Page

browsw
Starting Member

2 Posts

Posted - 06/24/2004 :  18:42:29  Show Profile  Reply with Quote
Thanks for the reply sbt1 -

I have worked out the the Access ADP is downloading all the records from SQL and then uploading them when the form is closed - this causes the overwritting of other people's comments that have been written while the form is opened by user A.

The deal is that when a teacher clicks on a Subject on the main menu it opens the subject form - then they click on a button to filter out just their class.

We are currently still using the original Access MDB which writes the current record when going onto a new record - which the ADP does NOT. Can I replicate this MDB behaviour of writing the records in the ADP?
Go to Top of Page

sbt1
Yak Posting Veteran

89 Posts

Posted - 06/28/2004 :  10:57:58  Show Profile  Reply with Quote
I'm not sure. The system I made that we use here used separate .EXEs to read/write from the database. I would check some of the Access forums to see how you need to resolve the issue.

As I understand it Access moves entire table's data back and forth when editing records... a very slow, corruption-prone process. May make sense to consider migrating from an Access front-end to VB (and I say VB only because it's probably easier to convert from Access code to VB; it wouldn't be my first choice of a language).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000