Supress dialog box when MS query returns more than 65536 rec's

[Asha]

When the query from Microsft query builder executes and returns data which
exceeds the sheet limit (65536 rows), a dialog box prompting (ok/cancel) gets
displayed. Is it possible to supress this dialog box, either through settings
or through VBA code? either ways plz suggest the way to do it.

[K Dales]

If you don't care about the records you might lose, the best way to do this
is to limit your query so it can only return 65535 rows (leaving room for a
header row in this case); you can do that by adding a TOP n clause to the SQL
statement.

Make sure you have your query working well first, since editing the SQL
statement can make it impossible to visually represent the query in the
MSQuery grid. Then press the SQL button and change SELECT to read SELECT TOP
65535, as in the example:

SELECT TOP 65535 PHONEBOOK.NAME, PHONEBOOK.ADDRESS, PHONEBOOK.PHONE_NO FROM
PHONEBOOK WHERE PHONEBOOK.CITY = 'PHILADELPHIA'

If you can't lose those excess records, forget MSQuery and use ADO instead
to retrieve the full recordset inside VBA - then you can move the excess
records onto a new sheet or whatever you need to do.
--
- K Dales


"Asha" wrote:

> When the query from Microsft query builder executes and returns data which
> exceeds the sheet limit (65536 rows), a dialog box prompting (ok/cancel) gets
> displayed. Is it possible to supress this dialog box, either through settings
> or through VBA code? either ways plz suggest the way to do it.

[hoppermr]

Quick and dirty solution that might work:-

Application.DisplayAlerts = False



"K Dales" wrote:

> If you don't care about the records you might lose, the best way to do this
> is to limit your query so it can only return 65535 rows (leaving room for a
> header row in this case); you can do that by adding a TOP n clause to the SQL
> statement.
>
> Make sure you have your query working well first, since editing the SQL
> statement can make it impossible to visually represent the query in the
> MSQuery grid. Then press the SQL button and change SELECT to read SELECT TOP
> 65535, as in the example:
>
> SELECT TOP 65535 PHONEBOOK.NAME, PHONEBOOK.ADDRESS, PHONEBOOK.PHONE_NO FROM
> PHONEBOOK WHERE PHONEBOOK.CITY = 'PHILADELPHIA'
>
> If you can't lose those excess records, forget MSQuery and use ADO instead
> to retrieve the full recordset inside VBA - then you can move the excess
> records onto a new sheet or whatever you need to do.
> --
> - K Dales
>
>
> "Asha" wrote:
>
> > When the query from Microsft query builder executes and returns data which
> > exceeds the sheet limit (65536 rows), a dialog box prompting (ok/cancel) gets
> > displayed. Is it possible to supress this dialog box, either through settings
> > or through VBA code? either ways plz suggest the way to do it.