copying data from one dataset into another

[Ian Burton]

Hi,
I hope this is the correct NewsGroup for my query.
I have a vb windows application , and I have two seperate Microsoft access
2003 databases which the application accesses.
I need to import data from one database into another database, and I would
like to know the best way of achieving this in vb. I have datasets for both
databases. I have been looking into writing code to open one dataset, and
then go through the records iteratively, and then open the second dataset,
check if the record exists, and if I have gone through the table, then
create a row, and append it to the table. then get that @@identity record,
and then select the sub table, and continue through the system.
this is not an elegant solution, and will be heavy on resources. In
Microsoft access, I could simply create an append query, and run it based on
records that do NOT exist in the first table.
What is the best approach to this? I cannot see how I might attach both
databases tables into the same dataset. Presumably then I could create some
kind of relationship to select those records not existing in the second DB,
and then create a tableadapter query for that?
thanks for any ideas or links you cvan give me. I have been looking into
this intently for a while, and I suspect I have missed something obvious.
Ian

 

[Gregory A. Beamer]

Hi,
I hope this is the correct NewsGroup for my query.
I have a vb windows application , and I have two seperate Microsoft
access 2003 databases which the application accesses.
I need to import data from one database into another database, and I
would like to know the best way of achieving this in vb. I have
datasets for both databases. I have been looking into writing code to
open one dataset, and then go through the records iteratively, and
then open the second dataset, check if the record exists, and if I
have gone through the table, then create a row, and append it to the
table. then get that @@identity record, and then select the sub
table, and continue through the system. this is not an elegant
solution, and will be heavy on resources. In Microsoft access, I
could simply create an append query, and run it based on records that
do NOT exist in the first table. What is the best approach to this? I
cannot see how I might attach both databases tables into the same
dataset. Presumably then I could create some kind of relationship to
select those records not existing in the second DB, and then create a
tableadapter query for that? thanks for any ideas or links you cvan
give me. I have been looking into this intently for a while, and I
suspect I have missed something obvious. Ian

If this is a one time thing, add the tables from the other access
database with a _EX added to the table name. You can then do something
like this (I have not done all the work here, as I am not looking for
the primary key in the table yet (I have it defaulted to X):

Private Sub Button1_Click(ByVal sender As Object, _
ByVal e As EventArgs) Handles Button1.Click

Dim schemaTable As DataTable = GetSchemaTable()

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"

'Requires .NET 2.0 or greater
Using conn As OleDbConnection = New OleDbConnection(connString)

conn.Open()

Dim row As DataRow

'Run through each table name
For Each row In schemaTable.Rows
If (row.Item(3).ToString.ToUpper = "TABLE") Then
Dim tableName As String = row.Item(2).ToString

Dim query As String = GetSqlQuery(tableName, "id")

Dim cmd As New OleDbCommand(query, conn)

'cmd.ExecuteNonQuery()
End If
Next

End Using
End Sub

Private Function GetSchemaTable() As DataTable

Dim schemaTable As DataTable

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"
Dim conn As New OleDbConnection(connString)

Dim schema As Guid = OleDbSchemaGuid.Tables

'TODO: This could be used on columns
'Change to DataSet with two tables, if so
'Dim schema2 As Guid = OleDbSchemaGuid.Columns

Dim restrictions As Object() = _
{Nothing, Nothing, Nothing, "TABLE"}

Try
conn.Open()
schemaTable = conn.GetOleDbSchemaTable(schema, restrictions)
Finally
conn.Dispose()
End Try

Return schemaTable

End Function

Private Function GetSqlQuery(ByVal tableName As String, _
ByVal primarykey As String) As String

Dim builder As New StringBuilder()

builder.Append("SELECT ")
builder.Append(tableName)
builder.Append("_EX.* FROM ")
builder.Append(tableName)
builder.Append("_EX LEFT JOIN ")
builder.Append(tableName)
builder.Append(" ON ")
builder.Append(tableName)
builder.Append("_EX.")
builder.Append(primarykey)
builder.Append(" = ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" WHERE ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" IS NULL;")

Return builder.ToString

End Function


BTW, you can even set up the linked tables, if you wish and then destroy
them by walking through the table schema, but this is a usable copy
routine that you can alter.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Ian Burton]

