书签连续形式慢慢慢 [英] bookmark continuous form slow slow slow
问题描述
使用Access 2003和SQL Server 8.0版
大家好。创建了一个文本框，用户在其中键入了一个库存
数字，并将它们带到了令人兴奋的
表格中的库存编号。该表单基于sql server的链接表。这是
代码：
Dim rst作为DAO.Recordset
Dim InventoryItem As String
InventoryItem ="''" &安培; " TextBoxValue" &安培; "''"
Set rst = Me.RecordsetClone
rst.FindFirst" InventoryNumber =" &安培; InventoryItem
如果rst.NoMatch那么
MsgBox未找到记录
否则
Me.Bookmark = rst.Bookmark
结束如果
rst。关闭
非常简单的代码，它可以工作，但它很慢，很慢，慢。
连续表格上只有8500条记录。搜索越近，
数字越高越快。但是你去了一个数字
底部需要大约10秒。
当我通过sql表（ODBC）链接时，我给了它一把钥匙但是当我看到
表格的设计视图中的各个字段时，我可以看到密钥
，但字段上没有索引。我为sql server上的表创建了独特的聚类索引
。
我能做些什么来制作这个goto record。工作得更快。
任何帮助表示赞赏。
感谢光线
你说当你链接SQL表时，你给它一个键。你不应该b
必须这样做。如果你给你的SQL表一个主键，那么
链接过程应该自动选择它并为你的链表制作
键。如果系统提示您输入密钥，那么
就是错误的。确保你的表定义了主键。
有时，如果在SQL表上定义了索引，并且这些索引
名称按字母顺序排列你的主键，Access可以选择那些
那些，因此，提示你输入密钥，因为它选择了一个
非唯一索引。解决这个问题的一种方法是将主键重命名为
让它以aaaa开头。或者什么，以确保它是第一个。
其次，使用FindFirst很慢;但它不应该那么慢。通过使用保存为未绑定表单的表单，我有一个缓慢的
结果，我在表单打开后添加了一个记录集。我发现将表单保存为
a绑定表（甚至绑定到零记录集）会产生巨大差异，
将其保存为未绑定表单然后添加动态记录集。所以
确保您将表单保存为绑定表单，而不是作为未绑定的
表单。
最后，虽然你应该在使用FindFirst时获得更好的性能，但是你可能想要考虑一种不同的机制。你需要连续形式的8500条记录
？用户是否要滚动浏览8500条记录？
考虑用户实际需要/需要哪些记录并缩写您的
记录集来显示这些记录。可能是他们一次只需要一个，在
中，你可以改变记录集来显示那条记录。或者，如果他们需要一组记录，则需要
。但我不认为他们需要8500条记录连续形式的
！除非他们是速度读者，否则...... ;-)
HTH，
Neil
" eightman11" < rd ****** @ nooter.com写信息
新闻：10 ************************ ********** @ o6g2000h sd.googlegroups.com ...
使用Access 2003和sql server 8.0版
大家好。创建了一个文本框，用户在其中键入了一个库存
数字，并将它们带到了令人兴奋的
表格中的库存编号。该表单基于sql server的链接表。这是
代码：
Dim rst作为DAO.Recordset
Dim InventoryItem As String
InventoryItem ="''" &安培; " TextBoxValue" &安培; "''"
Set rst = Me.RecordsetClone
rst.FindFirst" InventoryNumber =" &安培; InventoryItem
如果rst.NoMatch那么
MsgBox未找到记录
否则
Me.Bookmark = rst.Bookmark
结束如果
rst。关闭
非常简单的代码，它可以工作，但它很慢，很慢，慢。
连续表格上只有8500条记录。搜索越近，
数字越高越快。但是你去了一个数字
底部需要大约10秒。
当我通过sql表（ODBC）链接时，我给了它一把钥匙但是当我看到
表格的设计视图中的各个字段时，我可以看到密钥
，但字段上没有索引。我为sql server上的表创建了独特的聚类索引
。
我能做些什么来制作这个goto record。工作更快。
任何帮助表示感谢。
感谢光线
< blockquote> 11月21日下午5:30，Neil < nos ... @nospam.netwrote：
你说当你链接SQL表时你给它一个键。你不应该b
必须这样做。如果你给你的SQL表一个主键，那么
链接过程应该自动选择它并为你的链表制作
键。如果系统提示您输入密钥，那么
就是错误的。确保你的表定义了主键。
有时，如果在SQL表上定义了索引，并且这些索引
名称按字母顺序排列你的主键，Access可以选择那些
那些，因此，提示你输入密钥，因为它选择了一个
非唯一索引。解决这个问题的一种方法是将主键重命名为
让它以aaaa开头。或者什么，以确保它是第一个。
其次，使用FindFirst isslow;但它不应该那么低。我曾经使用一个保存为未绑定表单的表单，然后我在表单打开后添加了一个记录集，从而获得了一次
结果。我发现将表单保存为
a绑定表（甚至绑定到零记录集）会产生巨大差异，
将其保存为未绑定表单然后添加动态记录集。所以
确保您将表单保存为绑定表单，而不是作为未绑定的
表单。
最后，虽然你应该在使用FindFirst时获得更好的性能，但是你可能想要考虑一种不同的机制。你需要连续形式的8500条记录
？用户是否要滚动浏览8500条记录？
考虑用户实际需要/需要哪些记录并缩写您的
记录集来显示这些记录。可能是他们一次只需要一个，在
中，你可以改变记录集来显示那条记录。或者，如果他们需要一组记录，则需要
。但我不认为他们需要8500条记录连续形式的
！除非他们是速度读者，否则...... ;-)
HTH，
Neil
" eightman11" < rdshu ... @ nooter.com写信息
新闻：10 *********************** *********** @ o6g2000h sd.googlegroups.com ...
使用Access 2003和sql server version 8.0
大家好。创建了一个文本框，用户在其中键入了一个库存
数字，并将它们带到了令人兴奋的
表格中的库存编号。该表单基于sql server的链接表。这是
代码：
Dim rst作为DAO.Recordset
Dim InventoryItem As字符串
InventoryItem ="''" &安培; " TextBoxValue" &安培; "''"
Set rst = Me.RecordsetClone
rst.FindFirst" InventoryNumber =" &安培; InventoryItem
如果rst.NoMatch那么
MsgBox未找到记录
否则
Me.Bookmark = rst.Bookmark
结束如果
rst.Close
非常简单的代码，它的工作原理，但它缓慢，缓慢。
连续表格上只有8500条记录。搜索越近，
数字越高越快。但是你去底部的数字是
需要大约10秒钟。
当我通过sql表（ODBC）链接时，我给了它一个键，但当我看到
时，各个字段在表的设计视图中我可以看到键
但字段上没有索引。我为sql server上的表创建了独特的聚集索引
。
我能做些什么来制作这个goto record工作得更快。
任何帮助表示赞赏。
thanks ray-隐藏引用的文字 -
- 显示引用的文字 -
------------------------------------ -------------------------------------------------- -------------------------------------------------- --------
嘿Neil：
你说我链接时不必给SQL表一个密钥br />
吗？在访问时，我做了新表 - 链接我做了一个ODBC链接到我的SQL表
。选择表后，我得到了一个访问屏幕，
的标题为选择唯一记录标识符。其中列出了所有
我的SQL表中的字段，我检查了构成
键的字段。在屏幕的底部，它声明为确保数据完整性
并更新记录，您必须选择一个或多个字段，这些字段是唯一的b $ b b标识每个记录。 />
当我进行ODBC连接时，它永远不会自动设置我的密钥。
我做错了什么？
Ray
嘿Neil：
>
你当我联系
时，我不应该给SQL表一个密钥吗？在访问时，我做了新表 - 链接我做了一个ODBC链接到我的SQL表
。选择表后，我得到了一个访问屏幕，
的标题为选择唯一记录标识符。其中列出了所有
我的SQL表中的字段，我检查了构成
键的字段。在屏幕的底部，它声明为确保数据完整性
并更新记录，您必须选择一个或多个字段，这些字段是唯一的b $ b b标识每个记录。 />
当我进行ODBC连接时，它永远不会自动设置我的密钥。
我做错了什么？
Ray
对，这正是我所说的。您不应该获得Select
唯一记录标识符提示。你唯一能得到的就是Access
无法确定它自己的SQL表主键。
你需要去进入SQL Server并为表分配主键。然后
返回Access，完全删除可能存在的任何链接，然后
重新创建链接。然后就行了。
Neil
using Access 2003 and sql server version 8.0
Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:
Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "''" & "TextBoxValue" & "''"
Set rst = Me.RecordsetClone
rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
Pretty simple code and it works, but it is slow, slow, slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.
When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.
Is there anything I can do to make this "goto record" work faster.
Any help appreciated.
thanks ray
You say that when you linked the SQL table you gave it a key. You shouldn''t
have had to do that. If you gave your SQL table a primary key, then the
linking process should have automatically picked that up and make that the
key for your linked table. If you were prompted for the key, then something
is wrong. Make sure your table has a primary key defined.
Sometimes, if you have indexes defined on your SQL table, and if those index
names fall alphabetically before your primary key, Access may pick up one of
those instead, and, thus, prompt you for the key, since it picked up a
non-unique index. A way to resolve that is to rename your primary key to
have it start with "aaaa" or something, to make sure it''s first.
Second, using FindFirst is slow; but it shouldn''t be that slow. I had slow
results once by using a form that was saved as an unbound form, to which I
added a recordset after the form was opened. I found that saving the form as
a bound form (even bound to a zero-record recordset) made a HUGE difference,
over saving it as an unbound form and then adding a recordset on the fly. So
make sure that you save your form as a bound form, and not as an unbound
form.
Last, though you should be getting better performance even using FindFirst,
you might want to consider a different mechanism. Do you need 8500 records
in your continuous form?? Is the user going to scroll through 8500 records??
Consider which records the user actually wants/needs and abbreviate your
recordset to show those. It could be that they only need one at a time, in
which case you can just change the recordset to display that one record. Or,
if they need a set of records. But I don''t think they''d need 8500 records in
a continuous form! Unless they''re speed readers, that is... ;-)
HTH,
Neil
"eighthman11" <rd******@nooter.comwrote in message
news:10**********************************@o6g2000h sd.googlegroups.com...using Access 2003 and sql server version 8.0
Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:
Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "''" & "TextBoxValue" & "''"
Set rst = Me.RecordsetClone
rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
Pretty simple code and it works, but it is slow, slow, slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.
When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.
Is there anything I can do to make this "goto record" work faster.
Any help appreciated.
thanks ray
On Nov 21, 5:30 pm, "Neil" <nos...@nospam.netwrote:You say that when you linked the SQL table you gave it a key. You shouldn''t
have had to do that. If you gave your SQL table a primary key, then the
linking process should have automatically picked that up and make that the
key for your linked table. If you were prompted for the key, then something
is wrong. Make sure your table has a primary key defined.
Sometimes, if you have indexes defined on your SQL table, and if those index
names fall alphabetically before your primary key, Access may pick up one of
those instead, and, thus, prompt you for the key, since it picked up a
non-unique index. A way to resolve that is to rename your primary key to
have it start with "aaaa" or something, to make sure it''s first.
Second, using FindFirst isslow; but it shouldn''t be thatslow. I hadslow
results once by using a form that was saved as an unbound form, to which I
added a recordset after the form was opened. I found that saving the form as
a bound form (even bound to a zero-record recordset) made a HUGE difference,
over saving it as an unbound form and then adding a recordset on the fly. So
make sure that you save your form as a bound form, and not as an unbound
form.
Last, though you should be getting better performance even using FindFirst,
you might want to consider a different mechanism. Do you need 8500 records
in your continuous form?? Is the user going to scroll through 8500 records??
Consider which records the user actually wants/needs and abbreviate your
recordset to show those. It could be that they only need one at a time, in
which case you can just change the recordset to display that one record. Or,
if they need a set of records. But I don''t think they''d need 8500 records in
a continuous form! Unless they''re speed readers, that is... ;-)
HTH,
Neil
"eighthman11" <rdshu...@nooter.comwrote in message
news:10**********************************@o6g2000h sd.googlegroups.com...
using Access 2003 and sql server version 8.0
Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:
Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "''" & "TextBoxValue" & "''"
Set rst = Me.RecordsetClone
rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark= rst.Bookmark
End If
rst.Close
Pretty simple code and it works, but it isslow,slow,slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.
When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.
Is there anything I can do to make this "goto record" work faster.
Any help appreciated.
thanks ray- Hide quoted text -
- Show quoted text -
------------------------------------------------------------------------------------------------------------------------------------------------
Hey Neil:
You say I shouldn''t have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".
It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?
Ray
Hey Neil:>
You say I shouldn''t have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".
It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?
RayRight, that''s exactly what I''m saying. You should not be getting the Select
Unique Record Identifier prompt. The only time you get that is when Access
can''t determine on it''s own the SQL table primary key.
You need to go into SQL Server and assign a primary key to the table. Then
go back to Access, completely delete any link that might be there, and then
recreate the link. Then it will work.
Neil
这篇关于书签连续形式慢慢慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
