Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <% Set DBConn=Server.CreateObject("ADODB.Connection")
    DBConn.Open "PROVIDER=MSDASQL;" & _
    "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
    "DBQ=C:InetpubwwwrootfpdbCRCG_Web_Staffing_Data_20 00.mdb"
    %>

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

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

    well thanks anyway for your quick response!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.
    <%
    Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
    adOpenForwardOnly = 0
    adLockReadOnly = 1
    adCmdTable = 2

    Dim objConn, objRS
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRS = Server.CreateObject("ADODB.Recordset")

    Dim strDatabaseType
    strDatabaseType = "Access"

    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=d:websitestest.mdb; " & _
    "Persist Security Info=False"
    Response.Write "Opened Database Successfully"
    objRS.Open "Events", objConn, AdOpenForwardOnly, adLockReadOnly, adCmdTable

    While not objRS.eof
    Response.Write objRS("EventID") & ""
    objRS.movenext
    Wend

    objRS.Close
    objConn.Close
    set objRS = nothing
    set objConn=nothing

    %>
    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!
    Wendell

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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!

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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.
    Wendell

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error trying to post info from web form to Access (Win 2000, Office 20

    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 _
    "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _
    "<TR>" & _
    " <TH>PersonID<FONT SIZE=""-1"">Click to Edit/Delete</FONT></TH>" & _
    " <TH>Last Name</TH>" & _
    " <TH>First Name</TH>" & _
    " <TH>Middle Name</TH>" & _
    " <TH>Colorado Home Town</TH>" & _
    " <TH>Service Branch</TH>" & _
    " <TH>War/Action</TH>" & _
    "</TR>"
    Do While Not rsPeople.EOF
    Response.Write _
    "<TR ALIGN=CENTER>" & _
    " <TD><A HREF=""AddEditDeleteResearch.asp?Action=Edit&Perso n=" & rsPeople("PersonID") & """>" & _
    rsPeople("PersonID") & "</A></TD>" & _
    " <TD>" & rsPeople("LastName") & "</TD>" & _
    " <TD>" & rsPeople("FirstName") & "</TD>" & _
    " <TD>" & rsPeople("MiddleName") & "</TD>" & _
    " <TD>" & rsPeople("ColoradoHomeTown") & "</TD>" & _
    " <TD>" & rsPeople("ServiceBranch") & "</TD>" & _
    " <TD>" & rsPeople("WarAction") & "</TD>" & _
    "</TR>"
    rsPeople.MoveNext
    Loop
    Response.Write "</TABLE>"
    Else ' the odd case where all research has been deleted
    Response.Write "<CENTER><H2>No existing research matches your criteria</H2></CENTER>"
    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •