作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
所以我想在 MS Access 2003 中将一个链接表复制到一个本地的代码、结构和数据中。
代码是:VBA 或 C#。或者其他任何事情..
更新:我希望来自 ms Access 的复制结构和数据行为保留主键。如果复制链接表,您可以选择将其粘贴为“结构和数据(本地表)”
这是我想在代码中实现的。
最佳答案
我的理解是 DAO 不支持十进制数据类型,但 ADOX 支持。这是使用 ADOX 将架构复制到新表的更新过程。
一个有趣的注意事项:Jet 的 OLEDB 提供程序按字母顺序而不是按顺序对列进行排序,如 in this KB article 所述。 .我并不担心保留序数位置,但您可能会担心,在这种情况下,您可以更新此过程以满足您的需要。
为了使代码的 ADOX 版本正常工作,您需要设置对 Microsoft ADO Ext 的引用。 2.x 用于 DDL 和安全性(其中 x = 版本号;我使用 2.8 来测试此过程)。您还需要对 ADO 的引用。
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
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 - 如何以编程方式将链接表复制到 Ms Access 中的本地表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1594096/
我是一名优秀的程序员,十分优秀!