Workspaces and BeginTrans

[Steven]

I have 3 questions:

1) Begin Tran , CommitTran, and Rollback do not work using a basic append
query by using the DoCmd.OpenQuery?

2) What happens if I am using wrkDefault.BeginTrans and begin a
dbs.Execute that takes 15 seconds to process and in that time the electricty
shuts off? If in the example below the elctricity shuts off while doing the
dbs.Execute then the code would never get to the code wrkDefault.CommitTrans
dbForceOSFlush; what would happen when the user goes back to that procedure.
Can I be assured that no data was updated and I am not left with inconsistent
data. Does anything need "flushed"?? Rollbacked?? or something like that?

'***Begin Example***

Dim dbPath, dbName As String
Dim dbs As Database
Dim wrkDefault As Workspace
Set wrkDefault = DBEngine.Workspaces(0)

dbPath = "C:\"
dbName = "db2.mdb"

dbName = dbPath + dbName

wrkDefault.BeginTrans

Set dbs = OpenDatabase(dbName)

dbs.Execute " INSERT INTO Table1 " _
& "SELECT Name " _
& "FROM [Table3];"

If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans dbForceOSFlush 'dbForceOsFlush actual value
is 1
Else
wrkDefault.Rollback
End If

dbs.Close

'***End Example***


3) Lets say I am working in database U:\Systems\MainSys.dbs and want to
INSERT from TableOne in MainSys.mdb into TableOneBU in
U:\Shared\tablebackup.mdb. Can that be done? Note: I do not want to have a
link of the table from U:\Shared\tablebackup.mdb into the MainSys.mdb Were
can I get instructions on that? I am thinking it works using the WorkSpaces.

That's a lot of questions. Thank you for your help.

Steven

 

[Albert D. Kallal]

I have 3 questions:

1) Begin Tran , CommitTran, and Rollback do not work using a basic append
query by using the DoCmd.OpenQuery?

That is correct. You can't specify what instance of the database in the
above. what happens if you have 5 databases open at the same time?

Those docmd's that open forms, run quires etc are really the same as the
user
interface. NOTE THAT TRANSACTIONS DO NOT WORK for the user interface. So,
updates
made by forms, quires, or by the user ARE NOT part of transactions.

2) What happens if I am using wrkDefault.BeginTrans and begin a
dbs.Execute that takes 15 seconds to process and in that time the
electricty
shuts off? If in the example below the elctricity shuts off while doing
the
dbs.Execute then the code would never get to the code
wrkDefault.CommitTrans
dbForceOSFlush; what would happen when the user goes back to that
procedure.
Can I be assured that no data was updated and I am not left with
inconsistent
data. Does anything need "flushed"?? Rollbacked?? or something like that?

Well, I suppose if during the code you are running you might be ok, but if
you are
doing a large update, the large portion, and the dangerous time is DURING
THE
COMMITTRANS. In other words, the actually update of the data tables does NOT
start until the commitrans. So, sure, if during your update code running the
power
goes out, you might be ok. However, you have just as much (if not more) of a
chance that during the commit trans when the data starts to flow to the
actual tables (from the temp files that the trans action creates), and if
power goes out during that time? well, you got a mess. So, not worried much
about the update that never happens, but when the actual update starts, and
the power goes out..you likely damaged the main file, and you have NO way to
be sure HOW FAR the update managed to go. So, flushed, rollback? ...no, you
go to the previous know good backup, which is likely the day before...

3) Lets say I am working in database U:\Systems\MainSys.dbs and want to
INSERT from TableOne in MainSys.mdb into TableOneBU in
U:\Shared\tablebackup.mdb. Can that be done? Note: I do not want to
have a
link of the table from U:\Shared\tablebackup.mdb into the MainSys.mdb
Were
can I get instructions on that? I am thinking it works using the
WorkSpaces.

From the help:

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

I will dispense with all of the "type" stuff, but you can use excel, or even
dbase files with the "in" clause.

SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";

A path name needs to usually be included, hence:

select * from book1 in "C:\Documents and Settings\Lawrence\My
Documents\db5.mdb"

So, you can specify the destination database by using the "in" clause. I
never tried it for transactions...so, you have to test it...