Error Message trying to connect to Access DB

[Cal Learner]

Just upgraded to WinXP Pro to get IIS (5.1) functionality on my home computer for testing my own web applications. Handles ASP just fine until it gets to a conn.open statement, and then I get the HTTP 500.100 - Internal Server Error message. The detailed error message is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x964 Thread 0xaf0 DBC 0x1064a5c Jet'.
/ofmb/_private/admin-BMN.asp, line 14

I've already upgraded from MDAC 2.7 to 2.8 without any change in the error condition. I've been through the permissions settings, and they appear appropriate. The conn.open statement in the code which is giving the server trouble is:

conn.open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=store1.mdb;" & _
"DefaultDir=c:\Inetpub\wwwroot\ofmb\fpdb\;"
"Uid=Admin;Pwd=;"

This same statement has never before given me any trouble getting to an Access db using the PWS predecessor to IIS. What's IIS want from me that I'm overlooking? Does it require a different connection statement than PWS did/does?
Thanks to anyone who can clear up my confusion.

[Jeff Cochran]

On Sat, 31 Jul 2004 16:59:01 -0700, "Cal Learner" <Cal
[email]Learner@discussions.microsoft.com[/email]> wrote:

>Error Type:
>Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x964 Thread 0xaf0 DBC 0x1064a5c Jet'.
>/ofmb/_private/admin-BMN.asp, line 14

FAQ:

Why do I get database-related 80004005 errors?
[url]http://www.aspfaq.com/show.asp?id=2009[/url]

Jeff

[Walter Munchheimer]

The connection problem is solved. Thanks Jeff. However, now that the db
tables run, the server produces an error message whenever it encounters an
UPDATE SQL trigger. This must surely be related to a permissions issue, but
I've looked everywhere I know of, and everything involved has read/write
privileges. I've been thru aspfaq.com article #2205, taking all of the
recommended steps. Still, every time: "Operation must use an updateable
query."
The SQL statement causing this is:

sql3="UPDATE bmn SET
bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)# WHERE
bmn.order_no="&CurrentOrder&";"

This looks right to me, and I feel quite certain that this statement works
fine on my work box running PWS instead of IIS 5.1. What do you think??

"Jeff Cochran" <jeff.nospam@zina.com> wrote in message
news:410c46bc.917452146@msnews.microsoft.com...

> On Sat, 31 Jul 2004 16:59:01 -0700, "Cal Learner" <Cal
> [email]Learner@discussions.microsoft.com[/email]> wrote:
>

> >Error Type:
> >Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> >[Microsoft][ODBC Microsoft Access Driver]General error Unable to open

registry key 'Temporary (volatile) Jet DSN for process 0x964 Thread 0xaf0
DBC 0x1064a5c Jet'.

> >/ofmb/_private/admin-BMN.asp, line 14

>
> FAQ:
>
> Why do I get database-related 80004005 errors?
> [url]http://www.aspfaq.com/show.asp?id=2009[/url]
>
> Jeff

[Bob Barrows [MVP]]

Walter Munchheimer wrote:

> The connection problem is solved. Thanks Jeff. However, now that the
> db tables run, the server produces an error message whenever it
> encounters an UPDATE SQL trigger.

"trigger"? Access does not have triggers. You must mean "UPDATE SQL
statement"

>This must surely be related to a
> permissions issue, but I've looked everywhere I know of, and
> everything involved has read/write privileges. I've been thru
> aspfaq.com article #2205, taking all of the recommended steps. Still,
> every time: "Operation must use an updateable query."

This is usually due to the lack of a primary key on your table.

> The SQL statement causing this is:
>
> sql3="UPDATE bmn SET
> bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)# WHERE
> bmn.order_no="&CurrentOrder&";"
>
> This looks right to me, and I feel quite certain that this statement
> works fine on my work box running PWS instead of IIS 5.1. What do you
> think??
>

You can't troubleshoot a sql statement without knowing what it is. Put the
following lines of code in your script immediately following the "slq3= ...
" line:
response.write sql3
response.end

