Does the data object have a maximum query length?

Question

I have an old vb6 program which queries an access 2000 database. I have a fairly long query which looks something like this:

Select * from table where key in ( 0, 1, 2, 3, 4, 5, 6, 7, 11, 12, 13, 14, 15, 19, 20, 21, 24, 27, 29, 30, 35, 38, 39, 40, 42, 43, 44, 46, 47, 49, 50, 53, 56, 59, 60, 61, 63, 64, 65, 66, 67, 68, 72, 76, 80, 84, 86, 89, 90, 91, 93, 94, 98, 99, 10041, 10042, 10045, 10046, 10047, 10049, 10057, 10060, 10089, 32200, 32202, 32203, 32204, 32205, 32207, 32214, 32245, 32303, 32314, 32403, 32405, 32414, 32415, 32503, 32703, 32803, 32903, 33003, 33014, 33102, 33103, 33303, 33403, 33405, 33601, 33603, 33604, 33614, 33705, 33714, 33901, 33903, 33914, 34001, 34105, 34114, 34203, 34303, 34401, 34501, 34601, 34603, 34604, 34605, 34803, 41001, 41005, 41007, 41013, 42001, 42005, 42007, 42013, 43001, 43002, 44001, 44007, 46001, 46007, 99999, 9999999)

However, when I look at the RecordSource of the data object, it seems that the query is being truncated to this (which is obviously not syntactically valid and throws an error):

Select * from table where key in ( 0, 1, 2, 3, 4, 5, 6, 7, 11, 12, 13, 14, 15, 19, 20, 21, 24, 27, 29, 30, 35, 38, 39, 40, 42, 43, 44, 46, 47, 49, 50, 53, 56, 59, 60, 61, 63, 64, 65, 66, 67, 68, 72, 76, 80, 84, 86, 89, 90, 91, 93, 94, 98, 99, 100

My data source looks like this:

 Begin VB.Data dtaList 
     Caption         =   "dtaList"
     Connect         =   "Access 2000;"
     DatabaseName    =   ""
     DefaultCursorType=   0  'DefaultCursor
     DefaultType     =   2  'UseODBC
     Exclusive       =   0   'False
     Height          =   345
     Left            =   960
     Options         =   0
     ReadOnly        =   0   'False
     RecordsetType   =   1  'Dynaset
     RecordSource    =   ""
     Top             =   4440
     Visible         =   0   'False
     Width           =   2295
  End

I've tried running the full query in the access database itself which works fine.

Is this a limitation in the VB.Data object, or is there some other explanation? Is there any way I can get around this issue?

Unfortunately I am unable to upgrade to a newer version of access.

VB6 and MS Access 2000 are both quite obsolete. I would probably try using ADO instead of VB.Data but I think you'll probably have to change a lot of stuff to make that work. I'm not sure what VB.DATA uses if it doesn't use some form of ADO. I'd also try to avoid needing/using a query like the one you listed. What do all those records have in common? Couldn't you use a boolean or integer field in the database instead to make those records share their commonality? It would simplify your query immensely. — HK1, Dec 04 '13 at 16:18
@HK1 Unfortunately the design of this application (almost 15 years old) makes a lot of those suggestions impossible. That list is generated dynamically based on the current user's permissions... — Mansfield, Dec 04 '13 at 16:30
Actually Jet 4.0 ("Access 2000") format MDBs are Windows-standard and far from obsolete. Support for them ships in Windows up through Windows 8.1! — Bob77, Dec 04 '13 at 18:00

Gord Thompson · Accepted Answer · 2013-12-04T16:43:33.067

The truncated version of the SQL statement you posted is 246 characters long, so it appears that something along the line is limiting the length of the SQL string to somewhere around 255 characters. As you have discovered by pasting the query into Access itself, the actual size limit of an Access query string is much larger (around 64,000 characters, I believe).

I remember running across a similar issue years ago but my problem was an INSERT statement that was writing some rather long strings to the database. The workaround in that case was to use a parameter query (which I realize, in hindsight, that I should have been using anyway). It greatly shortened the length of the SQL string because the parameters were passed separately. Unfortunately that workaround probably wouldn't help you because even if you dynamically created a parameterized version of the query it wouldn't be all that much shorter than the current SQL string.

Another workaround would be to write all of those numbers for the IN clause as rows in a temporary table named something like [inValues], and then use the query

SELECT [table].*
FROM
    [table]
    INNER JOIN
    [inValues]
        ON [table].[key] = [inValues].[key]

I tried something similar, but ran into other issues. Thanks for the suggestion though! — Mansfield, Dec 04 '13 at 17:39

Does the data object have a maximum query length?

1 Answers1