Zero Value Defaults for Access fields

[SimpleSync]

Karl --- tried to reply but the message is refused for some reason ??

Each Excel file has just one row:
Last,First,Initials,Phone,Fax,Street,City,State,Country,Company,Department,etc......

I want to import each of these files into a separate Table in an Access DB.
Once this is done each table is empty, but has column headers in place...in
effect the tables are 'primed'.

Our software package then takes an incoming LDIF file from Active Directory
and does an ODBC update, matching the AD attribute labels with the column
headers in Access.

When I look in the design View, all of the fields in the Access table
default to settings of:
Required: No
Allow Zero Length: No
Indexed: No

Since none are required I would have expected to be able to be able to
handle missing values in some fields from AD, but when I run the update it
fails. Example: In AD the 'Assistant' fields are normally blank and cause
the following error:
[Microsoft][ODBC Microsoft Access Driver] Field 'SQL2.Assistant' cannot be a
zero-length string. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

SQL2 is my table. Assistant is a column header. Most AD records do not have
any value in this field.

I can import each Excel file into an Access table, and then manually change
the setting to: Allow Zero Length: Yes
This works fine, but for testing we may need to build multiple tables, with
as many as 80 columns - and I would really like to avoid having to change
this value for all 80 fields.

Thanks,

Jerry

 

[David Lloyd]

Jerry:

Below is some sample code that will set the AllowZeroLength property of the
fields in a table. This property exists only for text, memo and hyperlink
fields, so you will have to check the Type property of the field before
setting the property value.

Function SetAllowZeroLength()
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")

For Each fld In tdf.Fields
If fld.Type = dbText Then
fld.Properties("AllowZeroLength") = True
End If
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Karl --- tried to reply but the message is refused for some reason ??

Each Excel file has just one row:
Last,First,Initials,Phone,Fax,Street,City,State,Country,Company,Department,etc......

I want to import each of these files into a separate Table in an Access DB.
Once this is done each table is empty, but has column headers in place...in
effect the tables are 'primed'.

Our software package then takes an incoming LDIF file from Active Directory
and does an ODBC update, matching the AD attribute labels with the column
headers in Access.

When I look in the design View, all of the fields in the Access table
default to settings of:
Required: No
Allow Zero Length: No
Indexed: No

Since none are required I would have expected to be able to be able to
handle missing values in some fields from AD, but when I run the update it
fails. Example: In AD the 'Assistant' fields are normally blank and cause
the following error:
[Microsoft][ODBC Microsoft Access Driver] Field 'SQL2.Assistant' cannot be a
zero-length string. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

SQL2 is my table. Assistant is a column header. Most AD records do not have
any value in this field.

I can import each Excel file into an Access table, and then manually change
the setting to: Allow Zero Length: Yes
This works fine, but for testing we may need to build multiple tables, with
as many as 80 columns - and I would really like to avoid having to change
this value for all 80 fields.

Thanks,

Jerry

 

[SimpleSync]

David,
Thank you for taking the time to provide this detailed information. I am
surprised that it takes code, rather than being able to modify defaults
within Access.
Regards,
Jerry

 

[David Lloyd]

Jerry:

I simulated importing a header row from Excel to create a table in Access
(2003) and the AllowZeroLength property defaulted to "Yes." Maybe there is
something in the import process that determines how this property is set in
the resulting table.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


David,
Thank you for taking the time to provide this detailed information. I am
surprised that it takes code, rather than being able to modify defaults
within Access.
Regards,
Jerry

Jerry:

Below is some sample code that will set the AllowZeroLength property of
the
fields in a table. This property exists only for text, memo and hyperlink
fields, so you will have to check the Type property of the field before
setting the property value.

Function SetAllowZeroLength()
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field

Set db = CurrentDb

Set tdf = db.TableDefs("MyTable")

For Each fld In tdf.Fields
If fld.Type = dbText Then
fld.Properties("AllowZeroLength") = True
End If
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Karl --- tried to reply but the message is refused for some reason ??

Each Excel file has just one row:
Last,First,Initials,Phone,Fax,Street,City,State,Country,Company,Department,etc......

I want to import each of these files into a separate Table in an Access
DB.
Once this is done each table is empty, but has column headers in
place...in
effect the tables are 'primed'.

Our software package then takes an incoming LDIF file from Active
Directory
and does an ODBC update, matching the AD attribute labels with the column
headers in Access.

When I look in the design View, all of the fields in the Access table
default to settings of:
Required: No
Allow Zero Length: No
Indexed: No

Since none are required I would have expected to be able to be able to
handle missing values in some fields from AD, but when I run the update it
fails. Example: In AD the 'Assistant' fields are normally blank and cause
the following error:
[Microsoft][ODBC Microsoft Access Driver] Field 'SQL2.Assistant' cannot be
a
zero-length string. (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

SQL2 is my table. Assistant is a column header. Most AD records do not
have
any value in this field.

I can import each Excel file into an Access table, and then manually
change
the setting to: Allow Zero Length: Yes
This works fine, but for testing we may need to build multiple tables,
with
as many as 80 columns - and I would really like to avoid having to change
this value for all 80 fields.

Thanks,

Jerry