Dan Waters
dwaters at usinternet.com
Tue Jun 10 12:23:25 CDT 2008
Mark,
I like your example for one record at a time updates.
How do you handle a situation like:
UPDATE tblTable Set Field = 'TRUE' _
WHERE Date = CurrentDate
Or:
DELETE * FROM tblTable _
WHERE Date = CurrentDate
Of course, each of these examples could involve many records.
Thanks!
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, June 10, 2008 11:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form
I can tell you how our PeopleSoft (web based) App handles it...it compares
the last_update_timestamp...if it is the same as when you pulled your
recordset...you get to save...if not...you lose your changes.
Golden rule with the app we purchased...first person to click save...wins!!!
Thanks,
Mark A. Matte
> Date: Tue, 10 Jun 2008 12:06:43 -0400
> From: jwcolby at colbyconsulting.com
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] VBA Unbound data entry / update form
>
> Rocky,
>
> I hate to jump all over your method because I want a method
> to use, but you are completely ignoring locking / update
> issues. What happened if another user updated the record
> between the time you loaded yours and the time you write it
> back?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Rocky Smolin at Beach Access Software wrote:
>> I know how to make an unbound form but I haven't got enough nerve to
present
>> such heresy - it runs counter to strongly held religious beliefs.
>>
>> OK - briefly - but you didn't hear it from me.
>>
>> 1. Design your form as you would a bound form but no Control Source in
the
>> text boxes.
>> 2. Create a recordset in the Open event of the form (DAO or ADO - I
prefer
>> DAO) using pretty much the same SQL or query as you would for your Record
>> Source in a bound form.
>> 3. Add a module to put the fields from the current record of the
recordset
>> into the text boxes which you can call whenever you want to display the
data
>> on your form.
>> 4. Add a module to put the values in the text boxes into the fields of
the
>> current record (in DAO use .Edit or .Add and .Update) which you can call
>> whenever you want to write the textbox values back to the table.
>> 5. I always put my own navigation buttons on the unbound for - First,
Last,
>> Next, Previous with Click events that move the recordset point
>> appropriately.
>>
>> 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 Jim Lawrence
>> Sent: Monday, June 09, 2008 5:38 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>> Hi Jennifer:
>>
>> I have no idea how to create bound Access forms. ;-) The last bound
database
>> that I have worked with was back in '97.
>>
>> Jim
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer Gross
>> Sent: Tuesday, May 27, 2008 5:12 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] VBA Unbound data entry / update form
>>
>> I have no idea how to create an unbound form in Access. I always use
bound
>> forms. Though I would be interested to know how it's done. Unless I've
got
>> it wrong, that seems to be the basic question here - For those of you who
do
>> it, how do you create an unbound form? How do you populate the textboxes
>> initially and then how do you save the information back to the tables?
>>
>> It's beginning to sound like nobody really does it.
>>
>> Jennifer
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Tuesday, May 27, 2008 2: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
>>
>>
>> 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
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> No virus found in this incoming message.
>> Checked by AVG.
>> Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date: 9/6/2008
>> 10:29
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Search that pays you back! Introducing Live Search cashback.
http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=srchpaysyo
uback
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com