Results 1 to 17 of 17

Thread: DAO MDB database SQL error "Too many fields"

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    DAO MDB database SQL error "Too many fields"

    This code used to work on my older MDB files, but with the more recent MDB files, it no longer works and now I get a "Too many fields" error. Thing is, looking at my code I don't see where I've added any new fields in this tables.

    And here is the code I'm using:

    SQLQ$ = "SELECT Element.*, Count.*, Sample.Kilovolts, Sample.Numbers, Sample.Sets, "
    'SQLQ$ = "SELECT Element.*, Count.CountToRow, Count.LineOrder, Count.ElementOrder, Sample.Kilovolts, Sample.Numbers, Sample.Sets, "

    SQLQ$ = SQLQ$ & "Sample.BeamSpotScanMode, "

    SQLQ$ = SQLQ$ & "Sample.BeamCurrents, Sample.BeamSizes, Line.*, Sample.RowOrder "
    'SQLQ$ = SQLQ$ & "Sample.BeamCurrents, Sample.BeamSizes, Line.LineToRow, Line.LineOrder, Sample.RowOrder "

    SQLQ$ = SQLQ$ & "FROM Count Right JOIN (Element RIGHT JOIN (Line RIGHT JOIN Sample "
    SQLQ$ = SQLQ$ & "ON Line.LineToRow = Sample.RowOrder) ON Element.ElementToRow = Sample.RowOrder) "
    SQLQ$ = SQLQ$ & "ON Count.CountToRow = Sample.RowOrder WHERE ("

    If FormMAIN.CheckElementXray.Value = vbChecked Then
    SQLQ$ = SQLQ$ & "Element.Symbol = '" & Trim$(DriftTmpSample(1).Elsyms$(1)) & "' AND "
    SQLQ$ = SQLQ$ & "Element.Xray = '" & Trim$(DriftTmpSample(1).Xrsyms$(1)) & "' AND "
    End If

    If FormMAIN.CheckMotorCrystal.Value = vbChecked Then
    SQLQ$ = SQLQ$ & "Element.Motor = " & Str$(DriftTmpSample(1).MotorNumbers%(1)) & " AND "
    SQLQ$ = SQLQ$ & "Element.Crystal = '" & Trim$(DriftTmpSample(1).CrystalNames$(1)) & "' AND "
    End If

    If FormMAIN.CheckKeVStandard.Value = vbChecked Then
    SQLQ$ = SQLQ$ & "Sample.KiloVolts = " & Str$(DriftTmpSample(1).kilovolts!) & " AND "
    SQLQ$ = SQLQ$ & "Sample.Numbers = " & Str$(DriftTmpSample(1).number%) & " AND "
    End If

    SQLQ$ = SQLQ$ & "Sample.Types = " & Str$(DriftTmpSample(1).Type%) & " AND "
    SQLQ$ = SQLQ$ & "Element.DisableAcqFlag = 0" & " AND "
    SQLQ$ = SQLQ$ & "Line.LineOrder = Count.LineOrder" & " AND "
    SQLQ$ = SQLQ$ & "Element.ElementOrder = Count.ElementOrder" & ")"

    Set PrDs = PrDb.OpenRecordset(SQLQ$, dbOpenDynaset)

    You will notice in the commented out lines that I tried to narrow down the number of fields by replacing the Count.* and Line.* text with the explicit fields. But that didn't help and instead I now get an "Item not found in this collection" error if I use them. But I am using the correct field names, so not sure what that is about.

    Can anyone suggest what I'm doing wrong?
    Last edited by John Donovan; Sep 20th, 2020 at 12:48 AM.

  2. #2
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by John Donovan View Post
    This code used to work on my older MDB files, but with the more recent MDB files, it no longer works and now I get a "Too many fields" error.
    Died you try compacting/repairing the database?

    Wolfgang

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: DAO MDB database SQL error "Too many fields"

    When you get that error, debug.print the SQLQ variable and post is contents. Sometimes wth ADO/DAO, the displayed error doesn't mean exactly what it says, but rather can be due to a SQL string with a typo.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4
    Fanatic Member
    Join Date
    Jan 2015
    Posts
    596

    Re: DAO MDB database SQL error "Too many fields"

    I know there is a limit for the number of returned fields.
    I had the same too (ADO)
    Reduce the number of fields instead of using the *

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: DAO MDB database SQL error "Too many fields"

    "Too Many Fields Defined" error when you save a table in Access is worth reading too.

    The internal column count that Microsoft Access uses to track the number of fields in the table has reached 255, even though you may have fewer than 255 fields in the table. This can happen because Access does not change the internal column count when you delete a field. Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by Wolfgang Enzinger View Post
    Died you try compacting/repairing the database?

    Wolfgang
    It's not a problem with just one database, it's a problem with all the newer databases.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by LaVolpe View Post
    When you get that error, debug.print the SQLQ variable and post is contents. Sometimes wth ADO/DAO, the displayed error doesn't mean exactly what it says, but rather can be due to a SQL string with a typo.
    Good idea. OK, so here is the SQL string when I get the error:

    Too many fields defined. (SQLQ string=SELECT Element.*, Count.*, Sample.Kilovolts, Sample.Numbers, Sample.Sets, Sample.BeamSpotScanMode, Sample.BeamCurrents, Sample.BeamSizes, Line.*, Sample.RowOrder FROM Count Right JOIN (Element RIGHT JOIN (Line RIGHT JOIN Sample ON Line.LineToRow = Sample.RowOrder) ON Element.ElementToRow = Sample.RowOrder) ON Count.CountToRow = Sample.RowOrder WHERE (Element.Symbol = 'si' AND Element.Xray = 'ka' AND Sample.Types = 1 AND Element.DisableAcqFlag = 0 AND Line.LineOrder = Count.LineOrder AND Element.ElementOrder = Count.ElementOrder))

    The thing is, I don't think it's a typo because it works fine on all my older MDB files. It's just the more recent MDB files that I'm getting this error. And I don't see that I've added any new fields to these tables.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by Thierry69 View Post
    I know there is a limit for the number of returned fields.
    I had the same too (ADO)
    Reduce the number of fields instead of using the *
    Wait. You mean using the wildcard character in the SQL string will *reduce* the number of fields???? I would have thought it would make them all get loaded!

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by dilettante View Post
    OK, good to know. So what can I do to avoid this error?

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by Thierry69 View Post
    I know there is a limit for the number of returned fields.
    I had the same too (ADO)
    Reduce the number of fields instead of using the *
    So I modified the first line to include Sample.* and get the same error:

    Too many fields defined. (SQLQ string=SELECT Element.*, Count.*, Sample.*, Sample.BeamCurrents, Sample.BeamSizes, Line.*, Sample.RowOrder FROM Count Right JOIN (Element RIGHT JOIN (Line RIGHT JOIN Sample ON Line.LineToRow = Sample.RowOrder) ON Element.ElementToRow = Sample.RowOrder) ON Count.CountToRow = Sample.RowOrder WHERE (Element.Symbol = 'si' AND Element.Xray = 'ka' AND Sample.Types = 1 AND Element.DisableAcqFlag = 0 AND Line.LineOrder = Count.LineOrder AND Element.ElementOrder = Count.ElementOrder))

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    I also changed it to Line.* and still get the same error:

    Too many fields defined. (SQLQ string=SELECT Element.*, Count.*, Sample.*, Line.* FROM Count Right JOIN (Element RIGHT JOIN (Line RIGHT JOIN Sample ON Line.LineToRow = Sample.RowOrder) ON Element.ElementToRow = Sample.RowOrder) ON Count.CountToRow = Sample.RowOrder WHERE (Element.Symbol = 'si' AND Element.Xray = 'ka' AND Sample.Types = 1 AND Element.DisableAcqFlag = 0 AND Line.LineOrder = Count.LineOrder AND Element.ElementOrder = Count.ElementOrder))

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    OK, so I tried to explicitly specify more of the fields, but now I'm getting a "Item not found" error. But I don't see what field is missing... I'm sure I'm looking right at it, but I'm not seeing it.

    Item not found in this collection. (SQLQ string=SELECT Element.*, Count.CountToRow, Count.LineOrder, Count.ElementOrder, Sample.Kilovolts, Sample.Numbers, Sample.Sets, Sample.Types, Sample.BeamSpotScanMode, Sample.BeamCurrents, Sample.BeamSizes, Line.LineToRow, Line.LineOrder, Sample.RowOrder FROM Count Right JOIN (Element RIGHT JOIN (Line RIGHT JOIN Sample ON Line.LineToRow = Sample.RowOrder) ON Element.ElementToRow = Sample.RowOrder) ON Count.CountToRow = Sample.RowOrder WHERE (Element.Symbol = 'si' AND Element.Xray = 'ka' AND Sample.Types = 1 AND Element.DisableAcqFlag = 0 AND Line.LineOrder = Count.LineOrder AND Element.ElementOrder = Count.ElementOrder))

  13. #13
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: DAO MDB database SQL error "Too many fields"

    I agree with Wolfgang.

    I'd first try compacting and repairing.
    If it still Errors, I would cut the number of fields in half in the SQL and go from there.
    If works, add another quarter back, and keep adding until errors.

    Also noticed this: Element.*
    I've never tried using a wildcard on a field so not sure if this is valid.
    I believe the suggestion was to try SELECT *, with no other fields listed.

    HTH

  14. #14
    Fanatic Member
    Join Date
    Jan 2015
    Posts
    596

    Re: DAO MDB database SQL error "Too many fields"

    Try like
    SELECT Element.Field1, Element.Field2, Count.Field1, Count.Field2 , Sample.Kilovolts, Sample.Numbers, Sample.Sets, "
    etc...
    Avoiding *

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by vb6forever View Post
    I agree with Wolfgang.

    I'd first try compacting and repairing.
    If it still Errors, I would cut the number of fields in half in the SQL and go from there.
    If works, add another quarter back, and keep adding until errors.

    Also noticed this: Element.*
    I've never tried using a wildcard on a field so not sure if this is valid.
    I believe the suggestion was to try SELECT *, with no other fields listed.

    HTH
    I understand, but Element.* works on all the older MDB files, just not with the more recent ones.

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    Quote Originally Posted by Thierry69 View Post
    Try like
    SELECT Element.Field1, Element.Field2, Count.Field1, Count.Field2 , Sample.Kilovolts, Sample.Numbers, Sample.Sets, "
    etc...
    Avoiding *
    Thanks, I will try this next.

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Feb 2019
    Posts
    30

    Re: DAO MDB database SQL error "Too many fields"

    I want to thank everyone that replied to my questions. I finally figured it out. Reducing some of the Table.* entires to specific fields, fixed the "Too many fields" error, the "Item not found error was from code later on that was depending on the Table.* code.

    So that brings me to a question I've always wanted answered: once an error occurs and is trapped, and one is ready to step through the code, is there any way to step *backwards* to see exactly what line caused the error?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width