访问和SQL后端 - 移动到下一个记录 - HHHELLLPPP！ [英] Access and SQL Backend - moving to next record - HHHELLLPPP!
问题描述
我使用Access 2000作为前端，MS SQL 2000作为后端。
我有一个记录表单，我使用类似的东西设置：
strSQL =" SELECT * FROM dbo_WBACCT WHERE
（（（dbo_WBACCT.ACCOUNT）=''" 423456"''））;"
Me.RecordSource = strSQL
显然我一次只想要一条记录，因为数据库非常好。
部分数据如下：
账户名称
2344566史密斯
2344578琼斯
2344582戴维斯
数据按帐号排序。如果我正在查看帐号＃
2344578，我想点击一个按钮查看下一个帐号，该例子中的
是2344582.
按钮的代码必须找到下一个帐号＃2344582所以
它可以再次更改表单的记录源。
如何有效地找到下一个帐号。使用ADO的'
rst。查找速度太慢，无法通过整个记录集来查找
帐户＃2344578，然后执行movenext。这需要花费一分钟的时间。
看起来很简单，但我只是在旋转我的车轮。您可能会问为什么一个
想要查看下一个帐号。答案是因为
这是一个想要在街上看到下一个人的实用程序。
下一个帐号＃是前一个帐号＃的邻居。专家说什么
？
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?
推荐答案
scsTiger写道：
scsTiger wrote:
我使用Access 2000作为前端，MS SQL 2000作为
后端。
我有一条记录我使用以下内容设置的表格：
strSQL =" SELECT * FROM dbo_WBACCT WHERE
（（（dbo_WBACCT.ACCOUNT）=''" 423456" ;''））;"
Me.RecordSource = strSQL
显然我一次只想要一条记录，因为数据库是
非常大。
部分数据如下：
账户名称
2344566 Smith
2344578 Jones
2344582 Davis
数据按帐号排序。如果我正在查看帐号＃
2344578，我想点击一个按钮查看下一个帐号
，例如2344582.
按钮的代码必须找到下一个帐号＃2344582
所以它可以再次更改表单的记录源。
如何有效地找到下一个帐号。使用ADO的'
rst。查找速度太慢，无法通过整个记录集来查找
帐户＃2344578，然后执行movenext。这需要花费一分钟的时间。
看起来很简单，但我只是在旋转我的车轮。您可能会问为什么一个
想要查看下一个帐号。答案是因为
这是一个想要在街上看到下一个人的实用程序。
下一个帐号＃是前一个帐号＃的邻居。专家说什么
？
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?
使用RowSource填充ListBox或ComboBox ...
来自dbo_WBACCT的SELECT DISTINCT帐户
....然后使用该列表选择要检索的帐户数据。
-
Rick Brandt，Microsoft Access MVP
电子邮件（视情况而定）至...
在Hunter dot com的RBrandt
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
你还没有清楚指定你的表格是
表格的记录来源（听起来可能是这样）或者你的表格是一个搜索表格
你在哪里输入一个帐号和搜索记录。
如果你想要的只是表格中的下一条记录，最简单的事情是在bql server后端创建一个视图
它将包含所需排序顺序所需的数据集
。然后，您可以使用
ODBC链接到该视图。然后只需使用表单上的导航按钮来导航
记录集。
Access中的等效方法是检索你的列表
所需帐户并将该列表存储在表中。将该表用作表单的
记录源。然后创建一个子表单，使用主表
作为其记录源，并使用Account
字段将表单链接到子表单。当您遍历主窗体时，相应的记录
将显示在子窗体中 - 就像主/细节设置一样。
Rich
***通过开发人员指南 http://www.developersdex.com ***
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 ***
Rick Brandt写道：
Rick Brandt wrote:
scsTiger写道：
scsTiger wrote:
我使用Access 2000作为前端，MS SQL 2000作为
后端。
我有一个记录表单，我使用类似的东西设置：
strSQL =" SELECT * FROM dbo_WBACCT WHERE
（（（dbo_WBACCT。 ACCOUNT）=''" 423456"''））;"
Me.RecordSource = strSQL
显然我一次只想要一条记录因为数据库非常大。
有些数据看起来像这样：
账户名称
2344566史密斯
2344578琼斯
2344582戴维斯
数据按帐号排序。如果我正在查看帐号＃
2344578，我想点击一个按钮查看下一个帐号
，例如2344582.
按钮的代码必须找到下一个帐号＃2344582
所以它可以再次更改表单的记录源。
如何有效地找到下一个帐号。使用ADO的'
rst。查找速度太慢，无法通过整个记录集来查找
帐户＃2344578，然后执行movenext。这需要花费一分钟的时间。
看起来很简单，但我只是在旋转我的车轮。您可能会问为什么一个
想要查看下一个帐号。答案是因为
这是一个想要在街上看到下一个人的实用程序。
下一个帐号＃是前一个帐号＃的邻居。专家说什么
？
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?
使用RowSource填充ListBox或ComboBox ...
来自dbo_WBACCT的SELECT DISTINCT帐户
...然后使用该列表选择要检索的帐户数据。
-
Rick Brandt，Microsoft Access MVP
电子邮件（视情况而定）至...
在Hunter dot的RBrandt com
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，
感谢您的帮助。 db_WBACCT非常庞大。这会比以编程方式打开记录集并使用FIND方法更快。
此外，对于列表框中的数据，您建议采用何种方式
以编程方式检索下一个帐号？让用户
选择下一个帐户＃而不是点击某种下一个按钮？
抱歉我的困惑。
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.
这篇关于访问和SQL后端 - 移动到下一个记录 - HHHELLLPPP！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
