[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


Dan Waters wrote:
> John - I've been reading this with interest:
> 
> 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! 
> 
> 2) How many concurrent users?  Too Many?
> 
> 3) How many indexes on the tables?  I actually only use the primary key as
> an index on every table.  More indexes slows down writing performance.
> 
> 4) Are you starting up by setting a recordset to open and leaving it that
> way?  This greatly reduces 'churn' in the locking database.  When I set this
> up all my users reported a significant performance improvement.  Code below:
> 
> '------------------------------------------------------------------------
>     Dim stgConnect As String
>     Dim dbs As DAO.Database
>     Dim tdf As DAO.TableDef
>         
>     stgConnect = BEFullPath
>     Set dbs = DBEngine(0)(0)
>     Set tdf = dbs.TableDefs("tblConnect")
>     If tdf.Connect <> ";Database=" & stgConnect Then
>         tdf.Connect = ";Database=" & stgConnect
>         tdf.RefreshLink
>     End If
>     dbs.Close
> 
>     Set GrstConnect = DBEngine(0)(0).OpenRecordset("tblConnect",
> dbOpenSnapshot)
> '------------------------------------------------------------------------
> 
> Notes:  BEFullPath is a function to return the full path to the BE.
>         tblConnect is a one row one field table in the BE.
>         The table link is refreshed because the table link is in a library.
>         GrstConnect is a globally defined DAO recordset.
>         GrstConnect remains open until just before the database is closed.
> 
> Good Luck!
> Dan