Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Question:
I have noticed several people have been receiving this error as well as myself. I'm using an Access 2000 DB & I've tried it on SQL 7.0.
I receive this error on BOTH types of DBs:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Any resolution to this so far?
Answer:
Identity works with Access 2000 ONLY if you use the "Jet OLEDB Provider 4.0"!
So define your ODBC connection not with "Microsoft Access Driver" but with "Jet OLEDB Provider 4.0". So your connection string should look like:
"Provider=Microsoft.Jet.OLEDB.4.0;Data source="C":\Backup\OBS\Development\Db\obs2k.mdb;Persist Security info="False""
If you use a DSN-Less connection. Otherwise its in the ODBC Settings.
Read more about this in the following ASPWATCH article
Comments
Access 2000 errors
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
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
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
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
You must me logged in to write a comment.