If this is a one time thing, add the tables from the other access
database with a _EX added to the table name. You can then do something
like this (I have not done all the work here, as I am not looking for
the primary key in the table yet (I have it defaulted to X):

Private Sub Button1_Click(ByVal sender As Object, _
ByVal e As EventArgs) Handles Button1.Click

Dim schemaTable As DataTable = GetSchemaTable()

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"

'Requires .NET 2.0 or greater
Using conn As OleDbConnection = New OleDbConnection(connString)

conn.Open()

Dim row As DataRow

'Run through each table name
For Each row In schemaTable.Rows
If (row.Item(3).ToString.ToUpper = "TABLE") Then
Dim tableName As String = row.Item(2).ToString

Dim query As String = GetSqlQuery(tableName, "id")

Dim cmd As New OleDbCommand(query, conn)

'cmd.ExecuteNonQuery()
End If
Next

End Using
End Sub

Private Function GetSchemaTable() As DataTable

Dim schemaTable As DataTable

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"
Dim conn As New OleDbConnection(connString)

Dim schema As Guid = OleDbSchemaGuid.Tables

'TODO: This could be used on columns
'Change to DataSet with two tables, if so
'Dim schema2 As Guid = OleDbSchemaGuid.Columns

Dim restrictions As Object() = _
{Nothing, Nothing, Nothing, "TABLE"}

Try
conn.Open()
schemaTable = conn.GetOleDbSchemaTable(schema, restrictions)
Finally
conn.Dispose()
End Try

Return schemaTable

End Function

Private Function GetSqlQuery(ByVal tableName As String, _
ByVal primarykey As String) As String

Dim builder As New StringBuilder()

builder.Append("SELECT ")
builder.Append(tableName)
builder.Append("_EX.* FROM ")
builder.Append(tableName)
builder.Append("_EX LEFT JOIN ")
builder.Append(tableName)
builder.Append(" ON ")
builder.Append(tableName)
builder.Append("_EX.")
builder.Append(primarykey)
builder.Append(" = ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" WHERE ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" IS NULL;")

Return builder.ToString

End Function


BTW, you can even set up the linked tables, if you wish and then destroy
them by walking through the table schema, but this is a usable copy
routine that you can alter.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

dear Gregory,
Thanks for this. I am just working through the code you gave me, and trying
it out, so I understand what it is doing.
It looks good, and quite a useful template for this very common activity.
The only thing I have to tell you is that this function will run every time
the application opens. the other database is being used by a third party
application, so we have no control over when the data changes. therefore I
expect to run this functionality on startup, and it is possible that they
may want this function converted to a service, and running constantly. I
don't know whether that changes your recommendation.
I am going through this and trying it out, and I will comment back early
next week with my results.
thanks for your help.
Ian Burton

 

[Ian Burton]

If this is a one time thing, add the tables from the other access
database with a _EX added to the table name. You can then do something
like this (I have not done all the work here, as I am not looking for
the primary key in the table yet (I have it defaulted to X):

Private Sub Button1_Click(ByVal sender As Object, _
ByVal e As EventArgs) Handles Button1.Click

Dim schemaTable As DataTable = GetSchemaTable()

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"

'Requires .NET 2.0 or greater
Using conn As OleDbConnection = New OleDbConnection(connString)

conn.Open()

Dim row As DataRow

'Run through each table name
For Each row In schemaTable.Rows
If (row.Item(3).ToString.ToUpper = "TABLE") Then
Dim tableName As String = row.Item(2).ToString

Dim query As String = GetSqlQuery(tableName, "id")

Dim cmd As New OleDbCommand(query, conn)

'cmd.ExecuteNonQuery()
End If
Next

End Using
End Sub

Private Function GetSchemaTable() As DataTable

Dim schemaTable As DataTable

Dim connString As String = "Provider=Microsoft.jet.oledb.4.0;" + _
"Data Source=c:\projects\batch.mdb"
Dim conn As New OleDbConnection(connString)

Dim schema As Guid = OleDbSchemaGuid.Tables

'TODO: This could be used on columns
'Change to DataSet with two tables, if so
'Dim schema2 As Guid = OleDbSchemaGuid.Columns

Dim restrictions As Object() = _
{Nothing, Nothing, Nothing, "TABLE"}

Try
conn.Open()
schemaTable = conn.GetOleDbSchemaTable(schema, restrictions)
Finally
conn.Dispose()
End Try

Return schemaTable

End Function

Private Function GetSqlQuery(ByVal tableName As String, _
ByVal primarykey As String) As String

Dim builder As New StringBuilder()

builder.Append("SELECT ")
builder.Append(tableName)
builder.Append("_EX.* FROM ")
builder.Append(tableName)
builder.Append("_EX LEFT JOIN ")
builder.Append(tableName)
builder.Append(" ON ")
builder.Append(tableName)
builder.Append("_EX.")
builder.Append(primarykey)
builder.Append(" = ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" WHERE ")
builder.Append(tableName)
builder.Append(".")
builder.Append(primarykey)
builder.Append(" IS NULL;")

Return builder.ToString

End Function


BTW, you can even set up the linked tables, if you wish and then destroy
them by walking through the table schema, but this is a usable copy
routine that you can alter.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

Greg,
thnaks a lot for this code. It is very interesting. I am was pulled off
this project for a couple of weeks, and now I am back on this again. I am
looking at it today.
regards,
Ian