E
Elvis
Hello,
I have a strange problem. I have a import process from excel, but can't use
the built-in load as the fields are scattered all over the sheet. It was
their attempt to use Excel for something its not meant to be
Anyway. I put the entire process - there are several sheets - within a
transaction. and utilise macros to perform the update/append and auditing
queries. I have run into a weird problem. It seems I run into a locking error
in the macro.
the code is pretty identical to the below. Note that I can't seem to
replicate this in Access 2002 - but am using access 2000 until all the client
machines are upgraded later this year.
Workspaces(0).BeginTrans
Set rs = CurrentDb.OpenRecordset("select * from IMPORT")
rs.AddNew
rs(0) = "jim"
rs(1) = "I am not"
rs(2) = "a bricklayer"
rs(3) = "damn it"
rs.Update
rs.AddNew
rs(0) = "jim"
rs(1) = "I am not"
rs(2) = "a bricklayer"
rs(3) = "damn it"
rs.Update
DoCmd.SetWarnings False
DoCmd.RunMacro "macro1"
DoCmd.SetWarnings True
Workspaces(0).CommitTrans
Exit Sub
The macro query in the first instance does not update the IMPORT table..just
uses it to determine what(if any) data needs to be appended to other tables.
So can't see the transaction blocking the table for any reason...it defies
logic...
The macro gives a locking error that the table IMPORT is exclusively locked
by another user or process.
very weird. everything is within the same transaction. Is this a bug in DAO
3.5? I can't find anything on the MS site. I have shut down access, rebooted
and retried and it gives me the same thing.
Any ideas what might be up?
I have a strange problem. I have a import process from excel, but can't use
the built-in load as the fields are scattered all over the sheet. It was
their attempt to use Excel for something its not meant to be
Anyway. I put the entire process - there are several sheets - within a
transaction. and utilise macros to perform the update/append and auditing
queries. I have run into a weird problem. It seems I run into a locking error
in the macro.
the code is pretty identical to the below. Note that I can't seem to
replicate this in Access 2002 - but am using access 2000 until all the client
machines are upgraded later this year.
Workspaces(0).BeginTrans
Set rs = CurrentDb.OpenRecordset("select * from IMPORT")
rs.AddNew
rs(0) = "jim"
rs(1) = "I am not"
rs(2) = "a bricklayer"
rs(3) = "damn it"
rs.Update
rs.AddNew
rs(0) = "jim"
rs(1) = "I am not"
rs(2) = "a bricklayer"
rs(3) = "damn it"
rs.Update
DoCmd.SetWarnings False
DoCmd.RunMacro "macro1"
DoCmd.SetWarnings True
Workspaces(0).CommitTrans
Exit Sub
The macro query in the first instance does not update the IMPORT table..just
uses it to determine what(if any) data needs to be appended to other tables.
So can't see the transaction blocking the table for any reason...it defies
logic...
The macro gives a locking error that the table IMPORT is exclusively locked
by another user or process.
very weird. everything is within the same transaction. Is this a bug in DAO
3.5? I can't find anything on the MS site. I have shut down access, rebooted
and retried and it gives me the same thing.
Any ideas what might be up?