有关access数据库的问题(如何得到.mdb文件的信息,包括所有表、每个表的字段、字段数据类型、主键等等的详细信息)

高高兴兴的晶晶 搬砖工  2003-12-04 03:37:06
搜索过以前的帖子,只能找到得到access库的所有表及表名的代码

1.通过查询系统表MsysObjects来查询所有的表。

2.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

但现在小弟像得到数据库的详细信息(包括所有表、每个表的字段、字段数据类型、主键等等的详细信息),然后最后写道word中 即生成一个.doc文件,有点像一个数据库的详细的设计说明的文档了。本人对word的也不太熟,还请帮忙写点代码之类或给个例子之类的。

还有以上两种方法有什么区别,哪位大侠能讲解一下

补充: 有点急哦! ^_^

...全文
95 点赞 收藏 13
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yoki 2003-12-06
用ADO列出某个表的所有字段、索引

查询类型 标准
=============================

adSchemaTables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE



使用 adSchemaTables 列出数据库中所有的表
在 Microsoft Access 97 and Access 2000 中以下例子展示了如何列出northwind数据库中所有的表和查询
Set rs = cn.OpenSchema(adSchemaTables)
While Not rs.EOF
Debug.Print rs!TABLE_NAME
rs.MoveNext
Wend


只列出表用以下的代码:
Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "Table")



在 Microsoft SQL Server 6.5 and 7.0 中以下代码列出Publs中所有的表和视图
Set rs = cn.OpenSchema(adSchemaTables)


