后端的独特选择 [英] Unique Selections In Back End

问题描述

我正在使用带有MS Access 2000 MDB前端的SQL 7，使用带有ODBC链接表的绑定表单

。在一种形式中，用户需要能够检查

框以选择一个或多个记录。这是通过包含两个字段的本地表

来完成的：SQL表的主键值和用于复选框的布尔值

字段。


由于用于包含布尔字段的本地表是MDB

文件的本地表，因此结果是底层表单查询中的异构连接，其中

会降低性能。我想让整个查询基于返回

结束SQL数据。但是，每个用户都需要能够制作一套独特的

选项，而不会影响他们的其他用户选择。


我的想法是将选择表移植到后端，并附加一个

附加字段用于机器名称;创建一个主表的视图加入到

选择表;将视图链接到前端;并将表单基于

SQL：选择*来自MyView Where MachineName =''MyMachine''"。


但是，我想知道是否有更好的方法。任何想法都会被赞赏。


谢谢，


Neil

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the MDB
file, the result is a heterogeneous join in the underlying form query, which
degrades performance. I would like to have the entire query be based on back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users'' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName=''MyMachine''".

However, I wonder if there''s a better approach. Any ideas would be
appreciated.

Thanks,

Neil

推荐答案

依赖机器名称可能很困难，因为如果机器名称

发生变化，您的代码将无法正常工作（不是很常见，但它

发生）。我见过人们使用的一种技术是使用本地MDB

表来进行选择。然后，您可以形成一个以逗号分隔的

ID列表，然后将其发送到后端程序。在这个过程中，你将
可以将逗号分隔值转换为临时表（使用

技术： http://www.algonet.se/~sommar/arrays-in-sql.html ）然后用实际表格加入

来显示结果。

-

HTH，

SriSamp

电子邮件： sr*****@gmail.com

博客： http://blogs.sqlxml.org/srinivassampath

网址： http://www32.brinkster.com/srisamp


" Neil" <无**** @ nospam.net>在消息中写道

