How do database of more 200 or more fields or columns?

[OrlandoRios]

Hello crazy people,


I´m brazilian coder, use VB6 and i need help, please.


How do database of more 200 or more fields or columns?


The columns must contain images of some GB's


When I try to pick up an error message "Formula too complex".


Can someone help me?


here is my code:

'--------------------------------------------

Sub make_DATABASE(dbGERpath$)

Dim f%, CAT As ADOX.Catalog, tOBJ As ADOX.Table

Set CAT = New ADOX.Catalog
CAT.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbGERpath$ & ";Mode=Read|Write"
CAT.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbGERpath$ & ";Mode=Read|Write"
Set tOBJ = New ADOX.Table

tOBJ.name = "OBJ" 'DEFININDO A TABELA (OBJ):
Set tOBJ.ParentCatalog = CAT

For f% = 0 To totFIELDS! 'Max fields 90 no error <but i need 200 or more fields>
With tOBJ.Columns
.Append "REG" & f%, adVarWChar, 52 'Tipo Text
.Item(f%).Attributes = adColFixed + adPropRequired
End With
Next f

CAT.Tables.Append tOBJ

Set CAT = Nothing: Set tOBJ = Nothing

End Sub


'--------------------------------------------


Function open_DATABASE() As Boolean

dbCON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbGERpath$ & ";Mode=Read|Write"
dbCON.CursorLocation = adUseClient
dbCON.Open

With cmdOBJ
.ActiveConnection = dbCON
.CommandText = "SELECT * FROM OBJ" 'txSQL$
.CommandType = adCmdText
End With

With tOBJ 'Cria a Tabela OBJ
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdOBJ
End With

Set SF.FLEX.DataSource = tOBJ
SF.FLEX.Redraw = True: SF.FLEX.Refresh

End Function

'--------------------------------------------

[gibra]

Access support up to 255 fields, so no problem for this.
About images, search how to save/load BLOB field.

[RhinoBull]

Follow few links in my signature to see how to store/retrieve images to/from database.

However, I recommend replacing Access db with more robust SQL Server Express and also rethink your table(s) design.
Try breaking one table into multiple with common fields that will uniquely identify relationship between tables.

[OrlandoRios]

Access support up to 255 fields, so no problem for this.
About images, search how to save/load BLOB field.

Greetings Gibra,

The problem is real and exists. There are limitations in VB6 and when I try to set a database with more than a hundred columns, the error (Formula too complex) appears. The problem is accentuated when trying to define the database fields for images or columns to 1 GB or more of data. Perhaps the problem is the shape or configuration of the database.

Thank you and good coding.

[gibra]

Greetings Gibra,

The problem is real and exists. There are limitations in VB6 and when I try to set a database with more than a hundred columns, the error (Formula too complex) appears. The problem is accentuated when trying to define the database fields for images or columns to 1 GB or more of data. Perhaps the problem is the shape or configuration of the database.

Thank you and good coding.

Normally into a Access database is not recommended to store images binary data, instead store path only.

Images are rarely updated, and if must be updated isn't appropriate to use a database for this.
in any case, as he wrote Rhinobull, you may not use this work for Access.

[OrlandoRios]

Follow few links in my signature to see how to store/retrieve images to/from database.

However, I recommend replacing Access db with more robust SQL Server Express and also rethink your table(s) design.
Try breaking one table into multiple with common fields that will uniquely identify relationship between tables.

Greetings RhinoBull,

My problem is not "store / retrieve images to / from database" but the database supporting data and the number of columns I need.

Your choice of migrating to SSEx is good and I had already thought of that, but I do not have enough time to learn it.

What do you mean by "Rethink your table (s) design?". It would be another solution?. Could you explain, please?

Breaking the tables, I had thought of before, but a bigger problem then arises: How to display and integrate data from multiple tables in a single FlexGrid?

Thank you and good coding.

[OrlandoRios]

Normally into a Access database is not recommended to store images binary data, instead store path only.

Images are rarely updated, and if must be updated isn't appropriate to use a database for this.
in any case, as he wrote Rhinobull, you may not use this work for Access.

Greetings Gibra,

Yes, I can save only links or paths, no probrema. But I would like to integrate everything into one database with multiple tables. ---> It would be more practical and objective.

(The option to change the Access would be my last option)

Can I use multiple tables in the same database and integrate and display all in one FlexGrid, it is possible to implement?


Thank you and good coding.

[DataMiser]

I tried your code to make database and set it to do 200 fields, database created, 200 fields added no problems.

I did get an object not set error when I ran the code that opens the table where it tried to bind to a flex grid.

Where are you getting the error?
When you set it to a high number of fields does it create any of them?

What version of Access are you using?

Edit: I changed from FlexGrid to DBGrid and the binding worked just fine, showing all 200 columns

[OrlandoRios]

I tried your code to make database and set it to do 200 fields, database created, 200 fields added no problems.

I did get an object not set error when I ran the code that opens the table where it tried to bind to a flex grid.

