Allow zero length as default 1

[ottman2]

Several times a week, I import a different text delimited table to process in Access, but it always takes me two steps because the text field parameter "Allow zero length" defaults to "No". I have to import once with errors, then change every field's parameter to "Yes", then delete all records, then import again into the existing table. I'm using MS Access 2000. Is they a way to change the default parameter value? Or is there a way to change all field's value quickly without having to click on each field.

 

[RoyVidar]

Couldn't you just keep the table to the next import, or are they to differnt?

This thread thread702-869822 discusses the topic. If you enter the helpfiles on the DefaultValue property (DAO), there's an example I think would work.

Roy-Vidar

 

[ottman2]

I looked at the thread listed, but I believe that solution is for exiting tables. I do import different tables on the fly. I'm hoping there is some object table I can munipulate quickly or change the default when it imports. I don't understand why Microsoft would make a text field default to NO, how can you expect every field to have such restrictions as not allowing zero length. I would expect about 1 or 2 fields in a table to impose that type of restriction, but most of them should be freeform.

 

[RoyVidar]

Gee - did I answer the wrong question (think this problem is only in 2000, in xp it defaults to yes)

Meant the AllowZerolength property - and yes, I believe it's for an existing table, but with some automation, you could try first importing, then run the below on the table, delete the contents and import again.

[tt]dim tdf as dao.tabledef
dim db as dao.database
dim fl as dao.field
set db=currentdb
set tdf=db.tabledefs!yourtablename
for each fl in tdf.fields
if fl.type=dbtext then
fl.allowzerolength=false
end if
next fl
set fl=nothing
set tdf=nothing
set db=nothing[/tt]

If this is a large problem, perhaps look into doing the whole process by VBA, reading the file, determine the number of columns/datatypes, create the table and import line by line?

Roy-Vidar

 

[ottman2]

That worked, I now have the function created, and all I do is pass the table name. It will save me a lot of hand labor, thanks.