473,287 Members | 3,319 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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?

Aug 4 '06 #1
11 3501
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
Aug 4 '06 #2
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 ***
Aug 4 '06 #3

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.

Aug 4 '06 #4
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 ***
Aug 4 '06 #5
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 ***
Aug 4 '06 #6
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
Aug 4 '06 #7
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
Aug 4 '06 #8
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
Aug 4 '06 #9
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
Aug 4 '06 #10
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


Aug 4 '06 #11
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
Aug 4 '06 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
by: Lee Rouse | last post by:
Hello all, This is going to be a rather lengthy "question". I have an Access 2k database, separated front end/back end. Front end copies are on about 30 workstations and used frequently during...
4
by: James P. | last post by:
Hello there, I have a bound-form using Navigator so that the user can move back and forth to update record in the form. Every time a record was modified and the user clicks the Navigator to...
6
by: MS | last post by:
Access 97 here. I want a simple way to "lock" certain records on a form. Some records remain "live" until all data is available which happens over time. When all the fields are complete, I want...
2
by: Ian Baker | last post by:
We have developed an Access/Jet database (2000, XP & 2003 versions) that has been used by clients all around the world for several years and is extremely robust with 55 tables, 172 hard stored...
4
by: jg1130 | last post by:
Greetings all, Here's my problem... I've built a small Access database that I placed on my server. I have one workstation connected to the database. The folder that the database is in on...
4
by: corey11 | last post by:
I'm a very low-level developer with limited VB knowledge but nonetheless was able to put together a very user-friendly and extremely helpful Access 97 database for my company some 10 years back. We...
6
by: jsacrey | last post by:
Hello everybody, I've got a bit of a situation that I could use some guidance with if possible. I work for an auditing firm where my users audit electronic shipping data for customers to see if...
6
by: Mark | last post by:
Currently using MS Access 2000 and SQL Server Express. Using the current DAO OpenRecordset code listed below, however I keep getting the error message.... 3254: ODBC --Cannot lock all records ...
2
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.