Access and SQL Backend - moving to next record - HHHELLLPPP!

[scsTiger]

I am using Access 2000 as the front end and MS SQL 2000 as the backend.

I have a one record form that I set using something like:

strSQL = "SELECT * FROM dbo_WBACCT WHERE
(((dbo_WBACCT.ACCOUNT)='"423456"'));"
Me.RecordSource = strSQL

Obviously I only want one record at a time because the database is very
big.

Some of the data looks like this:

ACCOUNT Name
2344566 Smith
2344578 Jones
2344582 Davis

The data is sorted on account number. If I am looking at account #
2344578, I want to click a button to view the next account number which
in the example is 2344582.

The code for the button must find that the next account # is 2344582 so
it can change the recordsource for the form again.

How can I efficiently find this next account number. Using ADO's
rst.Find is too slow to trudge through the whole recordset to find
account # 2344578 and then doing a movenext. It takes a could of
minutes.

Seems simple, but I am only spinning my wheels. You might ask why one
would want to look at the next account number. The answer is because
this is for a utility that wants to see the next person on the street.
The next account # is the neighbor of the previous account #. What do
the experts say?

[Rick Brandt]

scsTiger wrote:

I am using Access 2000 as the front end and MS SQL 2000 as the
backend.

I have a one record form that I set using something like:

strSQL = "SELECT * FROM dbo_WBACCT WHERE
(((dbo_WBACCT.ACCOUNT)='"423456"'));"
Me.RecordSource = strSQL

Obviously I only want one record at a time because the database is
very big.

Some of the data looks like this:

ACCOUNT Name
2344566 Smith
2344578 Jones
2344582 Davis

The data is sorted on account number. If I am looking at account #
2344578, I want to click a button to view the next account number
which in the example is 2344582.

The code for the button must find that the next account # is 2344582
so it can change the recordsource for the form again.

How can I efficiently find this next account number. Using ADO's
rst.Find is too slow to trudge through the whole recordset to find
account # 2344578 and then doing a movenext. It takes a could of
minutes.

Seems simple, but I am only spinning my wheels. You might ask why one
would want to look at the next account number. The answer is because
this is for a utility that wants to see the next person on the street.
The next account # is the neighbor of the previous account #. What
do the experts say?

Populate a ListBox or ComboBox with a RowSource of...

SELECT DISTINCT ACCOUNT FROM dbo_WBACCT

....Then use that list to choose the account data to retrieve.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Rich P]

You have not clearly specified if your table is the recordsource of a
form (which is sounds like it could be) or if your form is a search form
where you enter an account number and search for a record.

If all you want is the next record in the table, the easiest thing is to
create a view in the sql server backend which would contain the dataset
you need in the desired sort order. Then you can link to that view with
ODBC. Then just use navigation buttons on the form to navigate the
recordset.

An equivalent method in Access would be to retrieve a list of your
desired accounts and store that list in a table. Use that table as the
recordsource for a form. Then create a subform that uses the main table
as its recordsource and link the form to the subform using the Account
field. When you iterate through the mainform the corresponding record
will show up in the subform - Like a Master/Detail setup.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[scsTiger]

Rick Brandt wrote:

scsTiger wrote:

I am using Access 2000 as the front end and MS SQL 2000 as the
backend.

I have a one record form that I set using something like:

strSQL = "SELECT * FROM dbo_WBACCT WHERE
(((dbo_WBACCT.ACCOUNT)='"423456"'));"
Me.RecordSource = strSQL

Obviously I only want one record at a time because the database is
very big.

Some of the data looks like this:

ACCOUNT Name
2344566 Smith
2344578 Jones
2344582 Davis

The data is sorted on account number. If I am looking at account #
2344578, I want to click a button to view the next account number
which in the example is 2344582.

The code for the button must find that the next account # is 2344582
so it can change the recordsource for the form again.

How can I efficiently find this next account number. Using ADO's
rst.Find is too slow to trudge through the whole recordset to find
account # 2344578 and then doing a movenext. It takes a could of
minutes.

Seems simple, but I am only spinning my wheels. You might ask why one
would want to look at the next account number. The answer is because
this is for a utility that wants to see the next person on the street.
The next account # is the neighbor of the previous account #. What
do the experts say?

Populate a ListBox or ComboBox with a RowSource of...

SELECT DISTINCT ACCOUNT FROM dbo_WBACCT

...Then use that list to choose the account data to retrieve.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick,

Thanks for your help. db_WBACCT is huge. Will this be quicker than
just programmatically opening a recordset and using the FIND method.
Also, with the data in the list box, what way are you suggestting
programmatically to retrieve that next account number? Having the user
choose the next account # instead of hitting some kind of next button?
Sorry for my confusion.

[scsTiger]

Thanks Rich. Yes I am talking about a recordsource of a form. I am
grabbing only one record from the SQL backend due to the large amount
of records. I use a separate popup search form to find the account
they initially want to look at. It then sets the recordsource of the
main form. That part works fine. The problem comes in when they want
to step to the next account. I will first try the view in the sql
backend method you describe. I may need more detail on that one as I
am (obviously) new to SQL server.