新闻：1L **************** @ newsread1.news.pas.earthli nk.net ...

Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I''ve seen people use is to have a local MDB
table itself for making the selections. You can then form a comma-separated
list of IDs that is then sent to a backend procedure. In this procedure, you
can conver the comma-separated values into a temp table (using the
techniques in: http://www.algonet.se/~sommar/arrays-in-sql.html) and then do
the join with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earthli nk.net...

我使用带有MS Access 2000 MDB前端的SQL 7，使用带有ODBC链接表的绑定表单。在一种形式中，用户需要能够检查
框以选择一个或多个记录。这是通过一个包含两个字段的本地表来完成的：SQL表的主键值和用于复选框的布尔值

因为使用了本地表包含布尔字段对于MDB文件是本地的，结果是底层表单查询中的异构连接，这会降低性能。我想将整个查询基于后端SQL数据。但是，每个用户都需要能够做出一组独特的选择，而不会影响他们的其他用户选择。

我的想法是将选择表移到后面以机器名称的附加字段结束;创建一个加入主表的视图到选择表;将视图链接到前端;并将表格基于SQL：Select * From MyView Where MachineName =''MyMachine''"

然而，我想知道是否有更好的方法。任何想法都将受到赞赏。

谢谢，

Neil

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form query,
which degrades performance. I would like to have the entire query be based
on back end SQL data. However, each user needs to be able to make a unique
set of selections, without other users'' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the form
on the SQL: "Select * From MyView Where MachineName=''MyMachine''".

However, I wonder if there''s a better approach. Any ideas would be
appreciated.

Thanks,

Neil

机器改名不是问题，因为这些选择是临时的 - 或许是几个小时或一夜之间。他们不是
永久实体。


另外，如果我使用临时表，我不知道如何将其带入

前端除了通过传递查询。在这种情况下，它将是

只读。


因此，我认为我最好使用一个加入两者的视图table或

允许我通过ODBC链接它的一些其他方法。我有点小问题

关于我在消息中概述的方法，因为它意味着视图

将有X个记录x Y个机器，这将使它非常大。当然，

它只会返回当前机器的记录。不过，似乎

最初会处理大量的记录。


谢谢，


Neil

" SriSamp" < SS ****** @ sct.co.in>在消息中写道

news：eu ************** @ TK2MSFTNGP14.phx.gbl ...

The machine name changing isn''t an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They''re not
permanent entities.

Also, if I use a temporary table, I''m not sure how I would bring that into
the front end except through a pass-through query. In that case, it would be
read-only.

Thus, I think it''s best that I work with a view that joins the two table or
some other method that allows me to link it via ODBC. I''m a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large. Granted,
it would only return the records for the current machine. Still, it seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...

依赖机器名称可能很难，因为如果机器名称改变，你的代码将无法工作（不是很频繁，但它会发生）。我见过人们使用的一种技术是使用本地MDB
表来进行选择。然后，您可以形成一个以逗号分隔的ID列表，然后将其发送到后端程序。在这个过程中，您可以将逗号分隔值转换为临时表（使用以下技术：
http://www.algonet.se/~sommar/arrays-in-sql.html ）然后再做加入
与实际表格一起显示结果。
-
HTH，
SriSamp
电子邮件： sr ***** @ gmail.com
博客： http://blogs.sqlxml.org/srinivassampath
URL： http://www32.brinkster.com/srisamp

Neil <无**** @ nospam.net>在消息中写道
新闻：1L **************** @ newsread1.news.pas.earthli nk.net ...

Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I''ve seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earthli nk.net...

我是使用带有MS Access 2000 MDB前端的SQL 7，使用带有ODBC链接表的绑定表单。在一种形式中，用户需要能够检查
框以选择一个或多个记录。这是通过包含两个字段的本地表来完成的：SQL表的主键值和用于复选框的
布尔字段。

由于使用了本地表包含布尔字段对于MDB文件是本地的，结果是底层表单查询中的异构连接，这会降低性能。我想让整个查询基于后端SQL数据。但是，每个用户都需要能够做出一组独特的选择，而不会影响他们的其他用户的选择。

我的想法是移植选择表格到后端有一个
附加字段用于机器名称;创建一个加入主表的视图到选择表;将视图链接到前端;并将
表单基于SQL：Select * From MyView Where MachineName =''MyMachine''"

然而，我想知道是否有更好的方法。任何想法都将受到赞赏。

谢谢，

Neil

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users'' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the
form on the SQL: "Select * From MyView Where MachineName=''MyMachine''".

However, I wonder if there''s a better approach. Any ideas would be
appreciated.

Thanks,

Neil

只能使用Docmd.Runsql

" CREATE TABLE＃..."创建一个临时表，然后使用该表作为<的记录源br />
表格（只需确保在创建

表后分配记录源）。如果您在表格中创建主键，则可以在表单中对其进行编辑。

将使用Docmd.Runsql删除临时表。 DROP TABLE

＃.." -statement或来自前端的连接关闭。


在程序中，您可以使用查询中的临时表。


Neil写道：

You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a
form (just be sure to assign the recordsource after the creation of the
table). If you create a primary key in the table, you will be able to
edit it in your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:

机器名称更改不是问题，因为这些选择是临时的 - - 最多可能是几个小时或过夜。他们不是永久性的实体。

另外，如果我使用临时桌子，我不知道如何把它带到前端除了通过传递查询。在这种情况下，它将是只读的。

因此，我认为我最好使用一个加入两个表的视图或其他一些其他的允许我通过ODBC链接它的方法。我对我在消息中概述的方法有点小问题，因为它意味着视图将有X个记录x Y个机器，这会使它非常大。当然，它只会返回当前机器的记录。尽管如此，似乎最初会处理大量的记录。

谢谢，

Neil

" SriSamp" < SS ****** @ sct.co.in>在消息中写道
新闻：eu ************** @ TK2MSFTNGP14.phx.gbl ...

The machine name changing isn''t an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They''re not
permanent entities.

Also, if I use a temporary table, I''m not sure how I would bring that into
the front end except through a pass-through query. In that case, it would be
read-only.

Thus, I think it''s best that I work with a view that joins the two table or
some other method that allows me to link it via ODBC. I''m a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large. Granted,
it would only return the records for the current machine. Still, it seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...

依赖机器名称可能很难，因为如果机器名称改变，你的代码将无法工作（不是很频繁，但它会发生）。我见过人们使用的一种技术是使用本地MDB
表来进行选择。然后，您可以形成一个以逗号分隔的ID列表，然后将其发送到后端程序。在这个过程中，您可以将逗号分隔值转换为临时表（使用以下技术：
http://www.algonet.se/~sommar/arrays-in-sql.html ）然后再做加入
与实际表格一起显示结果。
-
HTH，
SriSamp
电子邮件： sr ***** @ gmail.com
博客： http://blogs.sqlxml.org/srinivassampath
URL： http://www32.brinkster.com/srisamp

Neil <无**** @ nospam.net>在消息中写道
新闻：1L **************** @ newsread1.news.pas.earth link.net ...

Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I''ve seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earth link.net...

我使用带有MS Access 2000 MDB前端的SQL 7，使用带有ODBC链接表的绑定表单。在一种形式中，用户需要能够检查
框以选择一个或多个记录。这是通过包含两个字段的本地表来完成的：SQL表的主键值和用于复选框的
布尔字段。

由于使用了本地表包含布尔字段对于MDB文件是本地的，结果是底层表单查询中的异构连接，这会降低性能。我想让整个查询基于后端SQL数据。但是，每个用户都需要能够做出一组独特的选择，而不会影响他们的其他用户的选择。

我的想法是移植选择表格到后端有一个
附加字段用于机器名称;创建一个加入主表的视图到选择表;将视图链接到前端;并将
表单基于SQL：Select * From MyView Where MachineName =''MyMachine''"

然而，我想知道是否有更好的方法。任何想法都将受到赞赏。

谢谢，

Neil

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users'' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the
form on the SQL: "Select * From MyView Where MachineName=''MyMachine''".

However, I wonder if there''s a better approach. Any ideas would be
appreciated.

Thanks,

Neil

这篇关于后端的独特选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！