Copying from one Database to another VB 2005

[Ryan]

I have 2 Access '97 databases. I am trying to migrate all the data from one
to the other (append to existing records). I do not have Access '97 and
opening with Access XP or later causes problems. I have found I can connect
to the databases in Visual Studio 2005 and insert records, etc, so I'm
trying to write my code using VB 2005.

I have 2 connections, 2 datasets... OldDS and NewDS. First I populate both
datasets:
OldDS.TableAdapter1.Fill(OldDS.Table1)
NewDS.TableAdapter1.Fill(NewDS.Table1)
This works fine, I've queried the datasets, they are both populated with
data from the tables, ready to go.

Here's where I have the problem, transferring data from the old dataset into
the new one. I've tried numerous approaches, here is the most recent
attempt:
For i As Integer = 0 To oldDS.Table1.Count - 1

' Copy Row from oldDataSet, INSERT into newDataSet

Dim r As DataRow = oldDS.Table1.Rows(i)

newDS.Table1.AddTable1Row(r.Item(0), r.Item(1), r.Item(2), r.Item(3),
r.Item(4))

Me.ProgressBar1.Increment(1)

Next

What happens is I get constraint errors.. type "DBNull" cannot be converted
to <insert type here>. I have checked both Datasets and constraints are
OFF. Why would it do this? All fields should allow Null values, but any
Nulls in the Old table are causing all sorts of problems. I've set the
default for Strings to (Empty) using the dataset designer and this seems to
help those fields but any other field type (DateTime for example) will throw
an exception.

Thanks,
Ryan

[Kevin Yu [MSFT]]

Hi Ryan,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to move data from one Access
database to the other. If there is any misunderstanding, please feel free
to let me know.

I would like to know if the schema of OldDS is the same as NewDS's. If so,
I don't think you need to copy the rows from one dataset to another. When
data is filled to OldDS, you can call SetAdded on each line to set the
RowState to Added. Here is an example:

For Each dr As DataRow In OldDS.Tables(0).Rows
dr.SetAdded();
Next

Then you can use a TableAdapter or DataAdapter to update the "Added" data
to the New Access database. HTH.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Ryan]

"Kevin Yu [MSFT]" <v-****@online.microsoft.comwrote in message
news:IJ****************@TK2MSFTNGXA01.phx.gbl...

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to move data from one Access
database to the other. If there is any misunderstanding, please feel free
to let me know.

This is correct. These are 2 Access 97 databases. One other thing I've
noticed is these tables don't even use Primary Keys. They are laid out more
like an Excel spreadsheet without any regard to rules of normalcy (just
FYI - don't think this will affect anything). These tables are used by 3rd
party software so I cannot alter the table layouts without risking losing
compatibility with the software that uses them.

I would like to know if the schema of OldDS is the same as NewDS's. If so,
I don't think you need to copy the rows from one dataset to another. When
data is filled to OldDS, you can call SetAdded on each line to set the
RowState to Added. Here is an example:

The schemas are the same, the only difference is the connection info (one
for each physical database).

For Each dr As DataRow In OldDS.Tables(0).Rows
dr.SetAdded();
Next

Then you can use a TableAdapter or DataAdapter to update the "Added" data
to the New Access database. HTH.

Ok, I've done this, but I'm unsure about what I'm suppose to do with the
TableAdapter to "update the Added data". How is setting the table state in
the oldDS for each row to "Added" going to help me move that data to the new
database?

Thanks,
Ryan

[Ryan]

One other thing I've noticed is these tables don't even use Primary Keys.

They are laid out more like an Excel spreadsheet without any regard to
rules of normalcy (just FYI - don't think this will affect anything).

Forgot to mention.. this does affect something. When I create my DataSets -
Update commands are not generated by the designer. I believe this is due to
the tables not having a primary key. Therefore, although I have been
successful copying data from one dataset to another, I have not been
successful using "Update" to commit the data back to the database
(newTA.Update(newDS.table(0))). Hence why I have been trying to use an
Insert command for each row to add the data directly to the database.

[Paul Clement]

On Wed, 25 Oct 2006 08:57:51 -0500, "Ryan" <Ty****@newsgroups.nospamwrote:

¤ I have 2 Access '97 databases. I am trying to migrate all the data from one
¤ to the other (append to existing records). I do not have Access '97 and
¤ opening with Access XP or later causes problems. I have found I can connect
¤ to the databases in Visual Studio 2005 and insert records, etc, so I'm
¤ trying to write my code using VB 2005.
¤
¤ I have 2 connections, 2 datasets... OldDS and NewDS. First I populate both
¤ datasets:
¤ OldDS.TableAdapter1.Fill(OldDS.Table1)
¤ NewDS.TableAdapter1.Fill(NewDS.Table1)
¤ This works fine, I've queried the datasets, they are both populated with
¤ data from the tables, ready to go.
¤
¤ Here's where I have the problem, transferring data from the old dataset into
¤ the new one. I've tried numerous approaches, here is the most recent
¤ attempt:
¤ For i As Integer = 0 To oldDS.Table1.Count - 1
¤
¤ ' Copy Row from oldDataSet, INSERT into newDataSet
¤
¤ Dim r As DataRow = oldDS.Table1.Rows(i)
¤
¤ newDS.Table1.AddTable1Row(r.Item(0), r.Item(1), r.Item(2), r.Item(3),
¤ r.Item(4))
¤
¤ Me.ProgressBar1.Increment(1)
¤
¤ Next
¤
¤ What happens is I get constraint errors.. type "DBNull" cannot be converted
¤ to <insert type here>. I have checked both Datasets and constraints are
¤ OFF. Why would it do this? All fields should allow Null values, but any
¤ Nulls in the Old table are causing all sorts of problems. I've set the
¤ default for Strings to (Empty) using the dataset designer and this seems to
¤ help those fields but any other field type (DateTime for example) will throw
¤ an exception.

Why not just use a SQL statement?

Open a connection to the destination database and specify the source database path in your SQL
statement:

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data Source=C:\Test
Files\destination.mdb")

AccessConn.Open()

AccessCommand.CommandText = "INSERT INTO [Data] SELECT * FROM [MS Access;DATABASE=C:\Test
Files\source.mdb;].[Data]"
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

[Kevin Yu [MSFT]]

Hi Ryan,

Paul's advice is very good. It might be the simplest way here to use on a
SQL statement.

As you can see, yes, the update commands are not generated because there is
no primary key in the table. So, you have to add a primary key to the
destination table in the Access database.

In this case, we only need one instance of the DataSet here since the two
schemas are identical. You can create 2 OleDbConnections which represents
connection to the 2 databases. A TableAdapter has a property named
Connection. Here are the steps:

1. Assign the first connection to TableAdapter.connection and get data from
source database.
2. Use the method I mentioned in my last post to set all the RowState to
Added.
3. Assign the second connection to TableAdapter.connection and call Update
to put data to destination database.

HTH.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Ryan]

