User Defined Data Type

[doodle]

Greetings. SQL 2k5, Access 97 (I know)

If I create User Defined data types in SQL, and relink my SQL tables
in the database, will Access still be able to read those data types?

For instance: for State I currently have Char(2) in SQL. If I open the
linked SQL table in access, it shows Access reading the data type as
Text.

I would like to create a user defined data type tState as Char(2). If
I do that, will access still bring in as 'Text'? I am especially
concerned about this working with Access 97.

thanks for your help,

-doodle

 

[storrboy]

Char(2) would be Text limited to 2 characters isn't it?
If so a Text field in Access contains a 'FieldSize' property which
does the same.

 

[Allen Browne]

For a comparison of the names used in Access (JET) with the field names you
use in DDL, see:
Field type reference - names and values for DDL, DAO, and ADOX
at:
http://allenbrowne.com/ser-49.html

The ADOX names are probably closest to the SQL Server names.

 

[Jamie Collins]

Char(2) would be Text limited to 2 characters isn't it?

Not quite: it is text *fixed* at two characters i.e. no more than two,
no fewer than two.

Jet has the NCHAR [Unicode] data type plus the SQL DDL syntax to
match. Jet also has the CHAR [ASCII] data type but not the syntax: the
Jet keyword 'CHAR' is a synonym for 'NCHAR', so you have to use the
CHAR(x) WITH COMPRESSION syntax. None of this syntax is available via
the Access user interface, which even in its 2007 guise still lags
behind the enhancements in the engine

Jamie.

--

 

[Jamie Collins]

Greetings. SQL 2k5, Access 97 (I know)

If I create User Defined data types in SQL, and relink my SQL tables
in the database, will Access still be able to read those data types?

For instance: for State I currently have Char(2) in SQL. If I open the
linked SQL table in access, it shows Access reading the data type as
Text.

I would like to create a user defined data type tState as Char(2). If
I do that, will access still bring in as 'Text'? I am especially
concerned about this working with Access 97.

If you are working across SQL platforms, you require portability and
the one of the best ways to ensure portability is to stick to the data
types found in the SQL-92 standard. That said, although CHAR(x) is
SQL-92 and both SQL Server and Jet 4.0 (plus ACE - Access 2007engine)
have it, Access97 does not. Try using VARCHAR(2), perhaps with a CHECK
constraint such as

LEN(my_col) = 2

Jamie.

--