Re: Deleting a record
- From: "Paul Scott" <paul.scott@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 04 Mar 2008 15:23:32 -0000
Paulo,
On Tue, 04 Mar 2008 13:39:56 -0000, Paulo Hoffmann <p@xxxxx> wrote:
I have an Access database that is handled only with SQL commands through ADO queries.
When I delete a record from the database, I need to read the table again in order to update a combobox. But just after the delete (SQL) command, it seems that the table is not updated yet and I get the deleted item on the combobox again.
If I wait a while and then read the table, the deleted record is gone and everthing is OK. But I do not want to wait to read the table.
Is there a way to refresh the Access table using a query?
We did originally have problems with two co-operating processes where although ProcessA had written a set of rows (in a transaction), ProcessB couldn't see any of them (?or only some of them?) until some short time later.
My code now contains two bodges which between them seem to have fixed this problem...
1)
private
fJetEngine : OleVariant ;
procedure Synchronise_MsAccess ;
begin
// First time through we need to create the OLE object
if VarIsEmpty ( fJetEngine ) then begin
fJetEngine := CreateOleObject ( 'JRO.JetEngine' ) ;
end ;
// "Ensure that my read cache is consistent with the database on disk" ***
fJetEngine.RefreshCache ( fSasAdoConnection.Internal_ConnectionObject ) ;
end ; // Synchronise_MsAccess
*** Note the wording on that comment! - There does not (did not?) appear to be any way to force the updates to be immediately flushed TO disk, rather this "RefreshCache" forces subsequent accesses to actually read FROM the disk tables
2) Although while looking through Google Groups to see /why/ I had this next line of code in my app, I found the following note...
There is at least one method of forcing Jet to immediately flush all updates
to disk in ADO. This method involves use of a transaction and a property
exposed by the Microsoft Jet OLE DB provider called "JET OLEDB:Transaction
Commit Mode". With this property set to 1 (default is 0), any changes made within a transaction are
flushed to disk when the transaction is committed. For example:
'open a connection
cn.Open
cn.Properties("Jet OLEDB:Transaction Commit Mode") = 1
'open your recordset
cn.BeginTrans
'do some work
rs.Update
cn.CommitTrans ' updates flushed to disk on commit
When this connection is closed and reopened, the "Jet OLEDB:Transaction
Commit Mode" property is reset to its default value of 0.
In Delphi-ADO-speak, this can be effected by...
(AdoConnection.ConnectionObject as _Connection).Properties.Item ['Jet OLEDB:Transaction Commit Mode'].Value := 1
- and I think this only needs to be called once after the connection is opened (not once per transaction)
While BOTH bodges seem to be /sufficient/ to allow my processes to co-operate, I haven't done any testing to see if both are actually /necessary/
HTH
--
Paul Scott
Information Management Systems
Macclesfield, UK.
.
- Follow-Ups:
- Re: Deleting a record
- From: Brian Bushay TeamB
- Re: Deleting a record
- References:
- Deleting a record
- From: Paulo Hoffmann
- Deleting a record
- Prev by Date: Re: TADOStoredProc problem
- Next by Date: Re: Deleting a record
- Previous by thread: Deleting a record
- Next by thread: Re: Deleting a record
- Index(es):
Relevant Pages
|
