to force commit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SIN - 28 Mar 2007 22:36 GMT

hi,
what is the way to aforce commit in that situation?
strSQL = "insert into invoice (Invoice_Num,Supplier_Id)
strSQL = strSQL & " values (" & Me.Invoice_Num & " , " & Me.Supplier_Id & ")
dbs.execute strSQL, dbFailOnError

i use in function undo but that table inserted!

thanks

Reply to this Message

'69 Camaro - 28 Mar 2007 23:56 GMT

Hi.

> i use in function undo but that table inserted!

One cannot undo an action query executed with the Execute method in VBA
code. The only way to "undo" it is to roll it back, but it must be embedded
within a transaction first. Unfortunately, one cannot see the effects of
the transaction until it's been committed, whereupon one cannot roll it back
to undo the damage.

In other words, one needs to understand the repercussions of the action
query before executing it in Access.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

> hi,
> what is the way to aforce commit in that situation?
[quoted text clipped - 6 lines]
>
> thanks

Reply to this Message

Susie DBA [MSFT] - 29 Mar 2007 01:19 GMT

YET ANOTHER REASON WE ALL SHOULD BE USING ACCESS DATA PROJECTS

On Mar 28, 3:56 pm, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAM> wrote:

> Hi.
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Reply to this Message

Susie DBA [MSFT] - 29 Mar 2007 01:22 GMT

and with SQL Server, technically-- you CAN see the results before it's
committed

and you've got a real apparatus; real transactions

if Access had real transactions then banks would be using it for every
single operation

On Mar 28, 3:56 pm, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAM> wrote:

> Hi.
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Reply to this Message

SIN - 29 Mar 2007 08:30 GMT

thanks,
can you advise me what can i do in situation that i have to update and
insert very dainty data.

even i do alote of test, theory i can be in situation that i get "on error".

sin

> Hi.
>
[quoted text clipped - 28 lines]
> >
> > thanks

Reply to this Message

'69 Camaro - 29 Mar 2007 10:27 GMT

Hi.

> can you advise me what can i do in situation that i have to update and
> insert very dainty data.

Create a SELECT query with the same criteria as your UPDATE query, so that
you can see which records will be updated. Add a column (or columns) in the
SELECT query that will have the calculated values you need in the UPDATE
query, so that you can see what changes will be made. Then change the
SELECT query to an UPDATE query and execute it.

For example:

SELECT StartDate, Date()
FROM MyTable
WHERE (ISNULL(StartDate));

and then change it to:

UPDATE MyTable
SET StartDate = Date()
WHERE (ISNULL(StartDate));

The SELECT query already showed you which records have a NULL StartDate, and
it showed you which date will be inserted into those records, so you know
whether or not the UPDATE query needs more tweaking.

> even i do alote of test, theory i can be in situation that i get "on
> error".

Make backups before you do something that may make you wish you hadn't.
That way, you can always restore from backup and try again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

> thanks,
> can you advise me what can i do in situation that i have to update and
[quoted text clipped - 40 lines]
>> >
>> > thanks

Reply to this Message