It your sql statement is correct, you should be able to open your database
in Access, create a new query in Design View, switch to SQL View, paste in
the statement as it appears in the browser window and run it without
modification (unless wildcards are involved). You will ususally get a more
informative error statement. If that doesn't allow you to solve the problem,
show us the real sql statement instead of the vbscript code that's supposed
to generate it.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Walter Munchheimer]

Good suggestions Bob, but the problem persists. Response.write sql3 returns
the following:

UPDATE buymenow SET
buymenow.order_status='Late',buymenow.conf_date=#8/1/2004 2:55:39 PM# WHERE
buymenow.order_no =385;

which works just fine when pasted into an MS-Access query. It had the
desired effect of writing the 2 values into the db record of order_no 385.
Then, when I comment out the response.write statement and go back to the
conn.execute sql3, the server returns the "operation must use an updateable
query" error msg. Incidentally, the order_no field is the primary key.
Thanks again, Bob. Where to now?

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eebswc$dEHA.1652@TK2MSFTNGP09.phx.gbl...

> Walter Munchheimer wrote:

> > The connection problem is solved. Thanks Jeff. However, now that the
> > db tables run, the server produces an error message whenever it
> > encounters an UPDATE SQL trigger.

>
> "trigger"? Access does not have triggers. You must mean "UPDATE SQL
> statement"
>

> >This must surely be related to a
> > permissions issue, but I've looked everywhere I know of, and
> > everything involved has read/write privileges. I've been thru
> > aspfaq.com article #2205, taking all of the recommended steps. Still,
> > every time: "Operation must use an updateable query."

>
> This is usually due to the lack of a primary key on your table.
>

> > The SQL statement causing this is:
> >
> > sql3="UPDATE bmn SET
> > bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)# WHERE
> > bmn.order_no="&CurrentOrder&";"
> >
> > This looks right to me, and I feel quite certain that this statement
> > works fine on my work box running PWS instead of IIS 5.1. What do you
> > think??
> >

>
> You can't troubleshoot a sql statement without knowing what it is. Put the
> following lines of code in your script immediately following the "slq3=

....

> " line:
> response.write sql3
> response.end
>
> It your sql statement is correct, you should be able to open your database
> in Access, create a new query in Design View, switch to SQL View, paste in
> the statement as it appears in the browser window and run it without
> modification (unless wildcards are involved). You will ususally get a more
> informative error statement. If that doesn't allow you to solve the

problem,

> show us the real sql statement instead of the vbscript code that's

supposed

> to generate it.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows [MVP]]

Are you still using the ODBC connection string? I would switch to the native
Jet OLEDB provider:

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & server.mappath("store1.mdb")

Bob Barrows

Walter Munchheimer wrote:

> Good suggestions Bob, but the problem persists. Response.write sql3
> returns the following:
>
> UPDATE buymenow SET
> buymenow.order_status='Late',buymenow.conf_date=#8/1/2004 2:55:39 PM#
> WHERE buymenow.order_no =385;
>
> which works just fine when pasted into an MS-Access query. It had the
> desired effect of writing the 2 values into the db record of order_no
> 385. Then, when I comment out the response.write statement and go
> back to the conn.execute sql3, the server returns the "operation must
> use an updateable query" error msg. Incidentally, the order_no field
> is the primary key. Thanks again, Bob. Where to now?
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eebswc$dEHA.1652@TK2MSFTNGP09.phx.gbl...

>> Walter Munchheimer wrote:

>>> The connection problem is solved. Thanks Jeff. However, now that the
>>> db tables run, the server produces an error message whenever it
>>> encounters an UPDATE SQL trigger.

>>
>> "trigger"? Access does not have triggers. You must mean "UPDATE SQL
>> statement"
>>

>>> This must surely be related to a
>>> permissions issue, but I've looked everywhere I know of, and
>>> everything involved has read/write privileges. I've been thru
>>> aspfaq.com article #2205, taking all of the recommended steps.
>>> Still, every time: "Operation must use an updateable query."

>>
>> This is usually due to the lack of a primary key on your table.
>>

