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

2003-12-04 03:37:06 374 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

C# 操作.mdb文件出现.ldb锁死文件无法删除.mdb文件问题: 有个问题困扰我很长时间了 access数据库操作当访问.mdb文件会出现一个.ldb锁死文件我想用代码删除.mdb文件可是操作不了提示被另一个程序调用 c#做法类似 Dim SaveFile As String = "F:test.mdb" Dim SaveFileName As String = SaveFile Dim Cat As New A 阅读详情

搜索过以前的帖子，只能找到得到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的也不太熟，还请帮忙写点代码之类或给个例子之类的。

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

补充：有点急哦！ ^_^

...全文

Python | access转csv | 读取access mdb文件以csv格式重新存储文章目录模块代码案例模块 import pypyodbc import pandas 由于下载pypyodbc1.3.4和1.3.5版本报错~只能下载1.3.2版本 pip install pypyodbc==1.3.2 pip install pandas 代码案例 import pypyodbc import pandas path = r'xxx.mdb' # 数据库文件 table = 'admintbl' savePath = r'xxx.csv' def getAllColumnNam 阅读详情

高高兴兴的晶晶

数据库(包含打印，安装，报表) 1,217 成员 55,949 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

楼上已经够详细了。

高高兴兴的晶晶 2003-12-05

打赏
举报

楼上，谢了！

再 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.不是就自动有格子了么?

高高兴兴的晶晶 2003-12-04

打赏
举报

谢谢马哥，再问一下对word的操作熟吗？

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

zcm123 2003-12-04

打赏
举报

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对象吧，我没用过，你试试看

高高兴兴的晶晶 2003-12-04

打赏
举报

楼上的兄弟，这个我知道的。
说的是用vb实现，用代码实现。
只给你一个文件路径，比如客户机器上没装office怎么办呢？

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

up up

victorycyz 2003-12-04

打赏
举报

用Access直接打开mdb文件,点“工具”菜单->分析->文档管理器,里面要什么有什么。

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

相关推荐