Hi Nick,
I made this a time ago as a samle for someone to make two access files, fill
of the access files, and than copy the contents from that to the other. The
only point is it is in VB.
But I think basicly there are a lot of things from your question in it.
\\\
Option Strict On
Module Main
'microsoft adox ext 2.7 for dll and security
Public Sub Main()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\db1.mdb") Then
System.IO.File.Delete("C:\db1.mdb")
System.IO.File.Delete("C:\db2.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db1.mdb")
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db2.mdb")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim conn2 As OleDb.OleDbConnection = New OleDb.OleDbConnection
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db1.mdb;User Id=admin;Password=;"
conn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db2.mdb;User Id=admin;Password=;"
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
conn2.Open()
Dim cmd2 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
cmd2.ExecuteNonQuery()
Try
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
For i As Integer = 4 To 12 Step 2
cmd2.Parameters.Clear()
cmd2.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd2.Parameters.Add(New OleDb.OleDbParameter("@a",
OleDb.OleDbType.Integer)).Value = i
cmd2.Parameters.Add(New OleDb.OleDbParameter("@b",
OleDb.OleDbType.Char, 20)).Value = Chr(75 + i)
cmd2.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd1)
Dim ds1 As DataSet = New DataSet
Dim dt1 As New DataTable("tbl1")
Dim dca As New DataColumn("a", Type.GetType("System.Int32"))
Dim dcb As New DataColumn("b", Type.GetType("System.String"))
dt1.Columns.Add(dca)
dt1.Columns.Add(dcb)
ds1.Tables.Add(dt1)
cmd1.CommandText = "Select a, b from tbl1"
Dim rdr As OleDb.OleDbDataReader
rdr = cmd1.ExecuteReader()
While rdr.Read()
Dim dr As DataRow
dr = ds1.Tables(0).NewRow
dr("a") = rdr.GetInt32(0)
dr("b") = rdr.GetString(1)
ds1.Tables(0).Rows.Add(dr)
End While
rdr.Close()
ds1.AcceptChanges()
cmd2.CommandText = "Select * from tbl1"
cmd2.Connection = conn2
Dim da2 As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(cmd2)
Dim cmb2 As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da2)
Dim ds2 As DataSet = New DataSet
conn2.Close()
da2.Fill(ds2)
For i As Integer = 0 To ds1.Tables(0).Rows.Count - 1
Dim swN As Boolean = True
For y As Integer = 0 To ds2.Tables(0).Rows.Count - 1
Dim a As Integer = ds2.Tables(0).Rows.Count - 1
If CInt(ds1.Tables(0).Rows(i).Item(0)) =
CInt(ds2.Tables(0).Rows(y).Item(0)) Then
ds2.Tables(0).Rows(y).Item(1) =
ds1.Tables(0).Rows(i).Item(1)
swN = False
Exit For
End If
Next
If swN Then
Dim dr As DataRow
dr = ds2.Tables(0).NewRow
For y As Integer = 0 To
ds1.Tables(0).Rows(i).ItemArray.Length - 1
dr(y) = ds1.Tables(0).Rows(i).ItemArray(y)
Next
ds2.Tables(0).Rows.Add(dr)
End If
Next
da2.Update(ds2)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
conn1.Close()
conn2.Close()
End Sub
End Module
///
