如何以编程方式将链接表复制到Ms Access中的本地表? [英] How to copy a linked table to a local table in Ms Access programmatically?

问题描述

所以我想在MS Access 2003中将链接表的代码，结构和数据复制到本地表中.

So I'd like to copy a linked table to a local one in code, structure and data in MS Access 2003.

代码为:VBA或C#.或其他任何事情.

Code being : VBA or C#. Or anything else for that matter..

更新:我希望从ms访问的副本结构和数据行为保持主键.如果复制链接表，则可以选择将其粘贴为结构和数据(本地表)" 我想用代码实现.

UPDATE : I want the copy structure and data behaviour from ms access to keep the Primary Keys. If you copy a linked table, you can choose to paste it as 'structure and data (local table)' It is that I want to achieve in code.

推荐答案

我的理解是DAO不支持十进制数据类型，但ADOX支持.这是更新的过程，使用ADOX代替，将架构复制到新表中.

My understanding is that DAO does not support the decimal data type, but ADOX does. Here's an updated procedure that uses ADOX instead to copy the schema to a new table.

一个有趣的注意事项:Jet的OLEDB提供程序按字母顺序而不是按顺序位置对列进行排序，如在这篇知识库文章中.我并不担心要保留顺序位置，但是您可以这样做，在这种情况下，您可以更新此过程以满足您的需求.

One interesting item of note: The OLEDB provider for Jet sorts the columns alphabetically rather than by ordinal position as explained in this KB article. I wasn't concerned about preserving the ordinal position, but you may be, in which case you can update this procedure to meet your needs.

为使ADOX版本的代码正常工作，您需要设置对Microsoft ADO Ext的引用. 2.x用于DDL和安全性(其中x =版本号；我使用2.8来测试此过程).您还需要对ADO的引用.

In order for the ADOX version of the code to work, you'll need to set a reference to Microsoft ADO Ext. 2.x for DDL and Security (where x = version number; I used 2.8 to test this procedure). You'll also need a reference to ADO as well.

Public Sub CopySchemaAndData_ADOX(ByVal sourceTableName As String, ByVal destinationTableName As String)
On Error GoTo Err_Handler

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim sourceTable As ADOX.Table
Dim destinationTable As ADOX.Table

Set cn = CurrentProject.Connection
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn

Set destinationTable = New ADOX.Table
destinationTable.Name = destinationTableName

Set sourceTable = cat.Tables(sourceTableName)

Dim col As ADOX.Column
For Each col In sourceTable.Columns
   Dim newCol As ADOX.Column
   Set newCol = New ADOX.Column

   With newCol
      .Name = col.Name
      .Attributes = col.Attributes
      .DefinedSize = col.DefinedSize
      .NumericScale = col.NumericScale
      .Precision = col.Precision
      .Type = col.Type
   End With

   destinationTable.Columns.Append newCol
Next col

Dim key As ADOX.key
Dim newKey As ADOX.key

Dim KeyCol As ADOX.Column
Dim newKeyCol As ADOX.Column
For Each key In sourceTable.Keys
   Set newKey = New ADOX.key
   newKey.Name = key.Name
   For Each KeyCol In key.Columns
      Set newKeyCol = destinationTable.Columns(KeyCol.Name)
      newKey.Columns.Append (newKeyCol)
   Next KeyCol

   destinationTable.Keys.Append newKey
Next key

cat.Tables.Append destinationTable

'Finally, copy data from source to destination table
Dim sql As String
sql = "INSERT INTO " & destinationTableName & " SELECT * FROM " & sourceTableName
CurrentDb.Execute sql

Err_Handler:
   Set cat = Nothing
   Set key = Nothing
   Set col = Nothing
   Set sourceTable = Nothing
   Set destinationTable = Nothing
   Set cn = Nothing

   If Err.Number <> 0 Then
      MsgBox Err.Number & ": " & Err.Description, vbCritical, Err.Source
   End If
End Sub

这是原始的DAO程序

Public Sub CopySchemaAndData_DAO(SourceTable As String, DestinationTable As String)
On Error GoTo Err_Handler

Dim tblSource As DAO.TableDef
Dim fld As DAO.Field

Dim db As DAO.Database
Set db = CurrentDb

Set tblSource = db.TableDefs(SourceTable)

Dim tblDest As DAO.TableDef
Set tblDest = db.CreateTableDef(DestinationTable)

'Iterate over source table fields and add to new table
For Each fld In tblSource.Fields
   Dim destField As DAO.Field
   Set destField = tblDest.CreateField(fld.Name, fld.Type, fld.Size)
   If fld.Type = 10 Then
      'text, allow zero length
      destField.AllowZeroLength = True
   End If
   tblDest.Fields.Append destField
Next fld

'Handle Indexes
Dim idx As Index
Dim iIndex As Integer
For iIndex = 0 To tblSource.Indexes.Count - 1
   Set idx = tblSource.Indexes(iIndex)
   Dim newIndex As Index
   Set newIndex = tblDest.CreateIndex(idx.Name)
   With newIndex
      .Unique = idx.Unique
      .Primary = idx.Primary
      'Some Indexes are made up of more than one field
      Dim iIdxFldCount As Integer
      For iIdxFldCount = 0 To idx.Fields.Count - 1
         .Fields.Append .CreateField(idx.Fields(iIdxFldCount).Name)
      Next iIdxFldCount
   End With

   tblDest.Indexes.Append newIndex
Next iIndex

db.TableDefs.Append tblDest

'Finally, copy data from source to destination table
Dim sql As String
sql = "INSERT INTO " & DestinationTable & " SELECT * FROM " & SourceTable
db.Execute sql

Err_Handler:
   Set fld = Nothing
   Set destField = Nothing
   Set tblDest = Nothing
   Set tblSource = Nothing
   Set db = Nothing

   If Err.Number <> 0 Then
      MsgBox Err.Number & ": " & Err.Description, vbCritical, Err.Source
   End If
End Sub

这篇关于如何以编程方式将链接表复制到Ms Access中的本地表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！