I've had a look around at trying to define
Allow Zero Length through an OleDbCommand but it doesn't seem to be an accessible property (I'll come to this in a second).
Firstly I tried using
ADOX.Catalog as a quick solution:
Dim connstring = String.Format("{0} Data Source={1};", provider, fullPath)
Dim cat As New ADOX.Catalog
cat.ActiveConnection = connstring
cat.Tables("SECTIONS").Columns("LINK_ID").Properties( _
"Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("SECTIONS").Columns("LINK_NAME").Properties( _
"Jet OLEDB:Allow Zero Length").Value = True
cat.Tables("SECTIONS").Columns("SECT_ID").Properties( _
"Jet OLEDB:Allow Zero Length").Value = False
...
However there appears to be a known
issue when setting
ADOX.Catalog.ActiveConnection. When I did this I was thrown an error on this line.
I thus tried to script the schema definition I was given with
DBWScript. As the default is Allow Zero Length I used the script in an OleDbCommand to alter columns I know needed changing as such:
Dim cmd As New OleDbCommand
Dim query = _
"ALTER TABLE [SECTIONS] DENY ZERO LENGTH [SECT_ID]; " & _
"ALTER TABLE [SECTIONS] DENY ZERO LENGTH [SECT_NAME]; " & _
...
However this gave me a syntax error when I ran it on the OleDbConnection.
According to
this post the Allow/Deny Zero Length property is not accessible through Jet sql.
At this point I've given up. I can't replicate the schema exactly through .NET without causing COM issues. It'll throw up some warnings in the software I'm handing the data over to but it's got to the point where I don't think I can make any progress on it.
Thanks again for the info.