Getting column widths from MSysObjects using SQL

[EarthSea]

Hi All,

I work with an application that allows me to run SQL on the database to which it is connected. I want to be able to get column widths for tables like I can using the system table USER_TAB_COLUMNS in ORACLE.

Now, I know that I can do this:

SELECT lvprop FROM msysobjects WHERE flags = 0 AND type = 1

In the data returned I can see (in plain text) 'ColumnWidth' but the details are encoded in a manner I do not recognise and so cannot decode.

Could somebody please tell how I can mine column width data from msysobjects.lvprop?

My ultimate goal is to be able to write SQL in which I specify the table name (msysobjects.name) and the table column.

Thank you.

[#2]

@EarthSea 

If VBA is an option for you, you could get the column width with a function (that also can be used in a query):

Public Function GetColumnWidth(sTable As String, sFieldname As String) As Long
  Dim rs As dao.Recordset
  Set rs = CurrentDb.OpenRecordset(sTable)
  
  GetColumnWidth = -1 'Init the return value in case the requested field is not found
  
  Dim Field As dao.Field
  rs.MoveFirst
  'Loop over the table fields until the requested field is found
  For Each Field In rs.Fields
    If Field.Name = sFieldname Then
      GetColumnWidth = Field.Size
      Exit For 'Step out of the For-loop since the requested field is found
    End If
  Next
  Set rs = Nothing
End Function

Place this function in a module and you can call it from a regular Access-query:

SELECT GetColumnWidth("tblBusiness", "BusinessId") AS ColumnWidth

Where "tblBusiness" is the table name and "BusinessID" is the field name, which can be replaced with any table and field name you like.

Maybe this works for you.

Best regards,

Tieme 

[#3]

Hi Tieme,

Thank you very much for the VBA code, I will keep that under my hat.

What I am really looking for is a method that will work with an ACCESS database 'out of the box' because I cannot necessarily expect my target audience to change their databases by adding functions. I really don't understand why column data in MSysObjects is not SQL-minable.

Thank you again.

[#4]

You won't find anything native. Such customization nearly always require VBA. Access is highly versatile but usually this is fine through advanced coding.

What is the end game here? What are you trying to achieve exactly? Maybe if we understand that better, we might be able to offer some suggestions?