只列出所有表用:
Set rs = cn.OpenSchema(adSchemaTables, _
Array("Pubs", Empty, Empty, "Table")




查询类型 标准字
===============================

adSchemaColumns TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME



用 adSchemaColumns 列出表中所有字段
在 Microsoft Access 97 and Access 2000 中列出 northwind.mdb 数据库 employees 表的所有字段代码如下:
Set rs = cn.OpenSchema(adSchemaColumns,Array(Empty, Empty, "Employees")

While Not rs.EOF
Debug.Print rs!COLUMN_NAME
rs.MoveNext
Wend



注意:需要OLE DB Provider for ODBC 和 Jet ODBC Driver 和 Jet OLE DB Providers支持

在 Microsoft SQL Server 6.5 and 7.0 中列出 Pubs database 中 Authors 表的所有字段用以下代码:
Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", "Authors")



查询类型 标准字
================================

adSchemaIndexes TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME



在下面例子中你必须提供一个索引名让 adSchemaIndexes querytype 使用
在 Microsoft Access 97 and Access 2000 中列出 northwind.mdb 数据库的 employees 表的所有索引用以下代码:
Set rs = cn.OpenSchema(adSchemaIndexes, _
Array(Empty, Empty, Empty, Empty, "Employees")

While Not rs.EOF
Debug.Print rs!INDEX_NAME
rs.MoveNext
Wend




在 Microsoft SQL Server 6.5 and 7.0 中列出 Pusb 数据库 Authors 表的所有索引用以下代码:
Set rs = cn.OpenSchema(adSchemaIndexes, _
Array("Pubs", "dbo", Empty, Empty, "Authors")



下面是一段完整的代码展示如何在 Sql Server + VB 中使用:
'Open the proper connection.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
'Getting the information about the columns in a particular table.
Set rs = cn.OpenSchema(adSchemaColumns, Array("pubs", "dbo", _
"authors"))
While Not rs.EOF
Debug.Print rs!COLUMN_NAME
rs.MoveNext
Wend

End Sub

Private Sub Command2_Click()
'Getting the information about the primary key for a table.
Set rs = cn.OpenSchema(adSchemaPrimaryKeys, Array("pubs", "dbo", _
"authors"))
MsgBox rs!COLUMN_NAME
End Sub

Private Sub Command3_Click()
'Getting the information about all the tables.
Dim criteria(3) As Variant
criteria(0) = "pubs"
criteria(1) = Empty
criteria(2) = Empty
criteria(3) = "table"
Set rs = cn.OpenSchema(adSchemaTables, criteria)
While Not rs.EOF
Debug.Print rs!TABLE_NAME

rs.MoveNext
Wend

End Sub

Private Sub Form_Load()
cn.Open "dsn=pubs;uid=sa;pwd=;"
'To test with the Native Provider for SQL Server, comment the
' line above then uncomment the following line. Modify to use
' your server.
'cn.Open "Provider=SQLOLEDB;Data Source=<servername>;" & _
' "User ID=sa;password=;"

End Sub




回复
hillmanweb 2003-12-06
楼上已经够详细了。
回复
楼上,谢了!


再 up 一下看看
回复
flc 2003-12-05
学习
回复
JoAiron 2003-12-04
代码太长了.希望能对你有帮助.我也没帮你筛选.懒得改了.你慢慢看把.
需要什么格式上的东西就把中间的那段"标签格式为7行"以下的代码改了
有些格式的东西可以在wrod里面录制宏中来实现
回复
JoAiron 2003-12-04
Private Sub cmdWord_Click()
If livPostResult.ListItems.Count = 0 Then
Exit Sub
End If
cmdWord.Enabled = False
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = CreateObject("Word.application")
'wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add
wdDoc.Activate
Dim strPostWordFile As String
Dim intcount, intNumber, intlistcount, intLable As Integer
Dim strFilename As String
Dim strTemp, strStc, strSeriesid, strSQL, strUpdate As String
intlistcount = livPostResult.ListItems.Count
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
intLable = 0
For intcount = 1 To intlistcount
strTemp = Trim(livPostResult.ListItems(intcount).Text)
If strTemp <> "" Then
strSeriesid = strTemp
If strCustflag = "P" Then
strSQL = "select a.cust_postcode ,a.concat_addr,a.initials,a.title,b.trace_flag from tblcustomer a,tblintentpur b where a.cust_id =b.cust_id and b.series_id =" & "'" & strTemp & "'"
End If
If strCustflag = "C" Then
strSQL = "select a.cust_postcode ,a.concat_addr,a.initials,a.title,b.custcomm_flag from tblcustomer a,tblintentpur b where a.cust_id =b.cust_id and b.series_id =" & "'" & strTemp & "'"
End If
On Error GoTo error_select
If ExeSql(strSQL, g_conn, rst) = False Then
PrintToLog "1379", "查询结果保存为word文档时,查询客户信息出错,错误代码 1379 查询语句: " & strSQL
MsgBox "查询结果保存为word文档时,查询客户信息出错,错误代码 1379", vbOKOnly + vbInformation, "提示信息"
Exit Sub
Else
If Nvl(rst("cust_postcode")) <> "" And Nvl(rst("concat_addr")) <> "" And Nvl(rst("initials")) <> "" Then

wdApp.Selection.PageSetup.RightMargin = CentimetersToPoints(7.66)
'标签格式7行
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.TypeParagraph
wdApp.Selection.MoveUp Unit:=wdLine, Count:=7
wdApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphJustify
wdApp.Selection.Font.Name = "黑体"
wdApp.Selection.Font.Size = 18
strPostWordFile = Nvl(rst("cust_postcode"))
wdApp.Selection.TypeText Text:=strPostWordFile
wdApp.Selection.MoveDown Unit:=wdLine, Count:=1
wdApp.Selection.Font.Name = "宋体"
wdApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
With wdApp.Selection.ParagraphFormat
.RightIndent = CentimetersToPoints(-0.03)
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
strPostWordFile = Nvl(rst("concat_addr"))
wdApp.Selection.TypeText Text:=strPostWordFile
wdApp.Selection.EndKey Unit:=wdStory
wdApp.Selection.MoveUp Unit:=wdLine, Count:=2
wdApp.Selection.Font.Name = "黑体"
strPostWordFile = Nvl(rst("initials"))
wdApp.Selection.TypeText Text:=strPostWordFile & " "
wdApp.Selection.Font.Name = "宋体"
wdApp.Selection.Font.Size = 14
strPostWordFile = Nvl(rst("title"))
wdApp.Selection.TypeText Text:=strPostWordFile
wdApp.Selection.EndKey Unit:=wdStory

intLable = intLable + 1
If intLable = 3 Then
wdApp.Selection.TypeParagraph
intLable = 0
End If
If strCustflag = "P" Then
strTemp = ""
strStc = Nvl(rst("trace_flag"))
For intNumber = 0 To g_intTraceflag
If tpyCrctraceflag(intNumber).trace_flag = strStc Then
strTemp = tpyCrctraceflag(intNumber + 1).trace_flag
End If
Next intNumber
strUpdate = "update tblintentpur set trace_flag =" & "'" & strTemp & "'" & "where series_id = " & strSeriesid
End If
If strCustflag = "C" Then
strTemp = ""
strStc = Nvl(rst("custcomm_flag"))
For intNumber = 0 To g_intCustcomm
If tpyCrccustcomm(intNumber).custcomm_flag = strStc Then
strTemp = tpyCrccustcomm(intNumber + 1).custcomm_flag
End If
Next intNumber
strUpdate = "update tblvehprod set custcomm_flag =" & "'" & strTemp & "'" & "where series_id = " & strSeriesid
End If
If strTemp <> "" Then
On Error GoTo error_flagUpdate
Dim cmdUpdateflag As ADODB.Command
Set cmdUpdateflag = New ADODB.Command
Set cmdUpdateflag.ActiveConnection = g_conn
cmdUpdateflag.CommandType = adCmdText
cmdUpdateflag.CommandTimeout = 100
cmdUpdateflag.CommandText = strUpdate
cmdUpdateflag.Execute
Set cmdBookUpdatesave = Nothing
End If
Else
'处理客户信息不完整的信息
'If Nvl(rst("cust_postcode")) <> ""
'And Nvl(rst("concat_addr")) <> ""
'And Nvl(rst("initials")) <> "" Then
Dim fileNumber As Integer
Dim intCode, strError, fileName As String
Dim datDate, datTime As Date
Dim strdate, strTime As String

intCode = pErrorcode
strError = pErrorstr
fileNumber = FreeFile
datDate = Date
datTime = Time

fileName = "c:\Crc" & Left$(datDate, 4) & Mid$(datDate, 6, 2) & Mid$(datDate, 9, 2) & ".Log"
strdate = CStr(datDate) & " " & CStr(datTime)

Open fileName For Append As fileNumber
Print #fileNumber, "时间:"; strdate; ","; " 日志代码: "; intCode; ","; " 信息提示: "; strError
Close #fileNumber
End If
End If
Else
'处理没有客户编号的客户
End If
rst.Close
Next intcount

On Error GoTo error_wordsave
wdApp.Selection.TypeParagraph
strFilename = Date
strFilename = strFilename & Trim(cmbCustPost.Text) & Trim(LsCustPost.Text) & "资料邮寄清单文件"
' wdDoc.SaveAs "g_scurrentDir" & "\" & strFilename & ".doc"
wdDoc.SaveAs "e:\" & strFilename & ".doc"
wdApp.Quit

Set wdApp = Nothing
Set wdDoc = Nothing
cmdWord.Enabled = True
Exit Sub
error_select:
MsgBox "查询结果保存为word文档时,查询客户信息出错,错误代码 1379", vbOKOnly + vbInformation, "提示信息"
PrintToLog "1379", "查询结果保存为word文档时,查询客户信息出错,错误代码 1379 查询语句: " & strSQL
cmdWord.Enabled = True
Exit Sub
error_flagUpdate:
MsgBox "更新客户资料邮寄标志出错,错误代码 1380", vbOKOnly + vbInformation, "提示信息"
PrintToLog "1380", "更新客户资料邮寄标志出错,错误代码 1380 查询语句: " & strUpdate
cmdWord.Enabled = True
Exit Sub
error_wordsave:
MsgBox "保存客户文件为word文档时出错,错误代码 1381", vbOKOnly + vbInformation, "提示信息"
PrintToLog "1381", "保存客户文件为word文档时出错,错误代码 1381"
cmdWord.Enabled = True
Exit Sub
End Sub

回复
JoAiron 2003-12-04
怎么不用excel.不是就自动有格子了么?
回复
谢谢马哥,再问一下对word的操作熟吗?

我要把,这些东西按一定格式写入,做成相应的表格,就像在print对象上,首先画好了相应的报表格式,然后把记录往相应的位置填充,然后用算法控制填写顺序,换行、满了换页等等的,这方面我是做过的,有一定经验。但是word要怎么操作呀,哪位能给个例子看看先吗?
回复
zcm123 2003-12-04
up
回复
yoki 2003-12-04

Access 系统表 MsysObjects 包含了数据库对象列表, 尽管未在文档中记载, 你仍可通过查询它来获取你想要的.

注: 请不要有意或无意地修改任何ACCESS系统表,否则会出现不可意料的情况.

使用下列 SQL 语句来获取你想要的

查询:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;


窗体:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;


表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;


报表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;


模块:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;


宏:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;

回复
Maconel 2003-12-04
写到word中估计用ole对象吧,我没用过,你试试看
回复
楼上的兄弟,这个我知道的。
说的是用vb实现,用代码实现。
只给你一个文件路径,比如客户机器上没装office怎么办呢?

还有就是对word的操作,能给点代码看看吗?
我是想把得到的相应信息按一定格式写到word中,比如制一个表格什么的,怎么操作呀,忘赐教!


up up
回复
victorycyz 2003-12-04
用Access直接打开mdb文件,点“工具”菜单->分析->文档管理器,里面要什么有什么。
回复
发动态
发帖子

1180

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
社区公告
暂无公告