ADO Scallable Search [英] ADO Scallable Search

问题描述

我有一个200多个表格的数据库（每个学校有两个表格），每个表格有100个 -

4000个记录（每个学生一个记录）。我正在寻找一份合同，希望能够在所有表格中进行搜索，通过搜索表单搜索用户输入的价值

。


我有一个解决方案，但我不认为它会在大规模上工作。


是否有更好的选择然后查询表1，查找匹配值和

将这些记录写入ADO变量，关闭该表，然后将

移动到下一个表等？


这对我来说似乎是一种非常强力的方法。


< Ade

-

Adrian帕克。被任命的牧师。 < ad *********** @ sympatico.ca>

想知道生活的目的吗？我很乐意与您分享...

I have a database of 200+ tables (two tables per school), each with 100 -
4000 records (one record per student). A contract I''m looking at wants to
be able to do a search across all the tables, searching for values a user
inputs via a Search form.

I have a solution, but I don''t think it''ll work on large scales.

Is there a better bet then querying table 1, finding matching values and
writing those records into an ADO variable, closing that table, then moving
on to the next table etc?

That seems a very brute force method to me.

<Ade
--
Adrian Parker. Ordained priest. <ad***********@sympatico.ca>
Want to know the purpose of life? I''d be happy to share it with you...

推荐答案

您可以在SQL中完成所有操作并为结果创建临时表。如果所有

200表具有相同的结构（我假设他们在这里做，你需要一个

系列（想想在表中添加一个SchoolID字段）和过滤

学校ID的所有记录，而不是每个学校有一张桌子可以节省你

a很多时间:-)。


SELECT INTO< TempTableName> * FROM< TableName> WHERE< ListOfCriteria>


取决于数据库类型（MySQL，SQL Server，Oracle，Firebird（Interbase），

Access）你可以将所有这些查询放在一个存储过程中（除了

Access）并执行该存储过程传递它的搜索条件

从该搜索表单开始。


执行所有查询后（或存储过程）


SELECT * FROM< TempTableName> ORDER BY< SortField（s）>


并出示该表格。

-
$ b $bStéphaneRichard

Ada World网站管理员
http://www.adaworld.com

" Adrian帕克" < [e-mail]>在消息中写道

新闻：X1 ******************* @ news20.bellglobal.com ..。

You could do it all in SQL and create a temp table for the results. if all
200 Table have the same structure (which I assume they do here, you''d need a
series of (Idealy adding perhaps a SchoolID field to a table and filtering
all records by School ID instead of having a table per school would save you
a lot of time for this :-).

SELECT INTO <TempTableName> * FROM <TableName> WHERE <ListOfCriteria>

Depending on Database type (MySQL, SQL Server, Oracle, Firebird (Interbase),
Access) you could put all these queries in a stored procedure (except
Access) and execute that stored procedure passing it the search criteria
from that search form.

Once all queries are executed (or store procedure)

SELECT * FROM <TempTableName> ORDER BY <SortField(s)>

And present that table.
--
Stéphane Richard
"Ada World" Webmaster
http://www.adaworld.com
"Adrian Parker" <[e-mail]> wrote in message
news:X1*******************@news20.bellglobal.com.. .

我有一个200多个表的数据库（每个学校有两个表），每个表有100个 -
4000个记录（每个学生一个记录）。我正在寻找的合同想要能够在所有表格中进行搜索，搜索用户通过搜索表单输入的值。

我有一个解决方案，但我不认为它会在大规模上工作。

是否有更好的选择，然后查询表1，找到匹配的值和
写入这些记录ADO变量，关闭那个表，然后
继续下一个表等？

这对我来说似乎是一种非常强力的方法。

< Ade
-
阿德里安帕克。被任命的牧师。 < ad *********** @ sympatico.ca>
想知道生活的目的吗？我很乐意与你分享......

I have a database of 200+ tables (two tables per school), each with 100 -
4000 records (one record per student). A contract I''m looking at wants to
be able to do a search across all the tables, searching for values a user
inputs via a Search form.

I have a solution, but I don''t think it''ll work on large scales.

Is there a better bet then querying table 1, finding matching values and
writing those records into an ADO variable, closing that table, then moving on to the next table etc?

That seems a very brute force method to me.

<Ade
--
Adrian Parker. Ordained priest. <ad***********@sympatico.ca>
Want to know the purpose of life? I''d be happy to share it with you...

" Stephane Richard" < ST ************** @ verizon.net>在消息中写道

新闻：Tk ***************** @nwrdny01.gnilink.net ...

"Stephane Richard" <st**************@verizon.net> wrote in message
news:Tk*****************@nwrdny01.gnilink.net...

您可以在SQL中完成所有操作并为结果创建临时表。如果
全部200个表具有相同的结构（我假设他们在这里做，你需要
a系列（想想在表中添加一个SchoolID字段并过滤所有记录）学校ID而不是每个学校有一张桌子可以节省
你需要花费很多时间:-)。