Rich P wrote:

You have not clearly specified if your table is the recordsource of a
form (which is sounds like it could be) or if your form is a search form
where you enter an account number and search for a record.

If all you want is the next record in the table, the easiest thing is to
create a view in the sql server backend which would contain the dataset
you need in the desired sort order. Then you can link to that view with
ODBC. Then just use navigation buttons on the form to navigate the
recordset.

An equivalent method in Access would be to retrieve a list of your
desired accounts and store that list in a table. Use that table as the
recordsource for a form. Then create a subform that uses the main table
as its recordsource and link the form to the subform using the Account
field. When you iterate through the mainform the corresponding record
will show up in the subform - Like a Master/Detail setup.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

For help with Sql Server issues you can go to the Sql Tab in
DeveloperDex (www.devdex.com) or go to the MSDN Newsgroup
(http://msdn.microsoft.com/newsgroups/)

At the MSDN NG go to Enterprise Development then on the dropdown go to
Sql Server. On the next dropdown go to sqlserver.programming.

When you post a question at the MSDN NG you will be asked to register.
Just put in any old email and password. That will be your ID and
password (its free - all microsoft).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Albert D. Kallal]

Interesting problem...

You could try for the next buttion:
strSQL = "SELECT top 1 from FROM dbo_WBACCT WHERE " & _
ACCOUNT " & """" & me!Account & """" & _
"order by Account"

Me.RecordSource = strSQL

You would change the above to the reverse for the prevous arrow.....

strSQL = "SELECT top 1 from FROM dbo_WBACCT WHERE " & _
ACCOUNT < " & """" & me!Account & """" & _
"order by Account dsnd"

The above would fetch ONLY one record .and the record would be the next
account in the file....

It should work, and if there is 1, or million records in the file..the
performance should be instant....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[scsTiger]

Albert,

Thanks. I'm getting a run time error. Seems its choking on the "TOP
1" portion of the sql statement. If I change top 1 to * , I get no
error. Any suggestions?

Thanks,
Jeff

For some reason, Access doesn't like the top 1 part of it.

Albert D. Kallal wrote:

Interesting problem...

You could try for the next buttion:
strSQL = "SELECT top 1 from FROM dbo_WBACCT WHERE " & _
ACCOUNT " & """" & me!Account & """" & _
"order by Account"

Me.RecordSource = strSQL

You would change the above to the reverse for the prevous arrow.....

strSQL = "SELECT top 1 from FROM dbo_WBACCT WHERE " & _
ACCOUNT < " & """" & me!Account & """" & _
"order by Account dsnd"

The above would fetch ONLY one record .and the record would be the next
account in the file....

It should work, and if there is 1, or million records in the file..the
performance should be instant....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Albert D. Kallal]

Well, we need some fields!!!

Try

strSQL = "SELECT top 1 * from FROM dbo_WBACCT WHERE " & _
ACCOUNT " & """" & me!Account & """" & _
"order by Account"

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[scsTiger]

Albert,

This worked perfectly. Sorry I did not catch the missing *. Thanks to
you and everyone else that was so quick to help.

Jeff

Albert D. Kallal wrote:

Well, we need some fields!!!

Try

strSQL = "SELECT top 1 * from FROM dbo_WBACCT WHERE " & _
ACCOUNT " & """" & me!Account & """" & _
"order by Account"

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Rick Brandt]

scsTiger wrote:

Rick,

Thanks for your help. db_WBACCT is huge. Will this be quicker than
just programmatically opening a recordset and using the FIND method.
Also, with the data in the list box, what way are you suggestting
programmatically to retrieve that next account number? Having the
user choose the next account # instead of hitting some kind of next
button? Sorry for my confusion.

Huge, perhaps but how long is the list of distinct accounts? Still huge?
Because that query will be run on the server whereas your find is probably being
processed locally.

Generally speaking a rowset consisting of a single field would have to be
awfully tall to be "large" in terms of data retrieval.

I suppose you could fetch 10 or so at a time by using the TOP predicate.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[scsTiger]

Rick,

Thanks for your reply, Albert gave me the following SQL statement to
return the next account:

strSQL = "SELECT top 1 * from FROM dbo_WBACCT WHERE " & _
ACCOUNT " & """" & me!Account & """" & _
"order by Account"

Rick Brandt wrote:

scsTiger wrote:

Rick,

Thanks for your help. db_WBACCT is huge. Will this be quicker than
just programmatically opening a recordset and using the FIND method.
Also, with the data in the list box, what way are you suggestting
programmatically to retrieve that next account number? Having the
user choose the next account # instead of hitting some kind of next
button? Sorry for my confusion.

Huge, perhaps but how long is the list of distinct accounts? Still huge?
Because that query will be run on the server whereas your find is probably being
processed locally.

Generally speaking a rowset consisting of a single field would have to be
awfully tall to be "large" in terms of data retrieval.

I suppose you could fetch 10 or so at a time by using the TOP predicate.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com