Access 2000 errors

September 3, 2001 by P.R. Newman

I get the same error, and I've got Access 2000 and I'm using "Jet OLEDB Provider 4.0":

Microsoft JET Database Engine error '80040e14'

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Is there something I need to tweak in the Access DB before I can retrieve the IDENTITY?

Maybe I'll just try it with SQL 7.0 instead.

RE: Access 2000 errors

September 3, 2001 by P.R. Newman

After struggling to get this extension to work without success, I finally tried Tom Muck's Insert-RetrieveID extension, part of UltraSuite. It worked like a dream.

According to the documentation, though, Muck's server behavior won't work with SQL Server: it doesn't use @@IDENTITY to retrieve the latest AutoNumber.

With a little tweaking, I was able to get this Microsoft sample to work with Access 2000:

>> Access 2000 errors

November 1, 2001 by charlie charlie

Could you please let me know what did you do with your code to the connection part? I still get the same error message by trying many ways

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/Login/PasswordEdit.asp, line 109

Please help, thanks in advance!

RE: >> Access 2000 errors

November 2, 2001 by P.R. Newman

Try these 2 things:

1) Comment out the "As" keywords after the variables are dimensioned (Visual Basic uses them; VBScript doesn't).

2) Add an include to, and make sure you have that file in your site root.

My altered code looks like this (at the top of the page):

<!--#include file="../"-->
Dim cnDatabase 'As ADODB.Connection
Dim rsNewAutoIncrement 'As ADODB.Recordset
Dim strConn 'As String
Dim strSQL 'As String
Dim strPathToMDB 'As String


Hope that helps.


See all 15 Comments