ｍｄｂ内のデータをコピーするには？ (ID:144361)

名前

ホームページ(ブログ、Twitterなど)のURL (省略可)

本文

遅くなりましたが、一旦コードの方がまとまりました。一応目的の動作はしてくれています。
稚拙なコードですので本当は恥ずかしいのですが教えて頂いた結果報告として載せてみます。

Public Sub mdbDataCopy(ByVal oldmdbPath As String, ByVal newmdbPath As String)
    '
    Dim wOldmdbConnection As New OleDb.OleDbConnection
    Dim wNewmdbConnection As New OleDb.OleDbConnection
    Dim wCommand As New OleDb.OleDbCommand
    Dim wSourceMdbSchemaTable As DataTable
    '================================================================================
    ' 旧mdb
    wOldmdbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                         "Data Source=" & oldmdbPath & ";" & _
                                         "Persist Security Info=False"
    wOldmdbConnection.Open()
    '
    wSourceMdbSchemaTable = wOldmdbConnection.GetOleDbSchemaTable _
                            (System.Data.OleDb.OleDbSchemaGuid.Tables, _
                            New Object() {Nothing, Nothing, Nothing, "TABLE"})
    '================================================================================
    ' 新mdb
    wNewmdbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                         "Data Source=" & newmdbPath & ";" & _
                                         "Persist Security Info=False"
    wNewmdbConnection.Open()
    '
    wCommand.Connection = wNewmdbConnection
    '================================================================================
    ' データコピー
    Dim i As Integer
    Dim wTableName As String
    '
    For i = 0 To wSourceMdbSchemaTable.Rows.Count - 1
        ' 
        wTableName = wSourceMdbSchemaTable.Rows(i)(2).ToString
        ' 
        wCommand.CommandText = "INSERT INTO [" & wTableName & "] " & _
                               "SELECT [" & wTableName & "].* " & _
                               "FROM [" & oldmdbPath & "].[" & wTableName & "]"
        '
        wCommand.ExecuteNonQuery()
    Next
    '================================================================================
    If wOldmdbConnection.State = ConnectionState.Open Then
        wOldmdbConnection.Close()
    End If
    '
    If wNewmdbConnection.State = ConnectionState.Open Then
        wNewmdbConnection.Close()
    End If
End Sub

見よう見まねな所もありますので何かご指摘があれば是非お願いします。
(やはり CommandText の所で色々行き詰ってました)

←解決時は質問者本人がここをチェックしてください。

戻る