雅，但试着说服整个利兹和格伦维尔学校他们

必须改变他们现有的格式:)


SELECT INTO< TempTableName> * FROM< TableName> WHERE< ListOfCriteria>

根据数据库类型（MySQL，SQL Server，Oracle，Firebird
（Interbase），Access），您可以将所有这些查询放在存储过程中（除了
访问）并执行该存储过程从该搜索表单传递搜索条件
。

You could do it all in SQL and create a temp table for the results. if all 200 Table have the same structure (which I assume they do here, you''d need a series of (Idealy adding perhaps a SchoolID field to a table and filtering
all records by School ID instead of having a table per school would save you a lot of time for this :-).
Ya, but try convincing the entire Leeds & Grenville schoolboard that they
have to change their existing format :)

SELECT INTO <TempTableName> * FROM <TableName> WHERE <ListOfCriteria>

Depending on Database type (MySQL, SQL Server, Oracle, Firebird (Interbase), Access) you could put all these queries in a stored procedure (except
Access) and execute that stored procedure passing it the search criteria
from that search form.

我确实使用Access。任何想法？

< snip>

Adrian

I am indeed using Access though. Any ideas?
<snip>
Adrian

在VB循环中，执行我的第一个查询在我的上一条消息中显示


选择进入< TempTableName> * FROM< TableName>在哪里<条件>


确保< TempTableName>所有查询的名称相同


一旦完成它们（其中shuold更快，比使用ADO快得多

记录集对象插入）。 （因此在循环完成之后。


在dbOpenDynaset模式下打开一个记录集对象。查询


SELECT * from< TempTableName> ; ORDER BY<排序顺序>"并且您应该设置。


-
$ b $bStéphaneRichard

" Ada World网站管理员
http://www.adaworld.com
" Adrian Parker" [e-mail]>写在留言中

news：r1 ****************** *@news20.bellglobal.com ..。

in a VB loop, execute the first query that I showed in my last message

Select Into <TempTableName> * FROM <TableName> WHERE <Conditions>

Making sure <TempTableName> is the same name for all queries

Once they are all done (which shuold be faster, much faster than using ADO
recordset Objects do insert). (hence right after the loop is done.

Open a recordset Object in dbOpenDynaset mode. with the query

SELECT * from <TempTableName> ORDER BY <Sort order>" and you should be set.

--
Stéphane Richard
"Ada World" Webmaster
http://www.adaworld.com
"Adrian Parker" <[e-mail]> wrote in message
news:r1*******************@news20.bellglobal.com.. .

Stephane Richard< st ************** @ verizon.net>写道在消息中
新闻：Tk ***************** @nwrdny01.gnilink.net ...

"Stephane Richard" <st**************@verizon.net> wrote in message
news:Tk*****************@nwrdny01.gnilink.net...

你可以做这一切都在SQL中并为结果创建临时表。如果所有

You could do it all in SQL and create a temp table for the results. if all

200表具有相同的结构（我假设他们在这里做，你会
需要一个

200 Table have the same structure (which I assume they do here, you''d need a

系列（希望将一个SchoolID字段添加到表格中，

series of (Idealy adding perhaps a SchoolID field to a table and

按学校ID过滤所有记录，而不是每个学校都有一个表格将保存

filtering all records by School ID instead of having a table per school would save

你

很多时间： - ）。

雅，但试着说服整个利兹&他们必须改变现有格式的Grenville schoolboard：

Ya, but try convincing the entire Leeds & Grenville schoolboard that they
have to change their existing format :)

SELECT INTO< TempTableName> * FROM< TableName> WHERE< ListOfCriteria>

取决于数据库类型（MySQL，SQL Server，Oracle，Firebird

SELECT INTO <TempTableName> * FROM <TableName> WHERE <ListOfCriteria>

Depending on Database type (MySQL, SQL Server, Oracle, Firebird

（Interbase），

(Interbase),

Access）您可以将所有这些查询放在存储过程中（除了访问权限）并执行该存储过程从该搜索表单中传递搜索条件
。

Access) you could put all these queries in a stored procedure (except
Access) and execute that stored procedure passing it the search criteria
from that search form.

我确实但是使用Access。有什么想法吗？

< snip>

Adrian

I am indeed using Access though. Any ideas?
<snip>
Adrian

这篇关于ADO Scallable Search的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！