Nov 09 2020 09:05 AM
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.
Nov 10 2020 12:20 AM - edited Nov 10 2020 12:23 AM
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
Nov 16 2020 12:01 PM
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.
Nov 16 2020 03:58 PM