error updating access database using where on number column

This is on server 2003, access 2000 database, but the version of access
doesn't seem to matter.
At first I thought it was a general problem updating, but it turns out that
this works:

update auth_table set auth_level = 'administration' where auth_level =
'administration'

and this doesn't:

update auth_table set auth_level = 'administration' where id = 1

the table structure is:

id => AutoNumber
emailaddress => Text
password_check => Text
auth_level => Text
full_name => Text

The error is not terribly informative:
Microsoft JET Database Engine error '80040e14'

Internal OLE Automation error

The name of the AutoNumber column doesn't seem to matter, and I created a
Number type column as a test and it gives the same error.

In the meantime I am switching to using the emailaddress column as my key
since it is also unique, but I have other needs for using the autonumber as
the key for updates in other applications that are also not working.

I am able to delete using the autonumber column just fine, as in:

delete from auth_table where id = 1
inserts also work just fine.

Any ideas?

Sean

RE: error updating access database using where on number column

Duh, forgot to mention this is asp -- of course it works fine in Access ---

"ohdotoh" wrote:

> This is on server 2003, access 2000 database, but the version of access
> doesn't seem to matter.
> At first I thought it was a general problem updating, but it turns out that
> this works:
>
> update auth_table set auth_level = 'administration' where auth_level =
> 'administration'
>
> and this doesn't:
>
> update auth_table set auth_level = 'administration' where id = 1
>
> the table structure is:
>
> id => AutoNumber
> emailaddress => Text
> password_check => Text
> auth_level => Text
> full_name => Text
>
> The error is not terribly informative:
> Microsoft JET Database Engine error '80040e14'
>
> Internal OLE Automation error
>
> The name of the AutoNumber column doesn't seem to matter, and I created a
> Number type column as a test and it gives the same error.
>
> In the meantime I am switching to using the emailaddress column as my key
> since it is also unique, but I have other needs for using the autonumber as
> the key for updates in other applications that are also not working.
>
> I am able to delete using the autonumber column just fine, as in:
>
> delete from auth_table where id = 1
> inserts also work just fine.
>
> Any ideas?
>
> Sean
>
>

RE: error updating access database using where on number column

Here I am replying to my own problem again, hoping that when some other
hapless geek has the same problem I had, he'll be able to find this message
in the GoogleVerse, and spend less than two weeks searching for an answer
only to find it was a directory permissions problem.

A brief synopsis:

Internal Ole Automation Error and occasionally Microsoft JET Database Engine
error '80040e14' in asp's on iis 6 on win server 2k3

Couldn't update when selecting by a non-text field, but I could insert and
delete. Date fields caused IIS to go off into LaLa and never come back. I not
only had to restart IIS, I had to use sysinternal's process explorer to track
down and kill the helper process that had the db open.

After waiting over a week for someone to respond here, and searching google,
and giving up using anything but text and memo fields in my db file so I
could keep working.....

From some Google bait I found on a hardly related error, I decided to check
perms on program files/common files/ado and windows/system32
------- and --------
sure enough, giving IUSR_MACHINENAME read and execute permissions on those
folders fixed the problem. Is this a known problem with any recent update to
win2k3? Is there some reason that a more informative and accurate error
couldn't be returned, other than that it would require planning before coding
the DLL?


Until next month, when I will undoubtedly encounter yet another stupid
problem....

Sean

Re: error updating access database using where on number column

ohdotoh wrote:
> Here I am replying to my own problem again, hoping that when some
> other hapless geek has the same problem I had, he'll be able to find
> this message in the GoogleVerse, and spend less than two weeks
> searching for an answer only to find it was a directory permissions
> problem.
>

Sorry, but i totally missed this post.

> sure enough, giving IUSR_MACHINENAME read and execute permissions on
> those folders fixed the problem. Is this a known problem with any
> recent update to win2k3? Is there some reason that a more informative
> and accurate error couldn't be returned, other than that it would
> require planning before coding the DLL?
>
This is a feature of Jet databases. In order to use a database concurrently
with other users, all users of the database must have read/write permissions
for the folder containing the database. The reason for this is that the
first user to open the database needs to create a locking control file,
databasename.ldb. Subsequent users must be able to edit this file. And the
last user to close the database must be able to delete this file. Watch the
folder containing your database as it is used. You should be able to see the
creation and deletion of the ldb file as the database is used.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: error updating access database using where on number column

Thanks for the effort, and please excuse my crankiness, but if you read the
message again, you will see I did not have a permissions problem on the
database file, or the folder it is in. From the portion you clipped out:

"From some Google bait I found on a hardly related error, I decided to check
perms on program files/common files/ado and windows/system32"

I am well aware that the folder/db file need to be writable for locking. I
was not aware that I would have to grant read and execute on oleaut32.dll,
and I would swear that though there has been a long peaceful pause while
working with jsp and php, that when I was last working with access files on
that same server I did not need to mess with Common Files or System32 in
order to be able to --- again read the original problem:

I couldn't

update some_table set some_text_column = 'something dumb' where
some_number_type_column = a freaking number ----

BUT I could

update the_same_table set the_same_column = 'something just as dumb' where
some_text_type_column = matching text

I could
insert into that table and delete from that table where
some_number_type_column = some number......

If I tried to insert into a date field IIS would hang. If I changed the
field to text I could insert.

The solution was not to set permissions for the folder, they were correct.
The solution was to set permissions for Common Files/ADO and
WINDOWS/System32 to allow IUSR to read and execute.

Again, is this a side effect of a recent security fix, like ADODB.STREAM
maybe? What are the odds anyone here would know the answer?

Again, pardon my crankiness, this is my third monday this week and it's only
tuesday.

SEan



"Bob Barrows [MVP]" wrote:

> ohdotoh wrote:
> > Here I am replying to my own problem again, hoping that when some
> > other hapless geek has the same problem I had, he'll be able to find
> > this message in the GoogleVerse, and spend less than two weeks
> > searching for an answer only to find it was a directory permissions
> > problem.
> >
>
> Sorry, but i totally missed this post.
>
> > sure enough, giving IUSR_MACHINENAME read and execute permissions on
> > those folders fixed the problem. Is this a known problem with any
> > recent update to win2k3? Is there some reason that a more informative
> > and accurate error couldn't be returned, other than that it would
> > require planning before coding the DLL?
> >
> This is a feature of Jet databases. In order to use a database concurrently
> with other users, all users of the database must have read/write permissions
> for the folder containing the database. The reason for this is that the
> first user to open the database needs to create a locking control file,
> databasename.ldb. Subsequent users must be able to edit this file. And the
> last user to close the database must be able to delete this file. Watch the
> folder containing your database as it is used. You should be able to see the
> creation and deletion of the ldb file as the database is used.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: error updating access database using where on number column

My mistake. I was eagar to help given that I had missed your post the first
time. I've never seen this problem so I cannot give you an answer.

From what I understand, Everyone should have Execute permissions on these
files. I really don't know why your IUSR account lost those permissions.

ohdotoh wrote:
> Thanks for the effort, and please excuse my crankiness, but if you
> read the message again, you will see I did not have a permissions
> problem on the database file, or the folder it is in. From the
> portion you clipped out:
>
> "From some Google bait I found on a hardly related error, I decided
> to check perms on program files/common files/ado and windows/system32"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.