I am developing a small website and am having difficulty connecting to an Access database in an administration page where I want password protection. I have used the following code to access the page, which is done successfully. It provides True if found or False if RS.EOF is reached

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~
Set Con = Server.CreateObject( "ADODB.Connection" )
  Con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"
    strSQL = "Select * FROM Account " &_
    "WHERE Name='" & username & "' " &_
    "AND Password='" & password & "'"

SET RS = Con.Execute( strSQL )

If NOT RS.EOF Then

do something like expose connections to other pages

End If
Set RS = nothing
Con.Close
Set Con = nothing
End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

however when it comes to editing the passwords used an error is generated saying that I require an updateable query?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If newpassword = confnewpassword Then
   If (thisusername = username) and (password = pword) Then ' where username & pword is passed from the previous page

   Set Con = Server.CreateObject( "ADODB.Connection" )
   Con.Open = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")

' Con.Open = OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb"))
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"

      sqlString = "UPDATE Account " &_
          "SET password='" & newpassword & "'" &_
          " WHERE name='" & thisusername & "'"

'Response.Write "SqlString=" & sqlString & "<br>"
      Con.Execute sqlString
      Response.Write "Your changes have been successfully writen"
    End If
  Else%>
        <p><b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;
        You have not confirmed your new password Correctly</b><p>
<%End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have approached my server provider who has given me a different connection string used on this server:
OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=path to database.mdb;");

This doesn't work on my computer, however I have added it to my .asp file and uploaded it onto the server, there I have received error messages which reduced the code to:

OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=path to database.mdb")

Now the error message received is very unfourthcoming and reads: HTTP 500 - Internal server error Internet Explorer

Has anyone seen this form of OLEDB connection string before and does anyone know how to make it work?

Hi Edward,

Could this be a security error? That is, is your Web server allowed to write to the folder where the database resides?

To find out, try this:

First, change the settings for your browser so you see the real error message:

http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=264

Next, change your Connection string back to how it was as it looked pretty good to me. For more details on Access / Jet connections, look here:

http://www.able-consulting.com/MDAC/...orMicrosoftJet

Then run the page. If you get an error like 80004005, take a look here:
http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=263 and here: http://Imar.Spaanjaars.Com/QuickDocID.aspx?QUICKDOC=290

If all this doesn't help, can you post the error you get?


HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar, I have adjusted my browser as suggested to remove 'Friendly Error' reports from the server and have now got the message below:

New Error message:
Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/12cooperdrive/revisePassword.asp, line 38

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have further adjusted my .asp file editing out the Execute command and reading instead the response.write sqlString which would have been processed, to me it looks OK so the problem is most likely to be as you suggest a server security issue.
I will take this up with my server provider for comment and report back the outcome.

revised code:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~
If not confnewpassword = " " Then
 If newpassword = confnewpassword Then
   If (thisusername = username) and (password = pword) Then

   Set Con = Server.CreateObject( "ADODB.Connection" )
      Con.Open = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb")
' Con.Open "aqmsDSN"
' Con.Open = OLE-DB("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & server.mappath("/i/impact/data.mdb"))
'Response.Write "Source=" & server.mappath("/i/impact/data.mdb") & "<br>"

      sqlString = "UPDATE Account " &_
                  "SET password='" & newpassword & "'" &_
                  " WHERE name='" & thisusername & "'"

Response.Write "SqlString=" & sqlString & "<br>"
' Con.Execute sqlString
      Response.Write "Your changes have been successfully writen"
    End If
  Else%>
        <p><b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;
        You have not confirmed your new password Correctly</b><p>
<%End If
End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Revised page revisepassword.asp (29th October)
returns: SqlString=UPDATE Account SET password='Richard' WHERE name='Edward Stephen'

Many thanks Imar - you are unique in so far that you are never put off and always come back with a solution.

Edward

PASSOWRD is a reserved keyword. So enclose that within []s This should solve the problem.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Imar & Vijay G, Your solution is correct. I have received a list of Access DB reserved words from my Server Provider giving the same information, I have added the table name to the field name ie. AccountName & AccountPassword which is achieving the same result. It is now working - many thanks to you both.
Cheers!

Edward.