Microsoft OLE DB Provider for ODBC Drivers error '80004005' HELP!!!!! 1

[Beren1h]

I'm trying to help build a database driven website.

We have an Access database that we can connect to and read data from, but we get the 'Operation must use an updateable query' error message when we try to write data to the database.

I've been to MSDN support and the folks who have the server tell me that the Internet Guest account has write permission to the folder the database is in. We are also using an DSN-less connection so I don't think that any of the DSN ReadOnly issues are the problem. We are also testing with a single table database so there is no referential integrity issues here.

But we still get the same error!

Does anyone know what could be causing this?!?!

This project will die a quick death if I can't get this straight...

Thanks in advance...

 

[link9]

Need to see the applicable code involved with this issue to pinpoint the problem.


paul

 

[Beren1h]

Below is the code. This code works fine when I test it on Brinkster, but it returns the 'updateable query' error when we run it on our company's intranet server which makes me think this is some kind of server permission issue that is preventing us from writing to the database


<%
Dim strReqA
Dim strAdd
Dim strSQL
Dim RS

strReqA = Request.Form(&quot;T1&quot

strAdd = &quot;INSERT INTO [Table1] (city) values ('&quot; & strReqA & &quot;')&quot;

strSQL = &quot;SELECT [city] FROM [Table1]&quot;
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;\beren1h\db\City.mdb&quot

Set RS = Server.CreateObject(&quot;ADODB.recordset&quot
RS.Open strSQL, Conn

Conn.Execute(strAdd)

Do Until RS.EOF
Response.Write (RS(&quot;city&quot & &quot;<BR>&quot
RS.MoveNext
Loop

RS.close
Conn.Close

Set RS = Nothing
Set Conn = Nothing
%>

 

[KnightCoder]

i have got the same problem.

i can select fine. But when it come to inserting a new record

it gives me

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

but the SQL is a simple INSERT INTO TABLE query


HELP!

 

[KnightCoder]

I THINK I MAY HAVE FOUND THE ANSWER

http://www.macromedia.com/support/ultradev/ts/documents/80004005update.htm

Good Luck

 

[cmmrfrds]

It looks like you are opening a non-updateable query.

Check the 3rd and 4th options on the open, since they control the update ability of the query.

By using defaults is what you get in the first example, but you want second example.
rs.Open Sql1, connString, adOpenForwardOnly, adLockReadOnly

rs.Open Sql1, connString, adOpenStatic, adLockOptimistic

 

[Beren1h]

Thanks, cmmrfrds and KnightCoder

cmmrfrd, I'll give that a shot, but the above listed code works fine on Brinkster without the extra options specified.

Is there something with the server set-up (which I know nothing about) that would prevent or allow the code without the additional options to work?

 

[cmmrfrds]

This doesn't appear to be a problem in IIS. The error appears to becoming from the ODBC driver. If it was me, I would get rid of ODBC altogether since you don't need it with ADO. ADO already has all the libraries you need to make the connection and you will remove a layer of drivers. MDAC, which contains all the ADO libraries is built into the Windows 2000 Operating System. It is actually quite easy to build an ADO connection string if you use the Microsoft wizard. Take the OLEDB connection string and replace the connection string your are using with ODBC.

Easiest way to create the string is with the wizard. Steps to use the wizard.
1. create an empty notepad text file and save it.
2. rename the notepad file and put an extention of udl on the file.
3. double click on the renamed file and it will start the wizard.
4. follow the steps to create a connection to an Access MDB. You will see many other OLEDB providers besides Access, like sql server, oracle, etc........
5. get out of the wizard.
6. open the file with the udl extention with notepad. use the all files extention option to open the file.
7. you will see the connection string.
8. copy the connection string and put it in your program in place of the ODBC connection string. you will no longer be going through ODBC at this point.

 

[link9]

Ask the folks in charge of your server to give &quot;Everyone&quot; full permissions on the directory in question just for testing purposes.

My guess is that that will clear up the issue. If so, then your script is going in under some other account besides the IUSR_machinename.

Try giving &quot;System&quot; or &quot;Network&quot; permissions on the directory, or some other account, until you nail down what account your logging under.

If they are unwilling to help you out there, then check out the Security log on the server. If it's a permissions issue, then there will be an entry where access was denied when ODBC tried to do its work. It should give you the account that tried to change the file.

If you don't see an entry there, then turn on auditing for that directory, where all activity is written to the log file, and inspect.

One of these options should clear the problem up for you. IUSR_machinename isn't always the account that scripts try to do their work under, so sometimes, you just have to troubleshoot using methods like the ones I've described to track down your culprit.

good luck!
paul

 

[Beren1h]

cmmrfrds, I changed the DSN-less to an OLEDB connection, specifically I changed this:

Conn.Open &quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;\beren1h\db\City.mdb&quot


to this:

Conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;\beren1h\db\City.mdb&quot

but am still getting the same error.

 

[cmmrfrds]

Do you have Microsoft Access security on your database? Have you checked the Access accounts permissions? What account are you connecting to the access mdb with? I don't see an account in the connection string. Try using the 'admin' account when you login.

Have you opened the city.mdb in Microsoft Access with the same account you are using in ASP, then opened the table and tried to update or insert data? This should confirm or eliminate any problem on the Access side.

 

[Beren1h]

cmmrfrds,

We can open the database and manaully add data to the tables. This database has no security so (I think) that means we are using the Admin account by default.

I think that is what link9 above was suggesting about the accounts. I don't know anything about NT or IIS but it sounds like the server is processing the ASP and trying to write to the database under an account that doesn't have permission to do this. When we logon to the server our account has write permission so we can manaully write to the database.

I passed link9 suggestions on to the server guy...no word yet..

 

[cmmrfrds]

The Access account is part of the connection string under user id =. So, what you put in the connection string is what will be used to read/write the database, otherwise it will default to the admin account. The server isn't going to fool with the account you pass in the connection string. If you are still using the ODBC driver maybe account information was added when the data source was built.

Since you can read/write to the database from Access then there is not a permission problem on the server directory.

When you built the ADO connection string did you press the test button? If it tested okay, then hard code the string without the Server.MapPath.

I take it you are opening the recordset with one of the updateable parameters like adOpenStatic. You cannot update a readonly dataset which is the default.

 

[Beren1h]

Just had to post this and give link9 a star.

We got the server folks to do exactly what he said and gave &quot;everyone&quot; full permissions. That fixed the problem so now they just have to figure out what account needs to be adjusted.

Thanks to everyone that helped to bail me out!

 

[Xenobia]

Take out the path information from the OLE DB connection string thus...

Conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;City.mdb&quot

The Server.MapPath method is already giving you the full path information to your DB, so your original connection string actually resolves to...

DRIVE\beren1h\db\bern1h\db\City.mdb ,not the DRIVE\beren1h\db\City.mdb you actually need to connect.