Need error message if query results in more than 65536 recs

[Legacy 3234]

I am pretty good at Excel VBA but not too well versed in Access vba.

I have a form. I set up a button on the form (using the wizard) to run a macro.

The first thing the macro does is run a make-table query.

If the query is going to paste more than 65536 records to the table, I need to display a message and exit sub. I've searched far & wide and have no idea where to begin. Can anyone offer some advice?

 

[Norie]

Why do you need this error message?

Are you exporting to Excel?

 

[Legacy 3234]

Yes, the table is exported to Excel after it's created. If it's too many records the whole program crashes. I could leave the "you are about to paste xxx records to the table" message on but I can't depend on my users to know enough to stop if it's too many.

 

[Norie]

Why are you exporting to Excel?

Is there something you can do there that you can't in Access?

 

[Legacy 3234]

Perhaps whatever they're going to do with the data could be done in Access if they knew how, but people in my department/company (including me) don't know how to use Access to do the things we do in Excel.

 

[Norie]

What are these things?

 

[giacomo]

You can use a Top N query to limit the number of rows returned by a query. Take a look at this post:

http://www.mrexcel.com/board2/viewtopic.php?t=292101

hth,
Giacomo

 

[Legacy 3234]

Is there anyone here willing to tell me if what I asked for help with is possible or not and, if it is, point me in the right direction on how to do it?

 

[Legacy 3234]

Thank you, Giacomo. I'm about to leave for the weekend but will check that out first thing Monday.