Few code tweaks and this worked great. Couldn't get anything else to work
(working with Datasets). Thanks both of you for the help.

"Paul Clement" <Us***********************@swspectrum.comwrote in message
news:nf********************************@4ax.com...

On Wed, 25 Oct 2006 08:57:51 -0500, "Ryan" <Ty****@newsgroups.nospam>
wrote:

¤ I have 2 Access '97 databases. I am trying to migrate all the data from
one
¤ to the other (append to existing records). I do not have Access '97 and
¤ opening with Access XP or later causes problems. I have found I can
connect
¤ to the databases in Visual Studio 2005 and insert records, etc, so I'm
¤ trying to write my code using VB 2005.
¤
¤ I have 2 connections, 2 datasets... OldDS and NewDS. First I populate
both
¤ datasets:
¤ OldDS.TableAdapter1.Fill(OldDS.Table1)
¤ NewDS.TableAdapter1.Fill(NewDS.Table1)
¤ This works fine, I've queried the datasets, they are both populated with
¤ data from the tables, ready to go.
¤
¤ Here's where I have the problem, transferring data from the old dataset
into
¤ the new one. I've tried numerous approaches, here is the most recent
¤ attempt:
¤ For i As Integer = 0 To oldDS.Table1.Count - 1
¤
¤ ' Copy Row from oldDataSet, INSERT into newDataSet
¤
¤ Dim r As DataRow = oldDS.Table1.Rows(i)
¤
¤ newDS.Table1.AddTable1Row(r.Item(0), r.Item(1), r.Item(2), r.Item(3),
¤ r.Item(4))
¤
¤ Me.ProgressBar1.Increment(1)
¤
¤ Next
¤
¤ What happens is I get constraint errors.. type "DBNull" cannot be
converted
¤ to <insert type here>. I have checked both Datasets and constraints are
¤ OFF. Why would it do this? All fields should allow Null values, but
any
¤ Nulls in the Old table are causing all sorts of problems. I've set the
¤ default for Strings to (Empty) using the dataset designer and this seems
to
¤ help those fields but any other field type (DateTime for example) will
throw
¤ an exception.

Why not just use a SQL statement?

Open a connection to the destination database and specify the source
database path in your SQL
statement:

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=C:\Test
Files\destination.mdb")

AccessConn.Open()

AccessCommand.CommandText = "INSERT INTO [Data] SELECT * FROM [MS
Access;DATABASE=C:\Test
Files\source.mdb;].[Data]"
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)