Size of SQL-Server Varchar

Question

This is my function to get the size of a varchar field of a SQL-Server table used in access via ODBC:

Function fn_field_size(par_tab, par_field As Variant) As Variant

    Dim db_tab As DAO.Database
    Dim tab_tab As DAO.TableDef
    Dim tab_field As DAO.field

    fn_field_size = 0

    Set db_tab = CurrentDb()
    Set tab_tab = db_tab.TableDefs(par_tab)
    Set tab_field = tab_tab.Fields(par_field)
    fn_field_size = tab_field.Size

    Set tab_tab = Nothing
    Set db_tab = Nothing

End Function

It seemed to work fine until I used the function for a varchar(1000) field. In this case tab_field.Size is 0. With smaller fields like Varchar(100) the function works fine and returns 100.

Why is this?

Is DAO a requirement? I'd try to see if ADO makes more sense. That said as per [documentation](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/field-size-property-dao), the value you get from `DAO.Field.Size` is the *maximum* size, *in bytes*. Since an Access `Text` field is only up to 255 characters (510 bytes?), I suspect DAO is possibly seeing a `varchar(1000)` as some `Memo` field, for which the `Field.Size` is always 0. What do you get for `varchar(510)` vs `varchar(512)`? — Mathieu Guindon, Dec 06 '18 at 15:50
@MathieuGuindon is correct that Access database engine will interpret 255 character or less as `Text` (or `Short Text` in newer versions) and anything more as `Memo` (or `Long Text` in newer versions). Data access library (DAO/ADO) has no say on this as that is how the Access database engine will interpret the linked table using its data type system. — this, Dec 06 '18 at 15:57
Im not sure if DAO is a requirement. What is the difference between DAO and ADO? Can I use ADO only in this function? — Gener4tor, Dec 06 '18 at 15:57
Pretty sure skipping the link table / Access stuff and opening a `Recordset` with a connection string straight to the SQL Server, will get you the correct metadata. — Mathieu Guindon, Dec 06 '18 at 16:01
Access has no concept of a `VARCHAR(1000)` field. All text fields are UTF-16, and all fields that accept more than 255 characters are interpreted as _Long Text_. You need to lookup the field size on the server, and can do that using ADOX. I can write an answer, but generally you want to have reusable logic for setting up ADODB connections. You can also query the info from SQL server using a passthrough query. — Erik A, Dec 06 '18 at 16:10
At simple PT query as I have in my answer below is the least amount of work. — Albert D. Kallal, Dec 07 '18 at 19:53

score 1 · Accepted Answer · answered Dec 07 '18 at 00:41

As pointed out by others, ODBC sees a varchar,nvchar,char over 255 chars as “memo” type column, and thus you can’t get the max char count.

Assuming that you have a pass-though query defined in Access. (I always make one, and re-use it over and over for all code), then you can get the length of any field with this code:

Public Function GetFLength(strTable As String, strField As String) As Long

   Dim strSQL     As String

   strSQL = "select col_LENGTH('" & strTable & "','" & strField & "')"

   With CurrentDb.QueryDefs("qryPassR")
      .SQL = strSQL
      GetFLength = .OpenRecordset()(0)
   End With

End Function

For a varchar(max), the above will return -1, but for anything else, it will return the length of the column.

Size of SQL-Server Varchar

1 Answers1