[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • Last Modified:

Too many fields defined error - how to fix

I'm trying to make a program that creates a new database when it loads.  However, there are more than 255 fields (all of which are used), so I get this error.  I've tried using separate tables, but I still get the error.  Am I doing something wrong?  How can I get it to work?  Here is what I tried.  The error occurs when I get to the line rst.Open "SELECT...

Public Sub NewDatabase()

    Dim cat As New ADOX.Catalog
    Dim tb1 As New ADOX.Table
    Dim tb2 As New ADOX.Table
    Dim col As New ADOX.Column
   
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & Userfile & ";" & _
               "Jet OLEDB:Engine Type=4;"
   
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & Userfile
   
    With tb1
        .Name = "OMWII"
        .Columns.Append "StockName", adVarWChar, 40
        .Columns.Append "StockSymbol", adVarWChar, 6
        .Columns.Append "StockPrice", adSingle
   
        For i = 0 To 15
            .Columns.Append "QtrEnd(" & CStr(i) & ")", adDate
            .Columns.Append "RecDate(" & CStr(i) & ")", adDate
            .Columns.Append "RecPrice(" & CStr(i) & ")", adSingle
            .Columns.Append "52WkHi(" & CStr(i) & ")", adSingle
            .Columns.Append "52WkLo(" & CStr(i) & ")", adSingle
            .Columns.Append "RPRank(" & CStr(i) & ")", adSingle
            .Columns.Append "EPSRank(" & CStr(i) & ")", adSingle
            .Columns.Append "RSRank(" & CStr(i) & ")", adSingle
            .Columns.Append "Revenue(" & CStr(i) & ")", adSingle
            .Columns.Append "Income(" & CStr(i) & ")", adSingle
            .Columns.Append "Shares(" & CStr(i) & ")", adSingle
        Next i
       
    End With
   

    With tb2
        .Name = "OMWII2"
        .Columns.Append "StockSymbol", adVarWChar, 6
   
        For i = 0 To 15
            .Columns.Append "SPS(" & CStr(i) & ")", adSingle
            .Columns.Append "EPS(" & CStr(i) & ")", adSingle
            .Columns.Append "Margin(" & CStr(i) & ")", adSingle
            .Columns.Append "SlsChgLQ(" & CStr(i) & ")", adSingle
            .Columns.Append "IncChgLQ(" & CStr(i) & ")", adSingle
        Next i
       
    End With
   
    cat.Tables.Append tb1
    cat.Tables.Append tb2
   
    Set tbl = Nothing
    Set tb2 = Nothing
    Set cat = Nothing
   
    Connect2Database

End Sub

Public Sub Connect2Database()

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
   
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
              Userfile & ";Persist Security Info=False"
   
    rst.Open "SELECT * FROM OMWII LEFT JOIN OMWII2 " & _
    "ON OMWII.StockSymbol = OMWII2.StockSymbol", cnn, adOpenStatic, adLockOptimistic
    rst.AddNew
   
    For i = 0 To rst.Fields.Count - 1  'Eliminate Nulls
      rst.Fields(i).Value = 0
    Next i
   
    rst.Update
    rst.MoveNext
    If rst.EOF Then rst.MoveLast 'Eliminate EOF Error.

    Screen.MousePointer = vbNormal

End Sub
0
jfyfe
Asked:
jfyfe
  • 2
  • 2
1 Solution
 
gindeCommented:
I don't think that selecting more than 250 fields will work. If your application permits, select more than one field in one. I mean :
select field1 + "," + field2 + "," + field3 as first field
from yourtable.
0
 
GustavoValdesCommented:
Using a different approach from what you've been trying to do, the following code is able to create a row containing up to 255 fields, note that this is not an arbitrary number, is the maximum number of fields allowed in a table. So keep that in mind and if for any reason you need more fields maybe you should redesing your solution to fit to this Access' limit.

My approach is using just an ADO Command and constructing a Data Definition Language statement in wich you'll be able to define the name of the fields as well as its type. One thing more, I'm almost shure that it will always execute faster because it just convey the information to the DBMS and that's the responsible of doing the work, in the other approach there will be a big overhead just to handle the objects(field's in this particular case) and moving throw a collection of them.
    Cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\jdk1.2.2\Pract\db\CafeJava.mdb", "Admin", ""
   
    'Setting up the command
    'Adding 1000 fields
    SQLText = "CREATE TABLE NewTable("

    Dim i   As Integer

    'Adding the first field
    SQLText = SQLText & "Field0 varchar(30)"
    For i = 1 To 199
        SQLText = SQLText & ", Field" & CStr(i) & " varchar(30)"
    Next i

    SQLText = SQLText & ")"

    With Cmd
        .ActiveConnection = Cnn
        .CommandType = adCmdText
        .CommandText = SQLText
        .Execute
    End With

Hope it helps.
0
 
jfyfeAuthor Commented:

ginde: your suggestion was interesting, but I don't think I can make it work because I need to reference each field without parsing out the values.

gustavo: your suggestion of creating a table was helpful and I think I may use it.

However, since I still didn't get a solution that I feel comfortable with, I am just going to cancel the question.  Just wanted to say thanks for your suggestions though.
0
 
gindeCommented:
If you are going to use GustavoValdes's answer, then I would suggest you to give him points... As you already know, you can give different grades depending on your comfort..
0
 
jfyfeAuthor Commented:
I give you a C because you didn't answer my question, other than to say using more that 255 fields won't work.  I was kind of hoping for ideas to use a work-around or something.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now