Hi,
I have two different Access 2003 applications, they both have linked
tables to the same SQL Server 2005 database.
Since my applications often have to perform several related inserts,
updates, I prefer using transactions.
After carefully reading Microsoft sites and Google discussions, I
decided to use the Access built-in transaction system using
Workspaces(0).Begintrans, Workspaces(0).Committrans and
Workspaces(0).Rollback.
One of the problems I was immediately confronted with is that I get
ODBC timeouts after inserting in a table and then reading from that
same table (or from a view that has that table linked) within the same
transaction. Again I searched groups and read that with the limited
functionality of Access/Jet/ODBC transactions, one cannot read from a
table after inserting in it or updating it within the same
transaction.
However, my other Access application has no problem with executing the
same code! So now I don't understand a thing. I have two applications
both executing the same code (copied under this text). One
applications hangs on the dlookup, the other doesn't. Can anyone tell
me why this is and what the solution is?
1 Workspaces(0).Begintrans
2 sSql = "INSERT INTO dbo_T_Events (RelatieNr, Invoerhulp) VALUES (" &
iRelatieNr & ", '" & sInvoerhulp & "')"
3 CurrentDb.Execute sSql, dbSeeChanges
4 iNieuweEventNr = DLookup("EventNr", "dbo_T_Events", "InvoerHulp =
'" & sInvoerhulp & "'") ' TIMEOUTS!!!
5 sSql = "INSERT INTO dbo_T_AboVerlengEvents (EventNr, etcetera
etcetera
6 CurrentDb.Execute sSql, dbSeeChanges
7 Workspaces(0).Committrans
|
