to force commit

S

SIN

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
 
6

'69 Camaro

Hi.
i use in function undo but that table inserted!
Click to expand...

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

Susie DBA [MSFT]

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
 
S

SIN

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
 
6

'69 Camaro

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

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".
Click to expand...

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