dbOpenTable : disabling Transactions?

[Patrice Dargenton]

Hello, is it possible to disable transactions for RecordSet opened using
dbOpenTable in order to make a great number of updates? I have an Access
2000 database whose size explodes systematically (when the data change
indeed during update). For the moment, I found in the documentation that it
was only possible to disable the transactions for action queries
(UseTransaction = false).
Thank you.

 

[[MVP] S. Clark]

The UseTransaction property applies to Action Queries. Your question asks
about dbOpenTable, which means that you are opening a recordset. A
recordset cannot contain an Action Query. An Action query can only be
executed.

For transactions to be used during the update of a recordset, you would have
to explicitly start the Tx.

So, I'm confused. If there is something that I'm missing, please fill in
the blanks.


One more confusing tidbit is this blurb from the Help File:
"When the UseTransaction property is set to No, the only way to roll back an
entire transaction is to use the ADO RollbackTrans or DAO Rollback method in
Visual Basic."

This doesn't make sense, unless executing the query from code, but I still
don't think it applies in your case, as you want to do the update from a
recordset.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Patrice Dargenton]

The UseTransaction property applies to Action Queries. Your question asks
about dbOpenTable, which means that you are opening a recordset. A
recordset cannot contain an Action Query. An Action query can only be
executed.

For transactions to be used during the update of a recordset, you would have
to explicitly start the Tx.

So, I'm confused. If there is something that I'm missing, please fill in
the blanks.

When I start to edit the recordset, the data is buffered. This is a good
thing for security (completeness of the whole operation), but this is a bad
thing for quickness and for the size of the database, witch increase from 20
Mb to 500 Mb after updating 10000 records. For action query, it is possible
to disable the transaction, but I can't use an action query in my case
because the updating procedure is too complex for a query. Then, my question
was : is it possible to disable transaction for a recordset opened using
dbOpenTable ? (I use dbOpenTable for the speedness of the seek search
method).

Thanks.

 

[david epsom dot com dot au]

Update actions are 'Implicit' transactions. You can try
to see what happens if you make Implicit transactions
asynchronous. And you should try using a table lock
(dbDenyRead).

But I don't think there is any way to 'turn off Implicit
transactions' other than using Explicit transactions or
using Access 2.0 /Jet 2.x.

(david)

 

[[MVP] S. Clark]

Although you may have a perfectly valid reason for needing to seek in the
data. I'm not a big fan of the technique. Why do you need to seek? What
records are in the recordset that you don't need, thus requiring the seek?
If there are any unused records, then that would be a waste of resources.

When I need to find data in code, I prefer to use a WHERE clause instead of
seeking within the recordset.
sql = "Select id, name from customer WHERE state = 'OH'"
Then open the recordset based on the sql string.

As far as the query being too complex, you may need to break it down to more
bite-size pieces. Use two or three queries, instead of one.

I think the Action Query is still your best bet, unfortunately you're going
to have to work to get it to work. (But that happens in Access very often.)

 

[Patrice Dargenton]

Although you may have a perfectly valid reason for needing to seek in the
data. I'm not a big fan of the technique. Why do you need to seek? What
records are in the recordset that you don't need, thus requiring the seek?
If there are any unused records, then that would be a waste of resources.

There are no unused records. I just need to update some records at a time, I
don't need to update all records every times.

When I need to find data in code, I prefer to use a WHERE clause instead of
seeking within the recordset.
sql = "Select id, name from customer WHERE state = 'OH'"
Then open the recordset based on the sql string.

As far as the query being too complex, you may need to break it down to more
bite-size pieces. Use two or three queries, instead of one.

Yes I know. But breaking a complex query in many smaller queries always
works for select query, but this is not true for update queries : if You
have a left join, records may be not editable. Then, the only way to update
records is to seek them one by one and update one record at a time.

I think the Action Query is still your best bet, unfortunately you're going
to have to work to get it to work. (But that happens in Access very

often.)

I would do it if this could work, but I'm afraid that I can't make this sort
of update query.
Thanks,

 

[Patrice Dargenton]

Update actions are 'Implicit' transactions. You can try
to see what happens if you make Implicit transactions
asynchronous. And you should try using a table lock
(dbDenyRead).

Or open database exclusively.

But I don't think there is any way to 'turn off Implicit
transactions' other than using Explicit transactions or
using Access 2.0 /Jet 2.x.

I can't believe newer versions of Access miss this sort of useful
functionality (I can use a linked table to an Access 2.0 database? but this
may not be a serious solution)

 

[Patrice Dargenton]

Or open database exclusively.

It would seem that this is a good solution : The database size does not grow
up too much in this last case.