Error trying to post info from web form to Access (Win 2000, Office 2000 S

Topic

New Reply

I am trying to set up a DNS-less connection to an Access db presently residing in a fpdb folder on my website. I am running IIS on my server. Here’s the connect settings for opening the db and posting thereto:

This is ASP and the connection *seems* to be working, but the post fails with this message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Now that *seems* to be directing me to some kind of config in the Access db, not the ASP connection… it took me a while to get a connection string that didn’t fail, so I’m hoping I got that part right. But I am not getting much help from the MSKB on this.

The html code that is causing the break is this:

‘Response.Write Query
DBConn.BeginTrans
DBConn.Execute(Query)

A few other things to consider: the html/asp/SQL stuff was originally written to post to SQL Server. I have *not* done any ‘translating’ of SQL to Access and ported all the tables directly from SQL Server into the AccessDB. Furthermore, the db is not password-protected in any way and I went into properties on the db from Win Explorer and changed all permissions for all users to full access (changes, writes, etc.).

Any clues as to what to do next?

I thank you in advance for any help you may give!

Reply | Quote

Replies

If you’re not using a SQL Server back end, then your provider isn’t MSDASQL, it’s Microsoft Jet 4.0.

Reply | Quote

well, according to MSKB article here http://support.microsoft.com/directory/art…B;EN-US;q193332, if you look in the section ‘Setting up a DSN-Less Connection’, there are connect strings for Access, SQL Server and Oracle, all of which specify PROVIDER as MSDASQL and in the DRIVER section of the connect string it is specified which *kind* of SQL source is being called. If I use ASP Express to put together a DSN-Less connect string it does the same thing. According to the KB, MSDASQL is the ‘default OLE DB provider for ADO’, and some people just omit it completely; including it in the string is considered best practice.

And like I said, it seems to be able to open a connection but crashes when it tries to insert records into a table (it says queries must be ‘updateable’, whatever that means). If I substitute MSDASQL with this:

“PROVIDER=Microsoft.Jet.OLEDB.3.51;”

It crashes on connect with error ‘this provider cannot be found’.

So I am thinking the connection might be alright, at least as far as it finding and opening the db, but something is not allowing the data to be written into the db.

Reply | Quote

Sorry, I forgot about the ASP element, but Jet OLEDB 3.51 wouldn’t be the correct engine anyhow for Access 2000, it would be Jet OLEDB 4.0 regardless of the examples you might find. I don’t work with ASP, so I can’t be of any help to you.

Reply | Quote

I’ve also used higher versions of the jet db in the connect string with similar results.

well thanks anyway for your quick response!

Reply | Quote

I have a similar arrangement using Jet 4.0 on a web; unfortunately my workstation hard drive decided to crash yesterday morning so I can’t get at the source at the moment. I will take a look in the morning when I have access to the site through another workstation and see if I can see any problem areas. One thing that does occur to me is that the message is telling you that you don’t have an updateable query. That often means that you are missing a primary key on one of the tables in the query. You might check that possibility – I will respond again tomorrow.

Reply | Quote

Thanks for your help. I have tried a few other things since you responded:
1) Set an item in the table to be updated as a primary key
2) Using IIS, enabled the Access file to be read and written to and also allowed ‘Script Source Access’

Neither of these helped either.

I am now wondering whether the problem is not so much establishing a connection (I think that I have gotten that far) but in verifying the ASP/SQL code is building a QUERY that can INSERT into the access db (i capitalize these words b/cuz they are in the actual code). After all, the ASP/SQL query was written for posting to SQL Server, so perhaps there are enough variants that Access is getting confused and telling me something not altogether accurate.

So — if that’s the case — then I will have to re-write a query/submit in ASP that Access understands. This is all very confusing, as Access understands VB natively and also supports SQL scripting but I don’t know how to get a data element from a webpage to get written into Access, basically — I don’t suppose VB has anything to do with all this, but there is an extensive help file in Access for it…

The queries themselves are not *particulaly* difficult but they are long. Perhaps I need an example of a simple query that Access can understand to help resolve this problem.

Reply | Quote

Sorry for not getting back to you quicker – it’s been an ugly week. But here is the code I used to verify that a test connection was working on a web that uses an Access 2K database.

Hope this is useful in debugging your questions about a test connection – it uses a simple table called events that has about 6 fields. The following ASP code is used to update that table from a submission form:
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

ObjConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=d:websitestest.mdb; " & _
"Persist Security Info=False"
Dim rsEvents
Dim blnNew
Set rsEvents = Server.CreateObject("ADODB.Recordset")
rsEvents.Open "Events", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
If Request.Form("Delete") “” Then ‘ user wishes to delete an item
rsEvents.Filter = “EventID = ” & Request(“EventID”)
If Not rsEvents.EOF Then rsEvents.Delete
Else ‘ user wishes to edit/add an item
If Request(“EventID”) = “” Then
blnNew = True
rsEvents.AddNew
Else
rsEvents.Filter = “EventID = ” & Request(“EventID”)
blnNew = False
End If
rsEvents(“EventDate”) = Request.Form(“EventDate”)
rsEvents(“EventTime”) = Request.Form(“EventTime”)
rsEvents(“EventWhere”) = Request.Form(“EventWhere”)
rsEvents(“Description”) = Request.Form(“Description”)
If blnNew = True Then
rsEvents(“WhoEntered”) = Session(“UserName”)
rsEvents(“WhenEntered”) = Now
Else
rsEvents(“WhoEdited”) = Session(“UserName”)
rsEvents(“WhenEdited”) = Now
End If
End If
rsEvents.Update
rsEvents.Close
Set rsEvents = Nothing

