SQL errors are generated when there is an error in the SQL statements used in the recordset.Open method.
The following table shows the types of SQL errors you may encounter.
Constant | Value | Description | Explanation |
DB_E_CANTCONVERTVALUE | 80040E07 | Cannot convert type, “constant.” | A literal value in the command text cannot be converted to the type of the associated column for reasons other than data overflow. The error string should contain the offending constant. |
DB_E_DATAOVERFLOW | 80040E57 | Constant value “constant” overflowed. | A literal value in the command text could not be stored in the type specified by the associated column. |
DB_E_ERRORSINCOMMAND | 80040E14 | Incorrect syntax near “token.” | The command text contained one or more errors— typically a syntax error or unexpected keyword. The error string should contain the unexpected token. |
E_OUTOFMEMORY | 8007000E | Out of memory. | Out of memory. |
DB_E_NOTABLE | 80040E37 | Table “table” does not exist. | The specified table does not exist. The error string should contain the table name. |
DB_E_BADCOLUMNID | 80040E11 | Field “field” does not exist. | A specified column did not exist. The error string should contain the erroneous field name. |
DB_E_DUPLICATETABLEID | 80040E3F | Table “table” already exists. | The specified table already exists in the current data source. |
DB_E_DUPLICATEINDEXID | 80040E34 | Index “index” already exists. | The specified index already exists in the current data source object. |
DB_E_JOINNEEDINDEX | 80040E40 | Inner joins require that at least one of the tables is indexed on the joined terms near ‘%1!s’. | Multi-table inner joins require that at least one of the tables is indexed on the joined terms. |
DB_E_NOINDEX | 80040E35 | Index “index” does not exist. | The specified index does not exist in the current data source or does not apply to the specified table. |
DB_E_DUPLICATECOLUMNID | 80040E3E | Field “field” already exists. | A field name is identical in two or more elements. |
DB_E_NOCOMMAND | 80040E0C | N/A | No command is set. |
DB_E_BADBOOKMARK | 80040E0E | N/A | Invalid bookmark. |
DB_E_DELETEDROW | 80040E23 | N/A | Row is deleted. |
DB_E_CANTFETCHBACKWARDS | 80040E24 | N/A | A forward only cursor cannot read backwards. |
DB_E_FIELDDIFFERENT | 80040E41 | Invalid field comparison near “%1!s.” | Two fields of different type were compared. |
DB_E_FIELDMAXEXCEED | 80040E42 | Exceeded maximum columns in table near “%1!s.” | An attempt was made to use more than the maximum number of columns per table. |
Note The error values in the table are hexadecimal and can be shown in your code by using Hex(Err.Number).
The following code example sets the constants for the SQL errors.
Const E_OUTOFMEMORY = &H8007000E
Const DB_E_CANTCONVERTVALUE = &H80040E07
Const DB_E_DATAOVERFLOW = &H80040E57
Const DB_E_ERRORSINCOMMAND = &H80040E14
Const DB_E_NOTABLE = &H80040E37
Const DB_E_BADCOLUMNID = &H80040E11
Const DB_E_DUPLICATETABLEID = &H80040E3F
Const DB_E_DUPLICATEINDEXID = &H80040E34
Const DB_E_NOINDEX = &H80040E35
Const DB_E_DUPLICATECOLUMNID = &H80040E3E
Const DB_E_NOCOMMAND = 80040E0C
Const DB_E_BADBOOKMARK = 80040E0E
Const DB_E_DELETEDROW = 80040E23
Const DB_E_CANTFETCHBACKWARDS = 80040E24
Const DB_E_FIELDDIFFERENT = 80040E41
Const DB_E_FIELDMAXEXCEED = 80040E42