Read and write all control properties in a database field separately via VB6

Question

I need to save the properties of about 50 controls in the form, such as length, width and height, distance from the top, distance from the left, font color, background color, etc in Access 2003 data base. And since Access 2003 only has the ability to manage 255 fields. It is not possible to save any of the features in one field. How can I save and read the properties of each control in a field serially, such as:

lbl(1): "120|300|250|500|True|330|False|, ..."
lbl(2): "90|140|50|310|Fale|100|False|, ..."

Also, I don't want to use the possibility of saving in a file like ini or binary. Is there a way? Thanks all

More Explain : There are about 50 controls such as label, image and shape, etc. in the form, the user can click on each control to change the size and location of each control, as well as other features such as font and color of the control. I want all the properties of a control to be stored and read separately in one field and together in access db.

As you know, each table in Access 2003 can manage only 255 fields. I have more than 50 control objects in the form, each of which has more than 9 properties . In other words, 450 fields are needed to store in table. If I can store all the features of each control in one field, I only need about 50 fields. The problem is how to store and read these properties together.

Dim Data() As String 
Dim moni As New ADODB.Recordset 

If moni.state = adStateOpen Then 
    moni.Close moni.Open ("select * from Moni_Settings "), dB, adOpenKeyset, adLockOptimistic 
    Data = Split(Text1, "|") newData = Data(0) & "," & Data(1) & "," & Data(2) & "," & Data(3) & "," & Data(4) & "," & Data(5) & "," & Data(6) & "," & Data(7) 'etc. 
    lbl(0).height = Data(0) 
    lbl(0).width = Data(1) 
    lbl(0).Left = Data(2)     
    lbl(0).Top = Data(3)
    lbl(0).ForeColor = Data(4)
    lbl(0).FontName = Data(5)
    lbl(0).FontSize = Data(6)
    lbl(0).FontBold = Data(7) 
End If

I was able to read the arrays with split, but I could not save the arrays in the field.

Answer 1

Firstly, I am not sure why you would want to be saving/setting the properties of controls in a form programmatically.

However, you could look at using .SaveAsText and .LoadFromText to save/load the form and all of the control properties to a text file:

Application.SaveAsText acForm,"frmControl","J:\test-data\frmControl.txt"
Application.LoadFromText acForm,"frmTestControl","J:\test-data\frmControl.txt"

If that isn't what you are after, then I think that you would need to use a simple table in Access consisting of ID (Autonumber), ControlName (Short Text), PropertyName (Short Text), PropertyValue (Short Text). Then some code like this would be used to write to this table:

Sub sSaveControlData()
    On Error GoTo E_Handle
    Dim rsControl As DAO.Recordset
    Dim ctl As Control
    Dim prp As Property
    Set rsControl = CurrentDb.OpenRecordset("tblFormControl")
    DoCmd.OpenForm "frmControl", acDesign, , , , acHidden
    For Each ctl In Forms!frmControl.Controls
        For Each prp In ctl.Properties
            With rsControl
                .AddNew
                !ControlName = ctl.Name
                !PropertyName = prp.Name
                !PropertyValue = prp.Value
                .Update
            End With
        Next prp
    Next ctl
sExit:
    On Error Resume Next
    DoCmd.Close acForm, "frmControl"
    rsControl.Close
    Set rsControl = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sSaveControlData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Answer 2

Ok, reasons aside?

Access is not the only database with such column count limits. But, so what?

If you are using a database, then you can store 100,000 such items.

How? It is called a database, and it is called data normalizing.

For example, ask the following:

How can an Access database be used say for an accounting system, and say store large number of invoices, or job costing, or whatever? Such systems will have VAST more then 255 "things" or information to store, right?

So, say for a ONE control, I want to store 100 settings.

And while the table is limited to 255 columns, an access form can have over 700 controls on a form!!!

So, how does anyone then store such accounting information, where we might have pricing for 10,000 products each with all kinds of information?

You simply build a correct database system that handles this, and in most cases, you probably never need more then 60 columns, let alone 255. In fact, looking even at super complex databases, if you build and designed the database correctly, then you probably NEVER need more then 60 fields.

And in fact, looking at the property sheet for JUST ONE control in access, it is SO VERY LARGE it scrolls right off the screen!!! (this is Access 2010+ format).

I mean, will you look at the "monster" property sheet for above!!!!

But, looking at the property sheet should HINT and GIVE you the Rosetta stone, and gold bar idea of HOW you do this with a database!!!!

Simple, you use what is called database normalizing!!!!

In fact, to show how one does this, we use Access!!!!

This is a classic design, and really is like how an invoice, or whatever system you build has LOTS of repeating data for ONE thing!!!

So, lets build a UI for this - might as well use Access.

So for ONE form, I might have those 700 controls, so we build a master and child system like this (not much different then say building an invoice - lots of rows of things purchased in the invoice, or in this case, lots of rows about the controls on the form).

You have/get this setup:

As you can see, I can have 10, or 700 or even more rows for the controls - it is all JUST repeating data.

This is how and why you NEVER want to think of a database like some Excel spreadsheet.

Access and databases are a DIFFERENT way of looking at information. And in 20+ years, I am hard pressed to think of EVER requiring a table with more then 50 columns, let alone 100 columns!!!!

How does the database "schema" for this look? It looks like this:

Now, you don't have to use a database for this. I suppose you could use XML, or these days json data is all the rage.

But, EVEN in the case of using XML or json, or an Access database to store this data?

You still use data modeling, and you DROP the old school concept of thinking of data like an Excel sheet. This is ALSO why over and over again, Excel sheets when they start to fall apart for business management, they get moved over to a database, and often Access.

So, in terms of evolution? Access and database modeling is how we solve these issues - ones that Excel will struggle with. And in MOST cases, it is not actually the number of columns, but once such a system is in a database?

Then we can even ask questions like

Find me the form with the most controls!!!

Such simple questions become hard in Excel, since we might have created 400 columns wide, but now we have to try and check if some cells are empty to figure out if controls exist - very difficult!

So, now in code, we will require TWO operations to get information about ONE form.

We will pull the parent table data (one row) that has information about the form, maybe a description about the form, who created it (or maybe when we ran the tool you are creating to pull all the control positions from the form).

So, our code will look like this:

Private Sub Command20_Click()

   Dim strFormName        As String
   Dim rstForm            As DAO.Recordset     ' ONE record for the form
   Dim rstFormControls    As DAO.Recordset     ' child table - many rows of controls
   Dim strSQL             As String
   
   
   strFormName = "frmHotels"
   
   ' get the master (parent form reocrd)
   strSQL = "SELECT * FROM tblForms WHERE FormName = '" & strFormName & "'"
   
   Set rstForm = CurrentDb.OpenRecordset(strSQL)
   
   Debug.Print "Form name = " & rstForm!FormName
   
   ' now get all controls for this form from tblControls
   
   strSQL = "SELECT * FROM tblControls WHERE Form_ID = " & rstForm!ID
   Set rstFormControls = CurrentDb.OpenRecordset(strSQL)
   
   Do While rstFormControls.EOF = False
   
       Debug.Print rstFormControls!ControlName & " - X,Y = " & _
       rstFormControls!XLocation & "," & rstFormControls!YLocaiton
       
       rstFormControls.MoveNext
  Loop
  
   
End Sub

And the output window looks like this:

So the array structure you are looking for? Nah, just use a database and the concept of data modeling for this.

As you can see in above, we can have a form with 10 controls, or 700.

As I stated, while the database max column count is 255, an access form can have over 700 controls (I think the actual hard limit is 754).