Response.Redirect “viewAllEvents.asp”
%>
Note that this is done using ADO which may be something you haven’t encountered yet. It is possible to create SQL text strings and execute the query from ASP, but I don’t normally do it that way, as you very little data verification control using web pages for capturing input. Good Luck!

Reply | Quote

well that’s a lot of code!

it looks like you are using ADO to create a recordset space and then map data elements from the form to the fields in a recordset “Events” which is then populated with data from the form via Request.Form — and this is NOTHING LIKE the code i am staring at. oy gevalt—

the method the outsourced vendor chose was the execute a SQL Query in ASP and zap it to SQL Server, and I have indeed reason to suspect we have been loosing records, if that’s what you mean by ‘little data verification control using web pages for capturing input. ‘ (You could mean not having control over whether a field is filled in or in the right format, etc.) But I don’t think it is appropriate to re-write the entire code DB just to migrate to Access — I wanted to port it to Access and thought perhaps connectivity was the issue, not the ASP method itself. it’s starting to look like it’s not feasable.

However, I do have other user-input forms that could easily be converted from form-to-email to access db and your codeing samples should help in implementing that.

Well, I thank you very much for your help and advice!

Reply | Quote

Actually, I was referring to the control over filling in fields and whether the data supplied made sense. Which is why we typically want quite a bit of control applied by the ASP logic, and since we are already doing that, we might as well do the ADO stuff.

On the other hand, Access SQL is not that different from SQL. There are a few things like the use of double qoutes (“) instead of single quotes (‘) to denote text, and a few arcane keywords that don’t work the same, but most SQL Server statements will work in Access. You do still need to establish the connection to the database, so some of the ASP code is still appropriate. I think I have one or two examples where I do selects using SQL – I will see if I can locate them and post them separately.

Reply | Quote

This is a sample of code I use to display a list of people and related info – it’s not very elegant, but it does work against an Access 2K database:
<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
Dim strDatabaseType
Dim objConn
Dim rsPeople

adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2
strDatabaseType = "Access"

Set objConn = Server.CreateObject("ADODB.Connection")
ObjConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=d:websitestest.mdb; " & _
"Persist Security Info=False"
Set rsPeople = Server.CreateObject("ADODB.Recordset")

' MySQL = "SELECT TOP 25 [LastName], [FirstName], [MiddleName], [ColoradoHomeTown], [ServiceBranch], [WarAction] FROM [Research]"
MySQL = "SELECT TOP 25 * FROM [Research]"
MySQL = MySQL & " WHERE ((([Research].[LastName] LIKE '" & Request("LastName") & "%')"
If NOT Request("FirstName") = "" Then
MySQL = MySQL & " AND ([Research].[FirstName] LIKE '" & Request("FirstName") & "%')"
End If
If NOT Request("ColoradoHomeTown") = "" Then
MySQL = MySQL & " AND ([Research].[ColoradoHomeTown] LIKE '" & Request("ColoradoHomeTown") & "%')"
End If
If NOT Request("ServiceBranch") = "" Then
MySQL = MySQL & " AND ([Research].[ServiceBranch] LIKE '" & Request("ServiceBranch") & "%')"
End If
If NOT Request("WarAction") = "" Then
MySQL = MySQL & " AND ([Research].[WarAction] LIKE '" & Request("WarAction") & "%')"
End If
MySQL = MySQL & ")) ORDER BY [Research].[LastName], [Research].[FirstName]"
' Response.Write MySQL
rsPeople.Open MySQL, objConn, adOpenForwardOnly, adLockReadOnly

If Not rsPeople.EOF Then ' normal case as there will usually be people research
Response.Write _
"

” & _
“

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
“

”
Do While Not rsPeople.EOF
Response.Write _
“

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
”

” & _
“

”
rsPeople.MoveNext
Loop
Response.Write “

PersonIDClick to Edit/Delete	Last Name	First Name	Middle Name	Colorado Home Town	Service Branch	War/Action
” & _ rsPeople(“PersonID”) & “	” & rsPeople(“LastName”) & “	” & rsPeople(“FirstName”) & “	” & rsPeople(“MiddleName”) & “	” & rsPeople(“ColoradoHomeTown”) & “	” & rsPeople(“ServiceBranch”) & “	” & rsPeople(“WarAction”) & “

”
Else ‘ the odd case where all research has been deleted
Response.Write “

No existing research matches your criteria

”
End If
rsPeople.close
set rsPeople = nothing
%>

This is a case where people enter a set of criteria to limit the number of records returned on the display form – I also restrict it to no more than 25 records just to limit the amount of data. The SQL statement is constructed on the fly in this case and then the command

rsPeople.Open MySQL, objConn, adOpenForwardOnly, adLockReadOnly

actually executes the SQL statement. Since this is a SELECT statement it works a little differently than the INSERT statement that you would need to save a new record. But the syntax for INSERTS is virtually identical to that in SQL Server. One of the significant problems is to see if errors are encountered in the save process, and web browser aren’t much help here. Hope this helps.

Reply | Quote