using * only retrieves 1 record, please look

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

using * only retrieves 1 record, please look

Author

Topic

bferriter
Starting Member

34 Posts

Posted - 2005-04-20 : 12:18:45

I have an access 2000 client front end with a 2000 microsoft SQL server behind it. I am having trouble with using recordsets in the vb code behind the forms. This is an example of what I have:

Private Sub Foo()

Dim dbs as Database
Dim rsTran as Recordset
Dim strsql as String

set dbs = CurrentDb
set rsTran = Nothing

strsql = ""
strsql = "select * from [stopoff]"

rsTran = dbs.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges, dbOptimistic)

End Sub

Though this will run, the stopoff table has 7162 records in it, if i put this in the code:

msgbox rsTran.recordcount

after I open the recordset, I always get a count of 1. I have tried this with other tables that are linked to the database, and they also return a count of 1.. I do not understand why it would not return all of the records. If i run this query in the analyzer, it correctly returns 7162 records.

Any ideas?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-20 : 12:27:40

Do not use *

List the field names you wish to retrive.

Select field1,fileld2
from yourtable

Jim
Users <> Logic

bferriter
Starting Member

34 Posts

Posted - 2005-04-20 : 12:30:58

I changed the string to:

"SELECT [qservicetype] from [stopoff]"

and it still only pull's the first record. With no "Where" claus, this should pull all the records..

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-20 : 12:42:58

Lets see something

Try

select top 100 percent qservicetype from stopoff

Jim
Users <> Logic

bferriter
Starting Member

34 Posts

Posted - 2005-04-20 : 12:52:18

same. :(

i also tried:

select top 100 percent from stopoff
select top 3 from stopoff
select top 3 qservicetype from stopoff

the linked tables have indexes selected correctly... i tried to view that one record it keeps retrieving by "msgbox rsTran!qservicetype", that still works

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-20 : 13:08:11

Don't confuse the value that the Count() property is returning with the actual size of the recordset itself. Your assumption is incorrect; all of the rows are returned, it is just that the recordset object's Count() property isn't always accurate until you have reached the EOF, since the cursor is declared on the server and the client has no idea how many rows are about to be returned. Be sure to read up carefully about how ADO Recordsets work.

If the record count is important, but not the records themselves, do a SELECT COUNT(*) and return just 1 value to the client instead of all rows. If the records themselves are important, then return the count after they all are processed.

- Jeff

bferriter
Starting Member

34 Posts

Posted - 2005-04-20 : 13:12:53

I want the query to return more than one row.

the main transaction is unique, but their can be multiple stopoffs per transaction. the link is a field in the stopoff table that has the main transactions invoice id. if i run a query like this:

"select * from stopoff where qlinkinvoice = '" & me.invoice "'"

this should return 3 entries (i verified this by looking at the table in datasheet form, and the subform on the form this record is on correctly displays the three stopoffs).. but when I try to select the record count it only shows 1...

You are correct Jeff, there are hidden records there. I guess I cannot rely on RecordCount() to correctly display the return of the query... Now I can move on to using it.

Thanks for your Help, Jim and Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources