[AccessD] VBA Unbound data entry / update form

Rocky Smolin at Beach Access Software rockysmolin at bchacc.com
Tue May 27 18:22:07 CDT 2008


Jennifer:

You use DAO to navigate the recordset  =MovePrevious,Next,First,Last,
FindFirst,Next, etc - and when you fetch a record call a sub that transfer
the values from the fields to the unbound text boxes.  You need a save
button on the form for the user to save the edited (or added) record because
it doesn't happen automatically just by moving off the record. Save calls a
sub that copies the values from the unbound text boxes to the DAO record.




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 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

No virus found in this incoming message.
Checked by AVG. 
Version: 8.0.100 / Virus Database: 269.24.1/1469 - Release Date: 27/5/2008
13:25




More information about the AccessD mailing list