如何将SQL Server的数据库无损地导出到ACCESS数据库，网上见了一些资料，但大多都是零碎的，源于一个应用的驱动，认真分析和实现了SQL Server与ACCESS之间的数据导入导出，写出来与大家分享。如转载请注明出处（郑州大学学者任）。
    1. 注意的问题：
       （1）SQL Server和ACCESS的数据类型不同，SQL Server的数据类型更为丰富，数据类型不同、数据类型的代码也不同，因此，不能用SQL Server的类型代码去套ACCESS的类型代码，比如：在SQL Server中，DateTime中的代码值是135，而在ACCESS是7。因此，第一步要搞清楚他们的数据类型及类型代码值。常用类型见下表：
SQL：
            smallint  2              int       3
            real      4              float     5
            bit       11             variant   12           
            binary    128            char      129
            nvarchar  202            varchar   200
            ntext     203
 
ACCESS:
            短整型    2              长整型    3
            浮点型    4        双精度浮点型    5
            是/否     11          日期/时间    7           
            文本     202               备注    203
 
    （2）判断字段是否允许为NULL(“空”）。在生成表时，要设定字段是否允许为空的约束规则。则ADO连接的Fields().Attributes可以得到。
             Case 120, 234, 104
                  FieldAttr = "NULL"
             Case 24, 138, 8
                  FieldAttr = "NOT NULL"
     （3）自动编号字段的判断与设定。
              当Fields().Attributes = 16时，
              FieldAttr = "IDENTITY (1, 1) NOT NULL"       或 ="AUTOINCREMENT(1,1)"
     （4）关键字的判断与设定。对不同的数据类型设置关键字，其属性代码是不同的，常见的有32776, 32792, 32784等。另外，还要考虑多个字段共同组成关键字的情况。因此，可以这样设定： 
              Case 32776, 32792, 32784   
                   FieldAttr = ""
                   KeyStr = KeyStr & x1.rsNew.Fields(I - 1).Name & ","

    2. 组成生成对应ACCESS数据表的字符表达式，当然是由数据表名、列名、数据类型、宽度、约束组成。基本形式为：Create Tabel TableName(FieldName FieldType(FieldLength) FieldConstraint, ...)。
     例：
        FieldStr = FieldStr & x1.rsNew.Fields(I - 1).Name & " " & FieldType & " " & FieldAttr & ","
        SQLCmd = "Create Table " & Table.Name & "(" & FieldStr & ")"
    执行SQL命令即可生成相应的表结构。
 
    3. 向数据表中追加数据。通过循环程序，将数据灌入ACCESS的MDB数据表中。这时需要命令：
        SQLCmd = "Insert into  & Table.Name & " values " & FieldValue
 
    4. 附VB程序，VC++程序可类似开发。
 Public Function 生成到本地ACCESS() As Boolean
    Rem 先生成到当前服务器
    Dim x1 As New ExecProc
    Dim x2 As New ExecProc
    Dim FieldStr As String
    Dim FieldAttr As String
    Dim FieldType As String
    Dim KeyStr As String
    Dim k As Integer
   
    On Error GoTo ERR:
    AccessConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim(Text1.Text)
  
Rem 不存在则创建
    If Not fso.FileExists(Trim(Text1.Text)) Then
       myDB.Create AccessConnString
    End If
    myDB.ActiveConnection = AccessConnString

    ConnString = AccessConnString
    x2.OpenCn                          '本地
    ConnString = SourceConnString
    x1.OpenCn                          '远程
    Dim adoxCatalogX1 As New ADOX.Catalog
    Dim adoxCatalogX2 As New ADOX.Catalog
    Dim adoxTableX1 As ADOX.Table
    Dim adoxTableX2 As ADOX.Table
    Picture1.Line (0, 0)-(1 / 100 * Picture1.ScaleWidth, Picture1.ScaleHeight), vbBlue, BF
    adoxCatalogX1.ActiveConnection = SourceConnString
    adoxCatalogX2.ActiveConnection = AccessConnString
    Rem 如果表已经存在，先执行删除操作
  
    For Each adoxTableX1 In adoxCatalogX1.Tables
        
          Rem 系统文件不处理
          If adoxTableX1.Type = "TABLE" Then
              x1.SQLStr = "select * from " & adoxTableX1.Name
              FieldStr = ""
              x1.OpenRs
              KeyStr = "PRIMARY KEY ("
              FieldStr = ""
              For I = 1 To x1.rsNew.Fields.Count
                  Select Case x1.rsNew.Fields(I - 1).Type
                         Case 2
                              FieldType = "smallint"
                         Case 3
                              FieldType = "int"
                         Case 4, 5
                              FieldType = "float"
                         Case 11
                              FieldType = "smallint"
                         Case 202, 203, 130, 200
                             If x1.rsNew.Fields(I - 1).DefinedSize < 255 Then
                                FieldType = "nvarchar(" & x1.rsNew.Fields(I - 1).DefinedSize & ")"
                             Else
                                FieldType = "text"
                             End If
                         Case 135
                             FieldType = "DateTime"
                         Case Else
                              MsgBox ("Find New Type " & x1.rsNew.Fields(I - 1).Type)
                   End Select
            
                   Select Case x1.rsNew.Fields(I - 1).Attributes
                          Case 16
                               FieldAttr = "IDENTITY (1, 1) NOT NULL"           '( AUTOINCREMENT(1,1))
                          Case 120, 234, 104
                               FieldAttr = "NULL"
                          Case 24, 138, 8
                               FieldAttr = "NOT NULL"
                          Case 32776, 32792, 32784                         '键
                               FieldAttr = ""
                               KeyStr = KeyStr & x1.rsNew.Fields(I - 1).Name & ","
                          Case Else
                               MsgBox ("Find New Attrib " & x1.rsNew.Fields(I - 1).Attributes)
                    End Select
                 
                    If I < x1.rsNew.Fields.Count Then
                       FieldStr = FieldStr & x1.rsNew.Fields(I - 1).Name & " " & FieldType & " " & FieldAttr & ","
                    Else
                      FieldStr = FieldStr & x1.rsNew.Fields(I - 1).Name & " " & FieldType & " " & FieldAttr            ' 加上字段的约束
                    End If
              Next I
            
              FieldStr = FieldStr & IIf(Len(KeyStr) = 13, "", "," & Left(KeyStr, Len(KeyStr) - 1) & ")")     ' 加上键的约束， 如果没有设键 则为""
              x2.SQLStr = "Create Table " & adoxTableX1.Name & "(" & FieldStr & ")"
              x2.ExecSQLCmd
              For J = 1 To x1.rsNew.RecordCount
                  FieldStr = "("
                  For I = 1 To x1.rsNew.Fields.Count
                      Select Case x1.rsNew.Fields(I - 1).Type
                         Case 2, 3, 4, 5, 11
                              If I < x1.rsNew.Fields.Count Then
                                 FieldStr = FieldStr & IIf(IsNull(x1.rsNew.Fields(I - 1).Value), "Null", x1.rsNew.Fields(I - 1).Value) & ","
                              Else
                                 FieldStr = FieldStr & IIf(IsNull(x1.rsNew.Fields(I - 1).Value), "Null", x1.rsNew.Fields(I - 1).Value) & ")"
                              End If
                             End Select
                    
                  Next I
 
                  x2.SQLStr = "Insert into " & adoxTableX1.Name & " values" & FieldStr
                  x2.ExecSQLCmd
                  x1.rsNew.MoveNext
              Next J
            
              k = k + 1
              Progress (1 + k * 98 / 83)
        End If
    Next
  
    Progress (100)
  
    Set adoxTableX1 = Nothing
    Set adoxCatalogX1 = Nothing
    Set adoxTableX2 = Nothing
    Set adoxCatalogX2 = Nothing
    X.ReadInitInfo
    bSuccessed = True
    Exit Function
  
ERR:
   MsgBox (ERR.Description)
  
End Function