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:

http://support.microsoft.com/support/kb/articles/Q232/1/44.ASP

>> 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 adovbs.inc, 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="../adovbs.inc"-->
<%
Dim cnDatabase 'As ADODB.Connection
Dim rsNewAutoIncrement 'As ADODB.Recordset
Dim strConn 'As String
Dim strSQL 'As String
Dim strPathToMDB 'As String

...etc.

Hope that helps.

-Paul

See all 15 Comments