ADO Add Column

[Bill]

ADO doesn't like "Boolean" for a data type. Can anyone
tell me what it should be. Also, better still, where can I
find the HELP text for this method. It seems that I've
tried everything I can thing of for a search expression
but haven't found it yet.......SIGH!

strDDL = "ALTER TABLE Registry ADD Column SecurityCL Boolean" 'Field:
Security Clearance
conBackend.Execute strDDL, dbFailOnError

Thanks,
Bill

 

[RoyVidar]

ADO doesn't like "Boolean" for a data type. Can anyone
tell me what it should be. Also, better still, where can I
find the HELP text for this method. It seems that I've
tried everything I can thing of for a search expression
but haven't found it yet.......SIGH!

strDDL = "ALTER TABLE Registry ADD Column SecurityCL Boolean"
'Field: Security Clearance
conBackend.Execute strDDL, dbFailOnError

Thanks,
Bill

Try with Bit.

Alse, here is an artilce with some information, which also links to
a couple more
http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

but since you're using dbFailOnError, you're using DAO, not ADO,
and this can only be executed on ADO/OLE DB (according to the docs,
but it executes with DAO on my 2007 setup)

Also, you should probably have a file on your computer named something
like JETSQL40.chm, find that, and you should have help on Jet SQL.

 

[Bill]

Thanks Roy, "Bit" did the trick.

I had fumbled around and found JETSQL40 Help file and
was somewhat amazed that the discussion on ALTER
TABLE didn't bother to list the various keywords used
to denote data types.

One of these days, I'll finally get clear about underlying
considerations about ADO versus DOA and where JET
applies and where it doesn't.

Thanks again,
Bill

 

[David W. Fenton]

One of these days, I'll finally get clear about underlying
considerations about ADO versus DOA and where JET
applies and where it doesn't.

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
and put into ADO instead.

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?

 

[Jamie Collins]

I had fumbled around and found JETSQL40 Help file and
was somewhat amazed that the discussion on ALTER
TABLE didn't bother to list the various keywords used
to denote data types.

The following MSDN article list most (if not all) of the data type
synonyms:

Microsoft Access 2000 Technical Articles
Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

FWIW the SQL section of the Access Help contains a number of
inaccuracies and is of particularly poor quality IMO. As for
omissions... well, the spec/Help for the engine has *always* been
lacking on content and detail.

Jamie.

--

 

[Jamie Collins]

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.

--