Help with MaxRecords Function 1

[throwdini]

I am trying to use MS Access to create a query / recordset that will use the maxrecords fucntion. I want to limit the number of records returned to a number given by the user. I know how to pass the user's number of records, but I have no idea how or why I cna't get the MaxRecords function to work.

Any help would be greatly appreciated.
Thanks
Jay

 

[THoey]

Are you talking about /looking for the TOP clause? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[throwdini]

I'm not sure what a TOP clause is. Basically what I'm looking for is something like this:

dim x,y as variant
dim z as interger

z = inputbox("Please enter a number between 1 - 100&quot

set x = currentdb.createquerydef ("&quot
x.sql = "INSERT INTO TableB SELECT TableA.* FROM TableA"
x.maxrecords = z
x.execute

When I run this it seems to ignore the maxrecords statement and inserts all records. It also doesn't work if I do something like this...

dim x as variant
dim y as string
dim z as interger

z = inputbox("Please enter a number between 1 - 100&quot
y = inputbox("Enter File name&quot

set x = currentdb.createquerydef ("temp&quot
x.sql = "select * from tableA"
x.maxrecords = z
docmd.transfertext actextdelim,,"temp","c:\" & y & ".txt"

This will create the text file but also not be limited to the maxrecords value. I have also tried both ways a hardwired maxrecords value, and have the same results.

The only way have have gotten it to do what I want (sort of) is to do this...

dim w, x as variant
dim y, z as interger

y = InputBox("Number of records?", "Toolbox&quot

Set x = CurrentDb.CreateQueryDef("&quot
x.sql = "select * from TableA"
x.returnsrecords = True

z = 0
Set w = x.openrecordset()
Debug.Print "Query Results:"
With w
Do While Not .EOF
z = z + 1
If z < y + 1 Then
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Else:
GoTo ex_sub
End If
Loop
.Close
End With

This will print the correct number of record in the immediate window, but that isn't exactly useful for me, and in application this process will be handling around 10,000 records. The immediate window seems to only store about 200 lines.

Sorry this is so long but help would be great!
Jay

 

[tcsbiz]

ThrowDini,

This a a link that explains how to use MaxRecords and TopValues functions and when to use them.

To quote the website directly:

&quot;The MaxRecords property sets or returns the maximum number of records of a query on an ODBC table and is useful in situations where limited client-resources prohibit management of large numbers of records from ODBC tables. The TopValues property is useful when you want to return certain records based on a specified percentage. MaxRecords is only for views in an Access project or queries in an ODBC data sources, not for queries on tables contained in the database, and is also available in Visual Basic.

The TopValues property returns a specified number of records or a percentage of records that meet the criteria you specify in the design of a query on any table. TopValues is not available in Visual Basic but can be used in tables contained in the database or ODBC tables. To set the number of records, TopValues requires a percent sign (%).

TopValues can return a number or a percent, whereas MaxRecords sets or returns only a number of records.&quot;

http://support.microsoft.com/default.aspx?scid=kb;en-us;207621

 

[throwdini]

I'll check it out.
Thanks