So I finally had a chance to play with reading data via ADO in Access and I've hit a stumbling block...
I'm receiving "type mismatch" errors when trying to read from the otherTelephone field. This field can hold multiple values, and when exported as an delimited text file, the multiple values are separated by semi-colons and I think this is the reason I'm getting stuck here!
FYI here's my code (utilising late binding) to read from AD
Dim rs As Object
Dim strSQL As String
Const adOpenStatic As Integer = 3
strSQL = "SELECT sAMAccountName, telephoneNumber, otherTelephone " & _
"FROM 'LDAP://DC=<...>,DC=<...>'" & _
"WHERE objectClass='user' AND objectCategory='Person' AND sAMAccountName='smithj'"
Set rs = New ADODB.Recordset
rs.Open strSQL, "Provider=ADSDSOObject;", adOpenStatic
If rs.EOF And rs.BOF Then
Me.txtNTUsername.Value = "Unable to find a match in AD"
Me.txtNTUsername.Value = rs.Fields("sAMAccountName")
Me.txtTelephoneNumber.Value = rs.Fields("telephoneNumber")
Me.txtOtherTelephone.Value = rs.Fields("otherTelephone")
Set rs2 = Nothing
With the code as above, no value is entered into txtOtherTelephone even though there definately is something in there (in this example there is also only a single value in otherTelephone).
When I change the line to read
Me.txtOtherTelephone.Value = rs.Fields("otherTelephone") & "x"
Me.txtOtherTelephone.Value = Cstr(rs.Fields("otherTelephone")) & "x"
I get Error 13 Type Mismatch
Any ideas what's wrong here?
If I've not explained clearly enough or you have any questions please let me know!
Ok, found the solution.
The value returned is of type Variant (it's an array of values)!
So what I had to do was the following
Dim i As Integer
Dim telExtension() As Variant
If IsNull(rs.Fields("otherTelephone").Value) Then
Me.txtTelExtension.Value = ""
telExtension() = rs.Fields("otherTelephone").Value
If UBound(telExtension) > 0 Then
For i = 0 To UBound(telExtension)
Me.txtTelExtension.Value = Me.txtTelExtension.Value & telExtension(0) & "; "
Me.txtTelExtension.Value = telExtension(0)