access导出表结构

来源：互联网发布：网络购票身份证待核验编辑：程序博客网时间：2024/05/21 10:56

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

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

工具 -> 安全 -> 用户与组的权限，在对象名称中选定 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