Dan Waters
dwaters at usinternet.com
Tue May 27 17:19:54 CDT 2008
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
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, May 27, 2008 4:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form
Rocky,
The whole point of the unbound exercise is to open a single
record, populate the form with the values, then close the
recordset. If I am going to hold the record open then I
might as well just bind the form and be done with it.
When the user saves the data in the unbound form, the form
needs to check if anything is dirty, if so then it needs to
grab the record again, if the record no longer exists then
the record has been deleted and then what...? Inform the
record that the record was deleted? Refuse to save the changes?
Assuming the record has not been deleted, then the updated
fields have to check the same fields in the underlying
record to see if the "old data" from the form (the data at
the time the form opened) matches the data in the
corresponding field in the record. If so then it can just
be written back. If not then the user has to be told that
the data changed and... Ask the user if they want to
overwrite the changes in the table with the changes they
just made? Refuse to save their changes?
As you can see, disconnected records cause a lot of
headaches that bound forms "just take care of".
"Disconnected" means that no lock is placed, no recordset is
held open, and dbPessimistic and dbSeeChanges are irrelevant.
I am not an "unbound" kinda guy so I have not answered these
questions, I am attempting to get answers for "best
practices" on how to handle these and any other similar issues.
The only reason I am even getting into this is that I have a
couple of "choke points" where for some unknown, unexplained
reason, I am getting "record locked" issues as the user
tries to edit records. AFAICT these are not even records
that other users are editing or even viewing. In fact I get
"locked" records when trying to ADD A NEW RECORD... which
indicates that the issue is way down in the guts of Access /
JET in things like Index or memo pages. My THEORY is that
if I make these forms unbound then these memo and index page
locks will go away since there are no longer dozens of users
trying to lock memo pages or index pages, except very
briefly as they save new / changed records.
Unfortunately, the "art of unboundedness" is arcane (at
least to me) and I am not finding good solid references as
to how to handle these things, nor what the results will be
at the "jet guts" level.
I tried to convince my clients to just move to SQL Server,
which doesn't have these memo and index page lock issues,
and let me go back to my peaceful bound form world. They
are not doing that so here we are.
John W. Colby
www.ColbyConsulting.com
Rocky Smolin at Beach Access Software wrote:
> John:
>
> Won't dbPessimistic in the LockEdits parameter of the OpenRecordset do
what
> you need to have done with regard to multiple users attempting to edit the
> same record?
>
> dbSeeChanges may also bee what you need. From the help:
>
> Use the dbSeeChanges constant in a Microsoft Jet workspace if you want to
> trap changes while two or more users are editing or deleting the same
> record. For example, if two users start editing the same record, the first
> user to execute the Update method succeeds. When the second user invokes
the
> Update method, a run-time error occurs. Similarly, if the second user
tries
> to use the Delete method to delete the record, and the first user has
> already changed it, a run-time error occurs.
>
> Typically, if the user gets this error while updating a record, your code
> should refresh the contents of the fields and retrieve the newly modified
> values. If the error occurs while deleting a record, your code could
display
> the new record data to the user and a message indicating that the data has
> recently changed. At this point, your code can request a confirmation that
> the user still wants to delete the record.
>
> HTH
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, May 27, 2008 1:05 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] VBA Unbound data entry / update form
>
> LOL, I do want to do a lot with an unbound form. It should open a record,
> display that record, allow it to be edited, and save the record back again
> once edited, all without corrupting data, honoring data saves before it
> saves etc.
> Bound forms do all of that, and the unholy alliance of unbounders have
> always indicated that they could also do all of this stuff.
>
> Now I need to do all that stuff and just thought those UAU members would
> have their expertise to share.
>
> 8-0
>
> John W. Colby
> www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com