access导出表结构

布满荆棘的人生 2022-08-06 14:12 236阅读 0赞

首先设置权限，否则会提示无访问权限

工具 -> 选项 -> 视图 -> 勾选隐藏对象、系统对象。

工具 -> 安全 -> 用户与组的权限 ，在对象名称中选定 MSysObjects ，然后权限中设置其读取权限。

\-------Java code------------------------------------------------------------------

package com.test;

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.ResultSetMetaData;  
import java.sql.Statement;  
import java.util.ArrayList;  
import java.util.List;

/\*\*  
\* @author 胡晶  
\* @version 1.0  
\* @date 2009-7-8  
\* @class\_displayName AccessDB  
\*/

public class AccessDB \{

/\*\*  
\* 输出表名  
\* @return List  
\*/  
static List getTables() \{  
List list = new ArrayList();  
try \{  
String strurl = "jdbc:odbc:driver=\{Microsoft Access Driver (\*.mdb)\};DBQ=c://test.mdb";  
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
Connection conn = DriverManager.getConnection(strurl);  
Statement stmt = conn.createStatement();  
ResultSet rs = stmt.executeQuery("SELECT name FROM MSysObjects WHERE type=1 and flags=0");  
while (rs.next()) \{  
list.add(rs.getString("name"));  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return list;  
\}

/\*\*  
\* 表的列信息  
\* @param tableName  
\*/  
static void getColumns() \{  
List list=getTables();  
String sql="";  
try \{  
String strurl = "jdbc:odbc:driver=\{Microsoft Access Driver (\*.mdb)\};DBQ=c://test.mdb";  
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
Connection conn = DriverManager.getConnection(strurl);  
// Statement stmt = conn.createStatement();  
for(int j=0;j<list.size();j++)\{  
sql = "select \* from "+list.get(j);  
PreparedStatement ps = conn.prepareStatement(sql);  
ResultSet rs = ps.executeQuery();  
ResultSetMetaData rsmd = rs.getMetaData();  
int numberOfColumns = rsmd.getColumnCount();  
System.out.println("--------------------"+j);  
for (int i = 1; i <= numberOfColumns; i++) \{  
System.out.print("列名:"+rsmd.getColumnName(i) + " / "); //列名  
System.out.print("数据类型名:"+rsmd.getColumnTypeName(i) + " / "); //数据类型名  
System.out.print("数据类型:"+getType(rsmd.getColumnType(i)) +":"+rsmd.getColumnType(i) +" / "); //数据类型  
System.out.print("允许空("+rsmd.isNullable(i)+") / "); //允许空  
System.out.print("自动增长("+rsmd.isAutoIncrement(i)+") /"); //是否自动增长  
System.out.print("长度("+rsmd.getPrecision(i)); //长度  
if(rsmd.getScale(i)>0)\{  
System.out.print(","+rsmd.getScale(i)); //小数长度  
\}  
System.out.println(")");  
\}  
\}  
\} catch (Exception e)\{  
e.printStackTrace();  
\}  
\}

/\*\*  
\* 获取表的主键名，需驱动支持，ACCESS不支持  
\* @param table  
\* @return String  
\*/  
static String getPrimaryKeys(String table)\{  
String key=null;  
Connection conn = null;  
ResultSet rs = null;  
try \{  
conn = DBConn.getConnection();  
DatabaseMetaData dbm = conn.getMetaData();  
rs = dbm.getPrimaryKeys(null, null, table);  
if(rs.next())\{  
key=rs.getObject(4).toString();  
\}  
\} catch (SQLException e) \{  
e.printStackTrace();  
\} finally\{  
DBConn.close(rs,conn);  
\}  
return key;  
\}

public static void main(String args\[\]) \{  
getColumns();  
\}

\-------VBScript code------------------------------------------------------------------  
Public Sub getTables()  
Dim rs As New ADODB.Recordset  
Dim conn As ADODB.Connection  
Dim sSQL As String  
  
Set conn = CurrentProject.Connection  
sSQL = "select id,Name from MSysObjects where Type=1 and flags=0"  
rs.Open sSQL, conn  
Do While Not rs.EOF  
Debug.Print "\*\*\*\*"; rs.Fields("Name").Value; "\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*"  
getTableColumns rs.Fields("Name").Value  
rs.MoveNext  
Loop  
rs.Close  
Set rs = Nothing  
Set conn = Nothing  
  
End Sub

Public Sub getTableColumns(sTableName As String)  
Dim rs As New ADODB.Recordset  
Dim conn As ADODB.Connection  
Dim fld As ADODB.Field

Set conn = CurrentProject.Connection  
rs.Open sTableName, conn, , , adCmdTable

For Each fld In rs.Fields  
Debug.Print fld.name, sFldType(fld.Type), fld.NumericScale, fld.Precision  
  
Next  
  
rs.Close  
Set rs = Nothing  
Set conn = Nothing

End Sub

Public Function sFldType(iType As Integer)  
Select Case IType  
Case 20  
sFldType = "BigInt"  
Case 128  
sFldType = "Binary"  
Case 11  
sFldType = "Boolean"  
Case 8  
sFldType = "BSTR"  
Case 136  
sFldType = "Chapter"  
Case 129  
sFldType = "Char"  
Case 6  
sFldType = "Currency"  
Case 7  
sFldType = "Date"  
Case 133  
sFldType = "DBDate"  
Case 134  
sFldType = "DBTime"  
Case 135  
sFldType = "DBTimeStamp"  
Case 14  
sFldType = "Decimal"  
Case 5  
sFldType = "Double"  
Case 0  
sFldType = "Empty"  
Case 10  
sFldType = "Error"  
Case 64  
sFldType = "FileTime"  
Case 72  
sFldType = "GUID"  
Case 9  
sFldType = "IDispatch"  
Case 3  
sFldType = "Integer"  
Case 13  
sFldType = "IUnknown"  
Case 205  
sFldType = "LongVarBinary"  
Case 201  
sFldType = "LongVarChar"  
Case 203  
sFldType = "LongVarWChar"  
Case 131  
sFldType = "Numeric"  
Case 138  
sFldType = "PropVariant"  
Case 4  
sFldType = "Single"  
Case 2  
sFldType = "SmallInt"  
Case 16  
sFldType = "TinyInt"  
Case 21  
sFldType = "UnsignedBigInt"  
Case 19  
sFldType = "UnsignedInt"  
Case 18  
sFldType = "UnsignedSmallInt"  
Case 17  
sFldType = "UnsignedTinyInt"  
Case 132  
sFldType = "UserDefined"  
Case 204  
sFldType = "VarBinary"  
Case 200  
sFldType = "VarChar"  
Case 12  
sFldType = "Variant"  
Case 139  
sFldType = "VarNumeric"  
Case 202  
sFldType = "VarWChar"  
Case 130  
sFldType = "WChar"  
CASE Else  
sFldType = "unKnown"  
End Select

End Function