field required?

[Bert]

Hi,

I'm working on a disconnected adodb-recordset (that is created from an
Access-Jet-database), and want to know if a field is required.
How can I find that out?

thank you,

 

[Brendan Reynolds]

This isn't something I've done in a 'real world' application, only tested
briefly for this newsgroup response, but it seems to work OK ...

Public Sub TestRequired()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Source = "SELECT * FROM tblSource"
.Open
.ActiveConnection = Nothing
If (.Fields("SourceText").Attributes And adFldIsNullable) = True
Then
MsgBox "Not Required"
Else
MsgBox "Required"
End If
.Close
End With

End Sub

 

[Bert]

Hi,
It seems I can't rely on this method. The results seem to be not correct for
all situations.

I hope there's a more reliable method, but I'm afraid, because I found
something about a bug in the ADO-library about this.

Bert

 

[Brendan Reynolds]

Can you expand on that at all? Under what conditions is this method not
reliable? Where did you find this report of a bug?

Meanwhile, though, can you connect directly to the source of the data, and
test the property of the field there, instead of in the recordset? You could
do that via either DAO or ADOX.

 

[Bert]

Sorry that I didn't explain further.
I found this link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;185823

In my case, I've a number-field (long integer, not required, no index) and a
date-field (not required, no index) and a text-field (not required,
zero-length allowed, no index) that still returns 'required' from you
method.
Here's another link where somebody seems to have the same problem:
http://groups.google.nl/groups?q=je...D:nl&selm=#jdyuuvmBHA.428@tkmsftngp04&rnum=11

btw, I'm using ADO 2.8 (on Win XP Pro / Office XP)

For me there's now not an acceptable other solution. I know ADOX/DAO, but in
my case it's not the way to go. I would check the recordset for this
property so that I could create some automatic validation. Now I've to
'know' this logic also in my code, and not only in the table. Some more
work...

Bert

 

[Brendan Reynolds]

According to the KB article and the reply to the newsgroup question, this
should only be an issue when using the ODBC driver, or one specific version
of the OLEDB provider. If you're using ODBC, can you use OLEDB instead? If
you're already using OLEDB, and your version of Msjetoledb40.dll is version
4.0.4331.4, installing the latest Jet service release may fix it - the
version of Msjetoledb40.dll on my system has a significantly higher build
number, 4.0.8227.0, and I'm not seeing the problem with that version.

 

[Bert]

ok, thank you
but strange enough: I'm using OLE DB, and using version 4.0.8227.0
The database I use is in 2002-format.

And I've still the problem

Bert

 

[Brendan Reynolds]

It seems the file format may be the key. I had previously tested using
Access 2000 file format, but after reading your latest post, I tested the
same code using Access 2002 format, and I can now reproduce the problem. I'm
afraid I don't have any good suggestions for a workaround at this time. I'll
ask around, and post back here if I find anything.

 

[Bert]

Great, thank you!

It seems the file format may be the key. I had previously tested using
Access 2000 file format, but after reading your latest post, I tested the
same code using Access 2002 format, and I can now reproduce the problem.
I'm afraid I don't have any good suggestions for a workaround at this
time. I'll ask around, and post back here if I find anything.