Committing changes to database

  • Thread starter Thread starter Derek Hughes
  • Start date Start date
D

Derek Hughes

Is there any way of getting access to commit the changes to the database,
that doesnt involve me.refresh ?

I basically want to be able to tell access to comit a field to the db
immediately rather than needing to wait for movenext.

I have sql server triggers that I want to perform its actions as soon as the
field is changed, not next time the data is loaded.

Is there some way to do this.

I also find me.refresh to be a pain, especially when working over slow links
as we do at points. Is there any other similar methods that dont require the
redownload of the whole recordset ?

Cheers

Derek
 
Normally, me.requery re-loads the whole record set (and does re-set the
current record form to the first record).

me.refresh only re-loads records with changes. Usually, this means that only
the current record is re-loaded (and, the position is NOT changed). However,
since ms-access does have to "check" for other records that are changed...it
will cause un-necessary traffic.

So, to just force a disk write, you can use:

me.Dirty = False

the above does the same thing as me.refresh, but ONLY the current record.

Often, the code is:

if me.Dirty = True then
me.Dirty = false
end if
I also find me.refresh to be a pain, especially when working over slow links
as we do at points. Is there any other similar methods that don't require the
redownload of the whole recordset ?
Click to expand...

Well, as always, you should NEVER load up a form with more then the one
record that it needs. So, if the user wants to edit a invoice number, ask
the user first, and then load the form to that ONE record. It makes no sense
at all to load up a form attached to whole table of invoices without
restricting it to the record that the user needs. You risk a lot of extra
network traffic by loading up a form without any regards to what record that
the user needs. We don't download all names into a instant teller machine
and THEN ask the user what account number to work on.

In fact, a form without restrictions starts loading down some records, and
those records are a real waste since we have NOT EVEN yet asked the user
what record we want! Remember, at this point we have not done any useful
work for the user, but have started transmitting records!. It does not make
sense to do this.

Also, if you restrict your form to the one record, then you can continue to
use me.refresh in your code that you use now without really a performance
problem.

Anyway, give the me.Dirty a try, as it only writes out the current record.

I should also mention that the above advice also applies when NOT using sql
server. If you have 5 users sharing a mdb file, and you use the where clause
to open a form, then the form will load as fast when the table has 1000
records, or 500,000. ONLY the ONE record will be sent down the network wire
if you use the "where" clause (this applies to both sql server, and also
just a mdb file share on a network).

At the end of the day, this means for both jet file shares and sql server
appcltions you want to try and first ask the user what they need to edit,
and THEN load the form to that ONE reocrd.

Also, for some ideas on prompting the user BEFORE the form loads, take a
look at the following screen shots:

http://www.attcanada.net/~kallal.msn/Search/index.html

and

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm
 
Back
Top