Selecting first 10 records (AccessXP)

Topic

New Reply

Hello. I have some code that selects and copies the results of a query as follows:
RunCommand acCmdSelectAllRecords
RunCommand acCmdCopy
Is a simple way to just copy the first 10 lines from the query?

I need to do this programmatically since I have hundred’s of queries to run through.

Thanks!

Reply | Quote

Replies

Welcome to Woody’s Lounge!

I don’t see an easy way to do that for queries.
You can create a top 10 query by setting the Top Values property of the query to 10 (and making sure that the query is sorted the way you want).

In a form based on a query, you can set the SelTop property to 1 and the SelHeight property to 10. This selects the first 10 records in the form.

Reply | Quote

Hi Twinky!

Of course Hans is correct. He’s one of the most knowledgeable and helpful people around. But since you’re new to the forum, I wonder if you perhaps haven’t phrased your question correctly or supplied sufficient detail. You haven’t, for example, told us what you want to do with the data once you’ve selected it.

As an exercise, I’ve assumed you might want to move the data elsewhere; perhaps even into a new table. Here’s some simple code that runs through all the queries in the database and for each query, creates a new table holding the top ten records.
I’ve deliberately left out docmd.setwarnings false/true for debugging purposes and to allow you some control. The code would also need better error trapping in case the table already existed. Is this any help?

JulesG

Private Sub cmdCopyQueries_Click()
On Error GoTo aEscape

Dim dbCurrent As DAO.Database
Dim strQueryName As String
Dim strSQL As String
Dim qdf As QueryDef

Set dbCurrent = CurrentDb

For Each qdf In dbCurrent.QueryDefs
strQueryName = qdf.Name
strSQL = “SELECT TOP 10 ” & strQueryName & “.* INTO tbl” & strQueryName & ” FROM ” & strQueryName & “;”

DoCmd.RunSQL strSQL

Next qdf

Exit Sub
aEscape:
MsgBox Err.Description
End Sub

Reply | Quote

Just in case there are queries whose name contains spaces or other “unusual” characters, you should put square brackets around the query name and table name:

strSQL = "SELECT TOP 10 [" & strQueryName & "].* INTO [tbl" & strQueryName & "] FROM [" & strQueryName & "]"

Reply | Quote

Thanks Hans and Jules. That worked brilliantly.

Reply | Quote