Re: ADO Add Column

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On 10 Aug, 21:47, "David W. Fenton" <XXXuse...@xxxxxxxxxxxxxxxxxxx>
wrote:
Question to A2K7 users:

Does the new version of DAO (with the different name) correct those
deficiencies, i.e., providing full DAO creation/manipulation for all
ACCDB data types?

While the support for data type synonyms in ACEDAO SQL DDL is
improved, it's still not up to the rich ANSI-92 Query Mode SQL DDL
syntax as used by the OLE DB provider (e.g. via ADO). For example:

CurrentDb.Execute "CREATE TABLE test81 (text_col CHAR(2));"
CurrentDb.Execute "CREATE TABLE test82 (text_col CHAR(2) WITH
COMPRESSION);"
CurrentDb.Execute "CREATE TABLE test82 (text_col CHAR(2),
CHECK(text_col LIKE '[0-9][0-9]'));"

Only the first statement will execute without error. No such problems
with ADO:

CurrentProject.Connection.Execute "CREATE TABLE test92 (text_col
CHAR(2) WITH COMPRESSION);"
CurrentProject.Connection.Execute "CREATE TABLE test93 (text_col
CHAR(4), CHECK(text_col LIKE '[0-9][0-9]'));"

Mostly, opt for DAO, and only use ADO for the data types and
operations that Microsoft willfully chose to leave out of DAO3.6
and put into ADO instead.

Good advice :) Now we're agreed that there remains a need and
legitimate reason for users to be using ADO and SQL-92 Query Mode
syntax, one should consider this when writing SQL that will be stored
in database objects (tables, Queries, VIEWs, PROCEDURES, etc), with
particular attention to wildcard characters. A quick example: consider
a validation rule to ensure the first letter of a column value is
always 'F' (case-insensitive):

[field_name] LIKE 'F*'

Using ADO, one could INSERT the literal value 'F*', not the designer's
intention. This is because the ANSI-92 Query Mode wildcard character
equivalent to '*' is '%'. In the past I've suggested coding for both
e.g.

([field_name] LIKE 'F*' OR [field_name] LIKE 'F%')
AND [field_name] <> 'F*'
AND [field_name] <> 'F%'

However, I've recently be made aware of the ALIKE operator which
allows ANSI SQL-92 wildcard characters to be used and honoured
regardless of Query Mode e.g.

[field_name] ALIKE 'F%'

Jamie.

--


.

Relevant Pages

  • ADO Vs ODBC Data types and formatting.
    ... I am replacing a VB5 application that handles database communication ... directly through ODBC API calls with a VB5 application that uses ADO ... I am having some issues with mapping the data types returned by ODBC ...
    (microsoft.public.sqlserver.odbc)
  • ADO Vs ODBC Data types and formatting
    ... I am replacing a VB5 application that handles database communication ... directly through ODBC API calls with a VB5 application that uses ADO ... I am having some issues with mapping the data types returned by ODBC ...
    (microsoft.public.vb.database)
  • The data types varchar and text are incompatible in the equal to operator
    ... TClientDatasets with String params set equal to ... ApplicationServer to ADO: ... "The data types varchar and text are incompatible in the equal to operator" ... Are there some parameter in ADO Connection String to supress this error? ...
    (borland.public.delphi.database.ado)
  • Re: ADO Add Column
    ... considerations about ADO versus DOA and where JET ... Mostly, opt for DAO, and only use ADO for the data types and ... operations that Microsoft willfully chose to leave out of DAO 3.6 ... ACCDB data types? ...
    (microsoft.public.access.formscoding)
  • Re: Like operatore problem
    ... use ALIKE in either DAO (ANSI-89 Query Mode) and ADO ... (ANSI-92 Query Mode) ...
    (microsoft.public.access.modulesdaovba)