>>> The SQL statement causing this is:
>>>
>>> sql3="UPDATE bmn SET
>>> bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)# WHERE
>>> bmn.order_no="&CurrentOrder&";"
>>>
>>> This looks right to me, and I feel quite certain that this statement
>>> works fine on my work box running PWS instead of IIS 5.1. What do
>>> you think??
>>>

>>
>> You can't troubleshoot a sql statement without knowing what it is.
>> Put the following lines of code in your script immediately following
>> the "slq3= ... " line:
>> response.write sql3
>> response.end
>>
>> It your sql statement is correct, you should be able to open your
>> database in Access, create a new query in Design View, switch to SQL
>> View, paste in the statement as it appears in the browser window and
>> run it without modification (unless wildcards are involved). You
>> will ususally get a more informative error statement. If that
>> doesn't allow you to solve the problem, show us the real sql
>> statement instead of the vbscript code that's supposed to generate
>> it.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Cal Learner]

Yes, I had already made the switch to the Jet OLEDB provider before this error message. I had to switch in order for the server to even make the conn.open connection work. That part works fine, and SELECT works fine, but wherever the coding involves UPDATE-ing the db, the error message appears. I have also already installed MDAC 2.8, and made certain that the folder with the db file has both read/write privileges for IUSR_machine and that the .mdb file itself is not read only. That's supposed to do it, no?

"Bob Barrows [MVP]" wrote:

> Are you still using the ODBC connection string? I would switch to the native
> Jet OLEDB provider:
>
> conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source= " & server.mappath("store1.mdb")
>
> Bob Barrows
>
> Walter Munchheimer wrote:

> > Good suggestions Bob, but the problem persists. Response.write sql3
> > returns the following:
> >
> > UPDATE buymenow SET
> > buymenow.order_status='Late',buymenow.conf_date=#8/1/2004 2:55:39 PM#
> > WHERE buymenow.order_no =385;
> >
> > which works just fine when pasted into an MS-Access query. It had the
> > desired effect of writing the 2 values into the db record of order_no
> > 385. Then, when I comment out the response.write statement and go
> > back to the conn.execute sql3, the server returns the "operation must
> > use an updateable query" error msg. Incidentally, the order_no field
> > is the primary key. Thanks again, Bob. Where to now?
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eebswc$dEHA.1652@TK2MSFTNGP09.phx.gbl...

> >> Walter Munchheimer wrote:
> >>> The connection problem is solved. Thanks Jeff. However, now that the
> >>> db tables run, the server produces an error message whenever it
> >>> encounters an UPDATE SQL trigger.
> >>
> >> "trigger"? Access does not have triggers. You must mean "UPDATE SQL
> >> statement"
> >>
> >>> This must surely be related to a
> >>> permissions issue, but I've looked everywhere I know of, and
> >>> everything involved has read/write privileges. I've been thru
> >>> aspfaq.com article #2205, taking all of the recommended steps.
> >>> Still, every time: "Operation must use an updateable query."
> >>
> >> This is usually due to the lack of a primary key on your table.
> >>
> >>> The SQL statement causing this is:
> >>>
> >>> sql3="UPDATE bmn SET
> >>> bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)# WHERE
> >>> bmn.order_no="&CurrentOrder&";"
> >>>
> >>> This looks right to me, and I feel quite certain that this statement
> >>> works fine on my work box running PWS instead of IIS 5.1. What do
> >>> you think??
> >>>
> >>
> >> You can't troubleshoot a sql statement without knowing what it is.
> >> Put the following lines of code in your script immediately following
> >> the "slq3= ... " line:
> >> response.write sql3
> >> response.end
> >>
> >> It your sql statement is correct, you should be able to open your
> >> database in Access, create a new query in Design View, switch to SQL
> >> View, paste in the statement as it appears in the browser window and
> >> run it without modification (unless wildcards are involved). You
> >> will ususally get a more informative error statement. If that
> >> doesn't allow you to solve the problem, show us the real sql
> >> statement instead of the vbscript code that's supposed to generate
> >> it.

> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

[Bob Barrows [MVP]]

Yes, that is supposed to do it. We're talking about filesystem permissions,
now, not website settings.