Where are you getting the error?
When you set it to a high number of fields does it create any of them?

What version of Access are you using?

Edit: I changed from FlexGrid to DBGrid and the binding worked just fine, showing all 200 columns

Greetings DataMiser,

With the amount of fields greater than 254 gives error [Too many fields defined].

I posted below the full source code and more functional.

With totFIELDS variable with value greater than 254 error happens [Too many fields defined].

Using VB 6.0 with the reference below:
References 1: Microsoft ActiveX Data Objects 2.8 Library (ADO)
References 2: Microsoft Ado Ext 2.8 for DDL and Security (ADOX)

Should I use other references?

Thank you and good coding.


Option Explicit
'Components : Microsoft FlexGrid Control 6.0 (FLEX)
'References 1: Microsoft Activex Data Objects 2.8 library (ADO)
'References 2: Microsoft Ado Ext. 2.8 for DDL and Security (ADOX)

Private Sub Form_Load()
Dim totFIELDS!, dbPTH$, f%, CAT As ADOX.Catalog, tADOX As ADOX.Table
Dim dbCON As New ADODB.Connection, cmdOBJ As New ADODB.Command, tOBJ As New ADODB.Recordset

totFIELDS = 254 'fields defined
dbPTH$ = "c:\database01.mdb": Kill dbPTH$ 'Define n del DataBase

'Create DataBase
Set CAT = New ADOX.Catalog
CAT.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
CAT.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
Set tADOX = New ADOX.Table

tADOX.Name = "OBJ" 'DEFININDO A TABELA (OBJ):
Set tADOX.ParentCatalog = CAT

For f% = 0 To totFIELDS! 'totFIELDS > 254 gives error [Too many fields defined]
With tADOX.Columns
.Append "REG" & f%, adVarWChar, 52 'Tipo Text
.Item(f%).Attributes = adColFixed + adPropRequired
End With
Next f

'totFIELDS > 254 gives error [Too many fields defined] in the line below
CAT.Tables.Append tADOX

Set CAT = Nothing: Set tADOX = Nothing


'Open DataBase
dbCON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
dbCON.CursorLocation = adUseClient
dbCON.Open

With cmdOBJ
.ActiveConnection = dbCON
.CommandText = "SELECT * FROM OBJ" 'txSQL$
.CommandType = adCmdText
End With

With tOBJ 'Cria a Tabela OBJ
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdOBJ
End With

End Sub

[firoz.raj]

please always write the code inside the code tags .it is much readable.

Option Explicit
'Components : Microsoft FlexGrid Control 6.0 (FLEX)
'References 1: Microsoft Activex Data Objects 2.8 library (ADO)
'References 2: Microsoft Ado Ext. 2.8 for DDL and Security (ADOX)

Private Sub Form_Load()
Dim totFIELDS!, dbPTH$, f%, CAT As ADOX.Catalog, tADOX As ADOX.Table
Dim dbCON As New ADODB.Connection, cmdOBJ As New ADODB.Command, tOBJ As New ADODB.Recordset

totFIELDS = 254 'fields defined
dbPTH$ = "c:\database01.mdb": Kill dbPTH$ 'Define n del DataBase

'Create DataBase
Set CAT = New ADOX.Catalog
CAT.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
CAT.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
Set tADOX = New ADOX.Table

tADOX.Name = "OBJ" 'DEFININDO A TABELA (OBJ):
Set tADOX.ParentCatalog = CAT

For f% = 0 To totFIELDS! 'totFIELDS > 254 gives error [Too many fields defined]
With tADOX.Columns
.Append "REG" & f%, adVarWChar, 52 'Tipo Text
.Item(f%).Attributes = adColFixed + adPropRequired
End With
Next f

'totFIELDS > 254 gives error [Too many fields defined] in the line below
CAT.Tables.Append tADOX

Set CAT = Nothing: Set tADOX = Nothing


'Open DataBase
dbCON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPTH$ & ";Mode=Read|Write"
dbCON.CursorLocation = adUseClient
dbCON.Open

With cmdOBJ
.ActiveConnection = dbCON
.CommandText = "SELECT * FROM OBJ" 'txSQL$
.CommandType = adCmdText
End With

With tOBJ 'Cria a Tabela OBJ
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdOBJ
End With

End Sub

[RhinoBull]

Greetings RhinoBull,
My problem is not "store / retrieve images to / from database" but the database supporting data and the number of columns I need.
Your choice of migrating to SSEx is good and I had already thought of that, but I do not have enough time to learn it.
What do you mean by "Rethink your table (s) design?". It would be another solution?. Could you explain, please?
Breaking the tables, I had thought of before, but a bigger problem then arises: How to display and integrate data from multiple tables in a single FlexGrid?
Thank you and good coding.

Instead of building one table (basically flat file) with many fields I would "break" it down into multiple "relational" tables.
Have a good look at these few links below:
Fundamentals of Relational Database Design
Relational Database Design - Normalization

[DataMiser]

With the amount of fields greater than 254 gives error [Too many fields defined].

