msaccess-vcs-addin
msaccess-vcs-addin copied to clipboard
joyfullservice
Build error: You must enter a value in the field
I'm using addin version 4.0.34, Office 365 64-bit, Win10 64-bit.
I did a full export of my .mdb. Then took the .src folder to another machine and ran a Build As....
The build finished but with two errors. There are two tables but they are nearly identical, here's the log lines for one of them:
-------------------------------------
Importing table data...
[...]
PDDscreenBits.txt
-------------------------------------
ERROR: Build error in: C:\Users\hugh\My Drive\3di\3di-repo\frontend\tables\PDDscreenBits.txt
Error 3314: You must enter a value in the 'PDDscreenBits.QsDk' field. Source: modImportExport.Build
-------------------------------------
Something about the QsDk column is causing a problem. Here's the exported tbldefs/PDDscreenBits.sql:
CREATE TABLE [PDDscreenBits] (
[Upr] VARCHAR (1),
[Part] UNSIGNED BYTE,
[Lwr] VARCHAR (1),
[Qs] VARCHAR (255),
[Score] SINGLE,
[Qcount] UNSIGNED BYTE,
[QsScored] UNSIGNED BYTE,
[QsToDo] VARCHAR (255),
[QsDk] VARCHAR (255),
[QsNotApp] VARCHAR (255),
CONSTRAINT [PrimaryKey] PRIMARY KEY ([Upr], [Part], [Lwr])
)
And here's the section for QsDk in tbldefs/PDDscreenBits.xml:
<xsd:element name="QsDk" minOccurs="1" od:jetType="text" od:sqlSType="nvarchar" od:nonNullable="yes">
<xsd:annotation>
<xsd:appinfo>
<od:fieldProperty name="ColumnWidth" type="3" value="-1"></od:fieldProperty>
<od:fieldProperty name="ColumnOrder" type="3" value="0"></od:fieldProperty>
<od:fieldProperty name="ColumnHidden" type="1" value="0"></od:fieldProperty>
<od:fieldProperty name="Required" type="1" value="1"></od:fieldProperty>
<od:fieldProperty name="AllowZeroLength" type="1" value="1"></od:fieldProperty>
<od:fieldProperty name="DisplayControl" type="3" value="109"></od:fieldProperty>
<od:fieldProperty name="IMEMode" type="2" value="0"></od:fieldProperty>
<od:fieldProperty name="IMESentenceMode" type="2" value="3"></od:fieldProperty>
<od:fieldProperty name="UnicodeCompression" type="1" value="1"></od:fieldProperty>
<od:fieldProperty name="TextAlign" type="2" value="0"></od:fieldProperty>
<od:fieldProperty name="AggregateType" type="4" value="-1"></od:fieldProperty>
<od:fieldProperty name="ResultType" type="2" value="0"></od:fieldProperty>
<od:fieldProperty name="CurrencyLCID" type="4" value="0"></od:fieldProperty>
</xsd:appinfo>
</xsd:annotation>
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"></xsd:maxLength>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
I'm new to Access, is Required in the XML equivalent to NOT NULL in the SQL? What should I do to fix up my rebuild? In the source database before export, QsDk contained an empty string so I think that should be the provided value on build.
I think you are on the right track here... You are requiring a value for the QsDk field, and the actual value in the table is an empty string. This is perfectly valid, but the problem is that a tab-delimited text file has a hard time representing the difference between a null and an empty string. In the source code, you can see the relevant section in clsDbTableData around line 237
Since an empty value usually means null when loaded into a database, I went with what I thought would work in the majority of use cases.
The good news is that this should be pretty easy to solve by switching the export format to XML for the table data in PDDscreenBits. The XML format may be slightly harder to read in version control, but the compatibility with these types of edge cases is much better. I would personally recommend using the XML format for table data wherever possible to ensure the greatest reliability in data transformation.
I see. Maybe the code could default to null, unless the field is Required, in which case it could interpret an empty field as the empty string instead? I suppose this would be a change in behaviour, but presumably it wouldn't affect anyone negatively because if they are in this situation they are already experiencing an error. If you think this would be an improvement I'm happy to try and put together a PR.
In any case, it sounds like I will switch those files to XML to get things working for now. I prefer the TSV format because I use a truly great tool (Beyond Compare) for diffing TSV files and I don't think XML is so easily diffed.
Do you want to give this a try?
' Data line
varLine = Split(strLine, vbTab)
rst.AddNew
' Loop through fields
For intCol = 0 To UBound(varHeader)
' Check to see if field exists in the table
If dCols.Exists(varHeader(intCol)) Then
' Check for empty string or null.
If varLine(intCol) = vbNullString Then
' The field could have a default value, but the imported
' data may still be a null value.
With rst.Fields(varHeader(intCol))
If Not IsNull(.Value) Then
' Could possibly hit a problem with the storage of
' zero length strings instead of nulls. Since we can't
' really differentiate between these in a TDF file,
' we will do some additional probing...
If .DefaultValue = vbNullString Then
' No default value for this field
If .AllowZeroLength Then
' Use empty string
.Value = vbNullString
Else
' Empty string not allowed. Use null instead
.Value = Null
End If
Else
' A default value is specified for this column.
' Don't change the value of this field.
End If
End If
End With
Else
This should test to see if the field has a default value, and if not, it will use an empty string if a zero length string is allowed. It seems like this may do a better job of handling cases like yours with perhaps a minor performance penalty. (I am not as concerned with performance for this function since XML is the primary data export format.)
Thanks for that, I've finally got around to trying it out. I still get the same error unfortunately.
Looking at this line
If Not IsNull(.Value) Then
Should that Not be removed? I'm not confident I understand this code, but I think it's saying "if the field has no default value, then we need to make an educated guess about what value to use". In which case the current test looks inverted: IsNull(.Value) implies that no default value was set?
Having said that, if I remove the Not then I get a different error importing a different table.
Good catch. You are correct, the Not should be removed. I tested this on a table with a required field that allows a zero length string, and the value of the field is Null until it is set to vbNullString.
The other error about duplicate values would be unrelated. Feel free to create a separate issue for that if the error persists. For troubleshooting purposes, the description is pretty helpful. I would review your data, relationships and indexes (primary key) to see why it might be hitting a duplicate value.
Thinking about this again, I'm not sure how .DefaultValue is relevant. It describes the value a field will receive when creating a new record (Microsoft docs), however on import we aren't creating a new record from scratch but restoring some existing ones which were written to disk. The filling-in of .Value from .DefaultValue must have occurred previously to create the record before it was exported.
The problem is that the on-disk format for Text/Memo columns does not distinguish between a null and an empty string (perhaps that could be remedied, but it's a separate ticket).
When we do encounter the empty string in the TSV file, there are four possible scenarios depending on the Required and AllowZeroLength properties of the destination column, when it is a Text/Memo. In fact, two of these scenarios should be impossible.
- Required=true, AllowZeroLength=true. Since null is not allowed, that leaves us "" as the only permissible value.
- Required=false, AllowZeroLength=true. Impossible to determine which of "" or null was intended. I suggest using null.
- Required=true, AllowZeroLength=false. Cannot arise because the export must have contained at least one character. Bad data file.
- Required=false, AllowZeroLength=false. Again, cannot arise because the export must have contained at least one character. Bad data file.
For columns of all other types which aren't Text/Memo, I assume that the empty string ⟺ null value. If for those other types you find the empty string in the TSV, and the column is Required=true, that's an error in the file.
Does that make sense?
Yes, that does make sense. That was pretty much the same conclusion that I came to as well after reviewing this a little further today. The revised code does exactly what you describe in points 1 and 2. Bad data in the file (or changed table requirements) isn't something that I specifically address in the add-in, but it would probably throw an error during the import.
The revised code looks like this:
' Data line
varLine = Split(strLine, vbTab)
rst.AddNew
' Loop through fields
For intCol = 0 To UBound(varHeader)
' Check to see if field exists in the table
If dCols.Exists(varHeader(intCol)) Then
' Check for empty string or null.
If varLine(intCol) = vbNullString Then
With rst.Fields(varHeader(intCol))
If IsNull(.Value) Then
' Could possibly hit a problem with the storage of
' zero length strings instead of nulls. Since we can't
' really differentiate between these in a TDF file,
' we will do some additional probing...
If .AllowZeroLength And .Required Then
' Use empty string instead of null value
.Value = vbNullString
End If
End If
End With
I think that's not quite right. For a start it doesn't check the column type and I believe this only applies for Text and Memo fields. I'm finding it a bit hard to see the changes via comments so I've gone ahead and made a PR: https://github.com/joyfullservice/msaccess-vcs-addin/pull/562. The PR isn't ready yet. For a start it's not tested! And I'm not sure what to do in the error case.
Just wanted to suggest something. Looking at the clsDbTableData, it appears we do the export/import manually via ExportTableDataAsTDF and ImportTableDataTDF.
Since we are not relying on some tools to import/export the contents, we are free to define our own placeholder, which would then eliminate the need to "guess", and then we wouldn't have to consider the Required nor AllowZeroLength (discounting the scenario where the data file is bad, obviously).
For example, we can choose the following placeholders:
- Use a naked
vbNullCharand map it to theNull. That is easy to test and check for without breaking compatibility with any text data that embedesvbNullCharsomewhere within the string. - Use some placeholder such as
<NULL>and map it toNull. It would be better if it's also naked to avoid problems with any text data that has literal "<NULL>" within its contents. - Something else.
I believe that common CSV tools should not care if they encounter a naked <NULL> while other text data are quoted and will show them in the column. For example, this CSV content:
TestA, TestB
1,"Hi"
2,<NULL>
3,"Bye"
loads correctly in Excel, so a naked <NULL> seems to works well enough and can be handled in the ExportTableDataAsTDF and ImportTableDataTDF routines.
Good thoughts, @bclothier!
I would be a little nervous with vbNullChar because of how Windows handles null-terminated strings. It seems like this could risk breaking in the future, even if it works now. The other issue is that it is a little less obvious for a new user staring at the content in Notepad.
I think I lean towards the literal <NULL> value as a placeholder. It seems highly unlikely that this would collide with an actual value that someone was trying to use. It is very obvious that we are dealing with a null value if the content is viewed in a text editor. That seems like a great solution from my perspective.
Just to flesh this out a bit more...
Given the CSV:
TestA, TestB
1,"Hi"
2,<NULL>
3,"Bye"
4,"<NULL>"
5,""
6,
7,"Dude!"
We would end up with this in Excel:
Excel is one of the worst option to edit a CSV file because of its propensity to mangle data like this, flattening both "<NULL>" and <NULL> into the same value <NULL>, same with "" and lack of value. If we proceed with the naked placeholder <NULL>, it'll need to be documented somewhere for users on what their options are for modifying the CSV file outside Access.