[AccessD] VBA Unbound data entry / update form

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




More information about the AccessD mailing list