Yes as was mentioned earlier the max is 255 since you are starting with 0 254 makes 255

Yet you said you were getting an error with anything over 200 so something is not the same now.

Anyway, I could not imagion a table where I needed more than 255 fields in the same table, most likely this is due to poor db design and should be split into more than one table perhaps several tables.

[OrlandoRios]

Yes as was mentioned earlier the max is 255 since you are starting with 0 254 makes 255

Yet you said you were getting an error with anything over 200 so something is not the same now.

Anyway, I could not imagion a table where I needed more than 255 fields in the same table, most likely this is due to poor db design and should be split into more than one table perhaps several tables.

Greetings DataMiser,

If you change the Text field for a Memo or LongBinary that carry up to 1.2 gigabytes, or use dbVersion old as [dbVersion20 2.0 file format],
the number of fields may decrease to 90 or less.

Let me explain further:

I need a database format that supports up to 600 fields or columns, which accepts images or Memo fields up to 5 megabytes each. The software I'm developing for a Japanese company has the function to create, modify and save data or objects. The User can create / delete / modify these objects on your screen, in addition to searching the database as a common software. These objects store images, videos, link's, sounds, text, files and many many others. Each object has a life of its own and can be transmitted over the web, encrypted, locked, disabled, and have encrypted password public or private. Each screen can be saved to a record and each object would be a field in the database. I have developed almost all set missing database.

As you can see, this software is unique and does not exist yet. The company demanded that was written in VB6, do not know the reason. I could do in another language with more features and more complexity. But the Japanese mobsters want the source code in VB6 or kill me . I think their intent is automatically porting the code in VB6 to another language.

Thank you and good coding.

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


It sounds like an Access based database is not right for you (the theoretical maximum size is 2GB, but anything over 1GB is likely to fail), you may be better off using SQL Server Express (maximum 10GB per database, safely) or similar.


The fact you think you want more than 50 fields in a table also makes it seem that you don't really understand databases, because it is unusual to have more than 20 fields per table - and usually when we see people wanting more than that, it is so that fields can be duplicated (eg: Name1, Name2, etc), which is not what you should be doing.

If you explain to us why you think you want that many fields (and what information you are actually going to be storing), we can give you appropriate advice.

[OrlandoRios]

Instead of building one table (basically flat file) with many fields I would "break" it down into multiple "relational" tables.
Have a good look at these few links below:
Fundamentals of Relational Database Design
Relational Database Design - Normalization

Greetings RhinoBull,

I'm reading the information you gave me and found it interesting and I'll put it on top of my tree priorities. I think my problem might actually be solved using multiple tables as you showed. Already using two tables in the database, one for storing ordinary data and one for storing attributes and properties of the objects. I'll spend a few days developing and using multiple tables and then try to figure out how to show data from multiple tables in a single Flex.

Thank you and good coding.

ps. If I can invite you to spend a week on the beach in Ipanema or Copacabana, with all expenses paid, seeing beautiful girls topless. Ok?

[OrlandoRios]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


It sounds like an Access based database is not right for you (the theoretical maximum size is 2GB, but anything over 1GB is likely to fail), you may be better off using SQL Server Express (maximum 10GB per database, safely) or similar.


The fact you think you want more than 50 fields in a table also makes it seem that you don't really understand databases, because it is unusual to have more than 20 fields per table - and usually when we see people wanting more than that, it is so that fields can be duplicated (eg: Name1, Name2, etc), which is not what you should be doing.

If you explain to us why you think you want that many fields (and what information you are actually going to be storing), we can give you appropriate advice.

Greetings si_the_geek,

Thanks for moving this topic to a more appropriate place.

I made several comparisons between Access, SSEx, SQLite and all have some basic differences and limitations. But like a classic Tower of Babel.

The reasons why I want a database so complex, you can see on # 13.

Thank you and good coding.

[Doogle]

Sounds like you have the concept of a 'Repository' and an 'Index' which (IMHO) is a good idea. However, the choice of a 'simple' Database for the Repository seems arbitrary. Given the volumes of data you're likely to run into design limitations associated with the DBMS you decide to use. I'd suggest that the Repository would be better implemented as a File System, ie a collection of Folders split over as many Disks as required, and the Index implemented as a set of Database Tables hoding the Attributes, Properties, Location etc of each element within the Repository.

This type of requirement is (was) common for Product Data Management Systems in engineering, where, for example, Drawings, BOM, Documentation, and other sorts of data could be perceived to be held in one place but in actuality were 'distributed'. The basic requirement boils down to provide the end user with the capability to add, modify, remove, and find meta-data. They shouldn't care or even want to know where it's actually stored.

[si_the_geek]

The reasons why I want a database so complex, you can see on # 13.

No I can't, which is why I asked.

From the information in post #13, I can only see a need for 1 field.

Presumably there is a need for some extra data along with it (perhaps a file name? index number? etc), but you have not given any indication of what.

[DataMiser]

Yep sounds like you need a table with 2 or 3 fields and hundreds of rows which is not a problem in any db engine.