-
Sep 19th, 2020, 11:24 PM
#1
Thread Starter
Junior Member
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.
-
Sep 20th, 2020, 03:18 AM
#2
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by John Donovan
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
-
Sep 20th, 2020, 09:56 AM
#3
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.
-
Sep 20th, 2020, 10:21 AM
#4
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 *
-
Sep 20th, 2020, 10:52 AM
#5
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.
-
Sep 20th, 2020, 11:37 AM
#6
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by Wolfgang Enzinger
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.
-
Sep 20th, 2020, 11:41 AM
#7
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by LaVolpe
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.
-
Sep 20th, 2020, 11:43 AM
#8
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by Thierry69
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!
-
Sep 20th, 2020, 11:44 AM
#9
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by dilettante
OK, good to know. So what can I do to avoid this error?
-
Sep 20th, 2020, 11:51 AM
#10
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by Thierry69
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))
-
Sep 20th, 2020, 11:53 AM
#11
Thread Starter
Junior Member
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))
-
Sep 20th, 2020, 12:14 PM
#12
Thread Starter
Junior Member
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))
-
Sep 20th, 2020, 07:48 PM
#13
Fanatic Member
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
-
Sep 20th, 2020, 11:46 PM
#14
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 *
-
Sep 21st, 2020, 12:08 AM
#15
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by vb6forever
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.
-
Sep 21st, 2020, 12:09 AM
#16
Thread Starter
Junior Member
Re: DAO MDB database SQL error "Too many fields"
Originally Posted by Thierry69
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.
-
Sep 21st, 2020, 11:33 AM
#17
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|