asp+access站内收搜引擎

Question

只输入一个关键字,在多个表中匹配多个字段(例如30个字段)的查询怎么写?请大家提示一下:
例如有如下表结构:(表名大写字母,字段名小写字母)
A.(a,b,c,d,e,f,g,g,h,j,k,l,m,n)
B.(a,b,c)
C.(a,b,c,d,e,f,g,g,h,j,k,l,m,n)
D.(a,b,c,d,e,f,g,g)
E.(a,b,c,d,e,f,g,g,h,j,k,l,m,n,o,p,q)

我不想总是用"select * from A where a like '%"&keyword&"%'",如果那样的话,得把人写头晕了效果也不好.有什么办法吗?
关键是,只输入一个关键字,多表多字段查询!

如果有人回答满意,加分100!

Answer 1

获取所有的表名,和表中的所有字段名,之后动态生成SQL语句
就解决了"把人写头晕"的问题了.
但是这样效率会很底~
获取所有表名(VB):
Dim adoCN As New ADODB.Connection
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1

Set rstSchema = adoCN.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close

adoCN.Close
字段名:
Dim adoCN As New ADODB.Connection
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1

Set rstSchema = adoCN.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close
字段名:
Dim adoCN As New ADODB.Connection
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1

Set rstSchema = adoCN.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close

Answer 2

Dim adoCN As New ADODB.Connection
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1

Set rstSchema = adoCN.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close

Answer 3

access如果数据库很大的话,这样搞查询会很慢的!!!建议用ms sql全文索引...
1,建立全文索引.
2,sql="select * from table where contains(*,keyword)"