[AccessD] VBA Unbound data entry / update form

Dan,

 >I've never indexed an editable field, so I'm not familiar 
with the effects.

Indexes are used to speed up sorts.  If you have a table 
with thousands of names, and you want to sort on last name, 
first name, then you have to put an index on both the last 
name field and the first name field.  Want to sort on SSN? 
index it.  Want to sort on birth date?  index it.  Want to 
sort on... index it.  Other than PK/FK fields, that is what 
indexes are for.

 >so being bound is unlikely to be the root cause.

Being bound is not the root cause, the root cause appears to 
be the way that JET stores indexes and memo fields in the 
MDB container.  Do the same thing with a SQL Server BE and 
the problem goes away, bound or unbound.



John W. Colby
www.ColbyConsulting.com


Dan Waters wrote:
> Well, I was trying to simplify things and remove some complexity.
> 
> Based on what you said about indexes, my next step would be to remove all
> the indexes on editable fields.  This would be just an experiment to see if
> the problem goes away.  I've never indexed an editable field, so I'm not
> familiar with the effects.
> 
> In any case, there must be something specific that can be solved.  I've
> never heard of the symptoms you're describing from anyone - so being bound
> is unlikely to be the root cause.
> 
> Best of Luck!
> Dan
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, May 27, 2008 5:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] VBA Unbound data entry / update form
> 
>  > 1) If your situation gives you the choice between Access 
> and SQL Server, then you can use SQL Server Express.  For 
> your customer's benefit - it's free!
> 
> I know that, and you know that, but they have used an MDB BE 
> for most of a decade.  SQL Server is the great unknown.
> 
>  > 2) How many concurrent users?  Too Many?
> 
> In the majority of cases, no.  There ARE specific places 
> where too many users cause mysterious issues with an MDB. 
> Memos are written in "pages", as are indexes.  If you open a 
> record and start to edit it, it "locks" an entire "page" of 
> the index structure or the memo area, which locks not just 
> your record but potentially many others.
> 
> This simply doesn't happen in SQL Server because SQL Server 
> doesn't have this "page" system for storing memo fields etc.
> 
>  > 3) How many indexes on the tables?
> 
> Not the point, the point is that ALL indexes are stored in 
> Index pages, and entire pages of indexs can be locked by a 
> single edit.
> 
> Creating / using Indexes should be determined by need, not 
> arcane locking issues within Jet.
> 
>  > 4) Are you starting up by setting a recordset to open and 
> leaving it that way?
> 
> If you are talking about creating and holding locks on the 
> BE, yes I am, but that isn't the issue either.
> 
> John W. Colby
> www.ColbyConsulting.com