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
jfyfeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.