Populate Form Text Box with the following

[tredd68 via AccessMonster.com]

I created a pass through query as follows and it works within access.

SELECT rowcnt FROM sysindexes WHERE object_name(id) = 'tablename' and indid <
2

This returns the row count quickly since it is stored already in the table
definition in SQL. I need to know how you can return the result of this in a
text box on a form.

I do not have the option of using SELECT Count(*) FROM sql table b/c the
number of records is: 19 million and it takes a while.

 

[Jeff Boyce]

I don't know how fast it would be (so you'll need to test it), but one
approach might be to use the DLookup() function against that passthrough
query to retrieve the value. You might be able to set the control source of
the textbox to the function (i.e. "=DLook...")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tredd68 via AccessMonster.com]

Thanks, I will try that!

I don't know how fast it would be (so you'll need to test it), but one
approach might be to use the DLookup() function against that passthrough
query to retrieve the value. You might be able to set the control source of
the textbox to the function (i.e. "=DLook...")

Regards

Jeff Boyce
Microsoft Office/Access MVP

I created a pass through query as follows and it works within access.

[quoted text clipped - 9 lines]

I do not have the option of using SELECT Count(*) FROM sql table b/c the
number of records is: 19 million and it takes a while.

 

[tredd68 via AccessMonster.com]

You are the man! That worked and returns the result just as quick as SQL. Thx!

I don't know how fast it would be (so you'll need to test it), but one
approach might be to use the DLookup() function against that passthrough
query to retrieve the value. You might be able to set the control source of
the textbox to the function (i.e. "=DLook...")

Regards

Jeff Boyce
Microsoft Office/Access MVP

I created a pass through query as follows and it works within access.

[quoted text clipped - 9 lines]

I do not have the option of using SELECT Count(*) FROM sql table b/c the
number of records is: 19 million and it takes a while.

 

[Jeff Boyce]

You are welcome.

Jeff

You are the man! That worked and returns the result just as quick as SQL.
Thx!

I don't know how fast it would be (so you'll need to test it), but one
approach might be to use the DLookup() function against that passthrough
query to retrieve the value. You might be able to set the control source
of
the textbox to the function (i.e. "=DLook...")

Regards

Jeff Boyce
Microsoft Office/Access MVP

I created a pass through query as follows and it works within access.

[quoted text clipped - 9 lines]

I do not have the option of using SELECT Count(*) FROM sql table b/c the
number of records is: 19 million and it takes a while.