查询以获取表模式 [英] Query to get Table schema
问题描述
感谢您花时间阅读我的问题。
我正在寻找Access中的一种方式，我可以运行一个查询，这将是
返回表架构信息。具体来说，我需要获得
列名，数据类型和列的大小。在SQL中有一个
存储过程执行此''SP_Help< table Name>。这个命令
给了我比我更多的东西，但我希望这个例子能有所帮助。
我正在写一个ASP搜索页面。我希望向页面发送一个
参数（表名），它将从架构数据中动态构建搜索页面
。我在MS SQL中使用了修改后的Stored
过程。
再次感谢！
NG
Thanks for taking time to read my questions.
I''m looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name, data type and size of the column. In SQL there is a
stored procedure that does this ''SP_Help <table Name>. This command
gives me more than I need but I hope the example will help.
I''m writing an ASP search page. I''m hoping to send the page a
parameter (table Name) and it will dynamically build the search page
from the schema data. I have this working with a modified Stored
Procedure in MS SQL.
Thanks Again!
NG
推荐答案
" N.格雷夫斯QUOT; <纳克***** @ REMOVEyahoo.com>写道：
"N. Graves" <ng*****@REMOVEyahoo.com> wrote:
感谢您花时间阅读我的问题。
我正在寻找Access中的一种方式，我可以运行一个将要返回的查询表架构信息。具体来说，我需要获取列的名称，数据类型和大小。在SQL中有一个
存储过程执行此''SP_Help< table Name>。这个命令给了我比我更多的东西，但我希望这个例子能有所帮助。
Thanks for taking time to read my questions.
I''m looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name, data type and size of the column. In SQL there is a
stored procedure that does this ''SP_Help <table Name>. This command
gives me more than I need but I hope the example will help.
您可以在VBA和DAO中编写一个程序来模拟SQL
服务器SP_HELP过程。这里有一些开始：
公共函数sp_help（）As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String
设置db = CurrentDb（）
每个tbl在db.TableDefs中
Debug.Print""
Debug .Print tbl.Name
For each fld in tbl.Fields
Debug.Print" " &安培; fld.Name& " " &安培; fld.Type
下一页
下一页
设置db = Nothing
结束功能
玩得开心！
-Mark
You could write a procedure in VBA and DAO to emulate the SQL
Server SP_HELP procedure. Here''s something to start with:
Public Function sp_help() As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String
Set db = CurrentDb()
For Each tbl In db.TableDefs
Debug.Print ""
Debug.Print tbl.Name
For Each fld In tbl.Fields
Debug.Print " " & fld.Name & " " & fld.Type
Next
Next
Set db = Nothing
End Function
Have fun!
-Mark
马克感谢您的回复和快速发布。
我不太熟悉DAO我使用ADO访问我的数据库。我将继续研究你的建议，但你有一个ADO
的例子吗？我无法找到和关于CurrentDB的信息（）
这通常是我访问数据库的方式。
sDBName =" driver = {Microsoft Access Driver （* .mdb）中}; DBQ = QUOT; &
Request.ServerVariables（" APPL_PHYSICAL_PATH"）& " \ fpdb\sayings.mdb"
设置objDB = Server.CreateObject（" ADODB.Connection"）
objDB.Open sDBName
SQLQuery =" select * from sayings"
objDB.execute（SQLQuery）
如果你有什么否则我非常感激
星期五，2006年3月10日21:45:14 -0800，标记 < no **** @ thanksanyway.org>
写道：
Mark thank you for you response and your quick posting.
I''m not very familiar with DAO I using ADO to access my database. I
will continue to research you suggestion but to you have an ADO
example? I could not find and information on the CurrentDB()
This typically the way I access the DB.
sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName
SQLQuery = "select * from sayings"
objDB.execute(SQLQuery)
If you have anything else to help I''m very much appreciative
On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <no****@thanksanyway.org>
wrote:
N。格雷夫斯QUOT; <纳克***** @ REMOVEyahoo.com>写道：
"N. Graves" <ng*****@REMOVEyahoo.com> wrote:
感谢您花时间阅读我的问题。
我正在寻找Access中的一种方式，我可以运行一个将要返回的查询表架构信息。具体来说，我需要获取列的名称，数据类型和大小。在SQL中有一个
存储过程执行此''SP_Help< table Name>。这个命令给了我比我更多的东西，但我希望这个例子能有所帮助。
Thanks for taking time to read my questions.
I''m looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name, data type and size of the column. In SQL there is a
stored procedure that does this ''SP_Help <table Name>. This command
gives me more than I need but I hope the example will help.
你可以在VBA和DAO中编写一个程序来模拟SQL
服务器SP_HELP程序。这里有一些开始：
公共函数sp_help（）作为布尔值
sp_help = True
Dim db作为DAO.Database
Dim tbl作为DAO。 TableDef
Dim fld作为DAO.Field
Dim sStmt作为字符串
为每个tbl设置db = CurrentDb（）
在db.TableDefs中调试。打印"
Debug.Print tbl.Name
For each fld in tbl.Fields
Debug.Print" " &安培; fld.Name& " " &安培; fld.Type
下一页
设置db = Nothing
结束功能
玩得开心！
-Mark
You could write a procedure in VBA and DAO to emulate the SQL
Server SP_HELP procedure. Here''s something to start with:
Public Function sp_help() As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String
Set db = CurrentDb()
For Each tbl In db.TableDefs
Debug.Print ""
Debug.Print tbl.Name
For Each fld In tbl.Fields
Debug.Print " " & fld.Name & " " & fld.Type
Next
Next
Set db = Nothing
End Function
Have fun!
-Mark
N.格雷夫斯在消息中写道< 0s ******************************** @ 4ax.com>
：
N. Graves wrote in message <0s********************************@4ax.com>
:
Mark感谢您的回复和快速发布。
我不太熟悉DAO我使用ADO访问我的数据库。我会继续研究你的建议但是你有一个ADO
的例子吗？我找不到和关于CurrentDB的信息（）
这通常是我访问数据库的方式。
sDBName =" driver = {Microsoft Access Driver（* .mdb） ）}; DBQ = QUOT; &
Request.ServerVariables（" APPL_PHYSICAL_PATH"）& " \ _fpdb\sayings.mdb"
设置objDB = Server.CreateObject（" ADODB.Connection"）
objDB.Open sDBName
SQLQuery =" select * from sayings"
objDB.execute（SQLQuery）
如果你有什么别的帮助我非常感激
周五，2006年3月10日21:45:14 -0800，标记 < no **** @ thanksanyway.org>
写道：
Mark thank you for you response and your quick posting.
I''m not very familiar with DAO I using ADO to access my database. I
will continue to research you suggestion but to you have an ADO
example? I could not find and information on the CurrentDB()
This typically the way I access the DB.
sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName
SQLQuery = "select * from sayings"
objDB.execute(SQLQuery)
If you have anything else to help I''m very much appreciative
On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <no****@thanksanyway.org>
wrote:
N。格雷夫斯QUOT; <纳克***** @ REMOVEyahoo.com>写道：
"N. Graves" <ng*****@REMOVEyahoo.com> wrote:
感谢您花时间阅读我的问题。
我正在寻找Access中的一种方式，我可以运行一个将要返回的查询表架构信息。具体来说，我需要获取列的名称，数据类型和大小。在SQL中有一个
存储过程执行此''SP_Help< table Name>。这个命令给了我比我更多的东西，但我希望这个例子能有所帮助。
Thanks for taking time to read my questions.
I''m looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name, data type and size of the column. In SQL there is a
stored procedure that does this ''SP_Help <table Name>. This command
gives me more than I need but I hope the example will help.
你可以在VBA和DAO中编写一个程序来模拟SQL
服务器SP_HELP程序。这里有一些开始：
公共函数sp_help（）作为布尔值
sp_help = True
Dim db作为DAO.Database
Dim tbl作为DAO。 TableDef
Dim fld作为DAO.Field
Dim sStmt作为字符串
为每个tbl设置db = CurrentDb（）
在db.TableDefs中调试。打印"
Debug.Print tbl.Name
For each fld in tbl.Fields
Debug.Print" " &安培; fld.Name& " " &安培; fld.Type
下一页
设置db = Nothing
结束功能
玩得开心！
-Mark
You could write a procedure in VBA and DAO to emulate the SQL
Server SP_HELP procedure. Here''s something to start with:
Public Function sp_help() As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String
Set db = CurrentDb()
For Each tbl In db.TableDefs
Debug.Print ""
Debug.Print tbl.Name
For Each fld In tbl.Fields
Debug.Print " " & fld.Name & " " & fld.Type
Next
Next
Set db = Nothing
End Function
Have fun!
-Mark
我不认为你会在
系统表中找到Access表的表模式信息，所以其他方式可能需要查询它们。
有.openschema方法，例如可以调用
这个
set rs = objDB.openschema（_
adschemacolumns，array（empty，empty，" yourtable"））
Check返回的字段名称和内容。
-
Roy-Vidar
I don''t think you''ll find table schema information of Access tables in
system tables, so other ways of enquiring for them is probably needed.
There''s the .openschema method, which could be called for instance like
this
set rs = objDB.openschema( _
adschemacolumns, array(empty, empty, "yourtable"))
Check the returning field names and contents of this.
--
Roy-Vidar
这篇关于查询以获取表模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
