access 2003 execute after edit in same transaction causes error

[Noam]

I have an old program that was developed on Access 97 using DAO.
It worked fine until I converted it to Access 2003.
I made all the proper adjustments and references. I even replaced all
declarations to include the "DAO" prefix.
I'm experiencing very strange errors.
I'm declaring a public variable as workspace (dao.dbengine.workspaces(0))
and another one as database (currentdb).
1. While editing a table inside a transaction and then executing an sql
statement on the same table (while inside the transaction) I got the error
"Could not update; currently locked" err# 3112
executing the same code without the transaction worked fine.
**The program includes massive code, therefore converting it to ADO is
expensive, troublesome and not an option.
2. When executing the AUTOEXEC macro directly everything works fine.
When executing the autoexec after touching the code (even though I didn't
change anything for exp: I commented a line and then uncommented it without
saving) I get the following error:
"The database has been placed in a state by user *My Username*on
machine..." err# 3734

 

[Robert Morley]

I don't have Access 2003, so I can only guess, but here are my thoughts on
the issue:

First, CurrentDB() isn't *always* in Workspaces(0)...99.99% of the time, but
not always. Try declaring your database as
<YourWorkspaceVariable>.Databases(0) and see if that alleviates any of your
problems.

As for the locking issue, try the same thing "by-hand" without the SQL
statement...maybe Access 2003 is trying to use a second connection in some
fashion and is coming across the transaction and seeing it as locked.

Finally, for the locked database issue, the only thing I can think of is
that you're opening multiple copies of the database, either manually or
programatically. That would cause that kind of error.



Good luck,
Rob

 

[Noam]

Robert Thnx for your reply,
I tried dbengine.workspaces(0).datbases(0) and got the error "Item not found
in this collection".

I managed to solve this error by changing the "default user" and "default
password" of the dbengine to <admin> username and password (This solved the
err mentioned above and the db locking I mentioned before in my first thread).

As I wrote in my first thread my mdb includes massive code so rewriting it
to use "By hand" update (using "edit" and "update") is impossible.
Furthermore this locking occurs also in other statements like:"DELETE or
"INSERT INTO".

I forgot to mention that every thing works fine on my developing PC.
I'm experiencing this err on a Windows 2003 server and the users are working
through terminal server.
I didn't find any mentioning regarding DAO or Access 2003 and TS over the web.
Office is installed correctly for working in terminal environment.
OS and all other programs are fully updated.
Personally I don't think it has something to do with TS.
I have a feeling it has something to do with permissions on the active
directory or in the Access security (MDW).

I found one thread about this issue which was solved by downgrading to
Access 2000. I don’t think my client will agree to downgrade.

Any Help will be appreciated.

Thnx Noam

I don't have Access 2003, so I can only guess, but here are my thoughts on
the issue:

First, CurrentDB() isn't *always* in Workspaces(0)...99.99% of the time, but
not always. Try declaring your database as
<YourWorkspaceVariable>.Databases(0) and see if that alleviates any of your
problems.

As for the locking issue, try the same thing "by-hand" without the SQL
statement...maybe Access 2003 is trying to use a second connection in some
fashion and is coming across the transaction and seeing it as locked.

Finally, for the locked database issue, the only thing I can think of is
that you're opening multiple copies of the database, either manually or
programatically. That would cause that kind of error.



Good luck,
Rob

 

[david epsom dot com dot au]

Yes, that is the way Access 2003 behaves.

You will have to rewrite your transactions to work
within the new limitations.

Starting to edit code will move the database to exclusive
mode, which will prevent some normal operations. You will
sometimes have to close your database after making changes
in order to unlock it.

These limitations exist in all 2000+ versions. The problem
with transactions is something to do with Jet 4 (and I think
only recent versions of Jet 4). The problem with exclusive
mode was introduced with Access 2000, and never fixed.

(david)