Depending on the Application Protection setting of your website, you may
need to grant the read/write privileges on that folder to the IWAM account
as well as the IUSR account. When the Application Protection is set to
High(Isolated), the IWAM account is the user, not the IUSR account. Also, if
the code is in global.asa, it will be IWAM running it.

This is the aspfaq article that covers this message:
[url]http://www.aspfaq.com/show.asp?id=2062[/url]


Try giving a password to your IUSR account, log in to the machine as IUSR,
and verify that you can create and delete a test text file in the folder
containing your database. When finished, log back in as administrator and
remove the password from the IUSR account.

Bob Barrows

Cal Learner wrote:

> Yes, I had already made the switch to the Jet OLEDB provider before
> this error message. I had to switch in order for the server to even
> make the conn.open connection work. That part works fine, and SELECT
> works fine, but wherever the coding involves UPDATE-ing the db, the
> error message appears. I have also already installed MDAC 2.8, and
> made certain that the folder with the db file has both read/write
> privileges for IUSR_machine and that the .mdb file itself is not read
> only. That's supposed to do it, no?
>
> "Bob Barrows [MVP]" wrote:
>

>> Are you still using the ODBC connection string? I would switch to
>> the native
>> Jet OLEDB provider:
>>
>> conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source= " & server.mappath("store1.mdb")
>>
>> Bob Barrows
>>
>> Walter Munchheimer wrote:

>>> Good suggestions Bob, but the problem persists. Response.write sql3
>>> returns the following:
>>>
>>> UPDATE buymenow SET
>>> buymenow.order_status='Late',buymenow.conf_date=#8/1/2004 2:55:39
>>> PM#
>>> WHERE buymenow.order_no =385;
>>>
>>> which works just fine when pasted into an MS-Access query. It had
>>> the
>>> desired effect of writing the 2 values into the db record of
>>> order_no 385. Then, when I comment out the response.write statement
>>> and go
>>> back to the conn.execute sql3, the server returns the "operation
>>> must
>>> use an updateable query" error msg. Incidentally, the order_no field
>>> is the primary key. Thanks again, Bob. Where to now?
>>>
>>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>>> news:eebswc$dEHA.1652@TK2MSFTNGP09.phx.gbl...
>>>> Walter Munchheimer wrote:
>>>>> The connection problem is solved. Thanks Jeff. However, now that
>>>>> the
>>>>> db tables run, the server produces an error message whenever it
>>>>> encounters an UPDATE SQL trigger.
>>>>
>>>> "trigger"? Access does not have triggers. You must mean "UPDATE SQL
>>>> statement"
>>>>
>>>>> This must surely be related to a
>>>>> permissions issue, but I've looked everywhere I know of, and
>>>>> everything involved has read/write privileges. I've been thru
>>>>> aspfaq.com article #2205, taking all of the recommended steps.
>>>>> Still, every time: "Operation must use an updateable query."
>>>>
>>>> This is usually due to the lack of a primary key on your table.
>>>>
>>>>> The SQL statement causing this is:
>>>>>
>>>>> sql3="UPDATE bmn SET
>>>>> bmn.order_status='LATE',bmn.conf_date=#formatdatet ime(now,0)#
>>>>> WHERE
>>>>> bmn.order_no="&CurrentOrder&";"
>>>>>
>>>>> This looks right to me, and I feel quite certain that this
>>>>> statement
>>>>> works fine on my work box running PWS instead of IIS 5.1. What do
>>>>> you think??
>>>>>
>>>>
>>>> You can't troubleshoot a sql statement without knowing what it is.
>>>> Put the following lines of code in your script immediately
>>>> following
>>>> the "slq3= ... " line:
>>>> response.write sql3
>>>> response.end
>>>>
>>>> It your sql statement is correct, you should be able to open your
>>>> database in Access, create a new query in Design View, switch to
>>>> SQL
>>>> View, paste in the statement as it appears in the browser window
>>>> and
>>>> run it without modification (unless wildcards are involved). You
>>>> will ususally get a more informative error statement. If that
>>>> doesn't allow you to solve the problem, show us the real sql
>>>> statement instead of the vbscript code that's supposed to generate
>>>> it.

>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"