VB & Database, SELECT works, but SELECT DISTINCT fails

SELECT works, but SELECT DISTINCT fails

Author	Message
Peter Bradsha #1 / 6	SELECT works, but SELECT DISTINCT fails I have a form that opens a one-column ADO recordset to match values with it: Dim adoRSLabEntries as ADODB.Recordset . . . Set adoRSLabEntries = New ADODB.Recordset With adoRSLabEntries .CursorType = adOpenKeyset .LockType = adLockOptimistic .ActiveConnection = frmDemog.adoCNPtDemog .Open "SELECT Lab FROM LAP" End With This works. But if I change the Open line to: .Open "SELECT DISTINCT Lab FROM LAP" it fails with the error "Run-time error '-2147217833(80040e57)': The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." MSDN search only turned up two articles, which weren't helpful. I'm still working on trying to run it against a smaller number of records - it seems to succeed with ~20 records, but fail with ~200. In the meantime, does anyone have any ideas what may be going on? Peter Bradshaw
Sat, 31 Jan 2004 08:22:40 GMT

Patrick Loga #2 / 6	SELECT works, but SELECT DISTINCT fails What's the datatype of Lap.Lab? Have you compared the execution plans? -- Patrick Logan, MCSD Opinions expressed are my own and not necessarily those of my employer.
Sat, 31 Jan 2004 23:47:51 GMT

Peter Bradsha #3 / 6	SELECT works, but SELECT DISTINCT fails You have opened a new door for me to investigate. I have been hammering on the theory that it is the number of records that is making it fail, since it worked with a small set of records. But I'll bet there is a single bad record in that bigger table that is making it fail - I just haven't seen it because there are so many. Probably too long. To answer your question... Lab is a Memo field in an Access table named LAP. Maybe using DISTINCT requires strings under 256 characters. Thanks for your reply. Peter Bradshaw Quote: > What's the datatype of Lap.Lab? Have you compared the execution plans? > -- > Patrick Logan, MCSD > Opinions expressed are my own and not necessarily those of my employer.
Sun, 01 Feb 2004 10:04:25 GMT

Douglas J. Steel #4 / 6	SELECT works, but SELECT DISTINCT fails I believe you've hit on it: I don't think Memo fields can be used with the DISTINCT predicate, just as they can't be used in UNION queries. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele Quote: > You have opened a new door for me to investigate. I have been hammering on > the theory that it is the number of records that is making it fail, since > it worked with a small set of records. But I'll bet there is a single bad > record in that bigger table that is making it fail - I just haven't seen > it because there are so many. Probably too long. > To answer your question... Lab is a Memo field in an Access table named LAP. > Maybe using DISTINCT requires strings under 256 characters. > Thanks for your reply. > Peter Bradshaw > > What's the datatype of Lap.Lab? Have you compared the execution plans? > > -- > > Patrick Logan, MCSD > > Opinions expressed are my own and not necessarily those of my employer.
Mon, 02 Feb 2004 06:40:13 GMT

Microsoft New #5 / 6	SELECT works, but SELECT DISTINCT fails You can also try .Open "SELECT Lab FROM LAP GROUP BY Lab" It may be more forgiving. Quote: > You have opened a new door for me to investigate. I have been hammering on > the theory that it is the number of records that is making it fail, since > it worked with a small set of records. But I'll bet there is a single bad > record in that bigger table that is making it fail - I just haven't seen > it because there are so many. Probably too long. > To answer your question... Lab is a Memo field in an Access table named LAP. > Maybe using DISTINCT requires strings under 256 characters. > Thanks for your reply. > Peter Bradshaw > > What's the datatype of Lap.Lab? Have you compared the execution plans? > > -- > > Patrick Logan, MCSD > > Opinions expressed are my own and not necessarily those of my employer.
Wed, 04 Feb 2004 16:10:35 GMT

Peter Bradsha #6 / 6	SELECT works, but SELECT DISTINCT fails As follow-up - it turns out it was the number of records that was making DISTINCT fail. I have already worked around it by using .Open "SELECT Lab FROM LAP ORDER BY Lab" then flipping through the sorted records and deleting any duplicates. I'll be interested to try that GROUP BY, though. Thanks for your reply. Peter Bradshaw Quote: > You can also try > .Open "SELECT Lab FROM LAP GROUP BY Lab" > It may be more forgiving. > > You have opened a new door for me to investigate. I have been hammering on > > the theory that it is the number of records that is making it fail, > since > > it worked with a small set of records. But I'll bet there is a single bad > > record in that bigger table that is making it fail - I just haven't seen > > it because there are so many. Probably too long. > > To answer your question... Lab is a Memo field in an Access table named > LAP. > > Maybe using DISTINCT requires strings under 256 characters. > > Thanks for your reply. > > Peter Bradshaw > > > What's the datatype of Lap.Lab? Have you compared the execution plans? > > > -- > > > Patrick Logan, MCSD > > > Opinions expressed are my own and not necessarily those of my employer.
Thu, 05 Feb 2004 10:35:57 GMT

Page 1 of 1

[ 6 post ]