Copy Table from Dataset to New Database

  • Thread starter Thread starter james
  • Start date Start date
J

james

I have searched until I cannot see straight anymore. ( I know, I have said that before) And have found all kinds of solutions
that just did not fit. I want to loop thru the data rows in a table in an in-memory dataset and copy the data to a new table in
a new Access database.
The empty table in the database has all the column names, Data types and Field sizes already set. All I need to do is copy the
contents of the in-memory table (from the dataset) to the new table.
The problem is, I am creating not just one new database, but, 63 new databases with single tables in them. I can create the new
table(s) "on the fly" and save the new , empty table to the new database created with ADOX. But, I cannot find a simple way to
copy the table from the in-memory dataset to the new table. Any suggestions on how to build a "generic" routine to copy one
table to another would greatly be appreciated.
james
 
james said:
I have searched until I cannot see straight anymore. ( I know, I have said
that before) And have found all kinds of solutions that just did not fit.
I want to loop thru the data rows in a table in an in-memory dataset and
copy the data to a new table in a new Access database.
The empty table in the database has all the column names, Data types and
Field sizes already set. All I need to do is copy the contents of the
in-memory table (from the dataset) to the new table.
The problem is, I am creating not just one new database, but, 63 new
databases with single tables in them. I can create the new table(s) "on
the fly" and save the new , empty table to the new database created with
ADOX. But, I cannot find a simple way to copy the table from the
in-memory dataset to the new table. Any suggestions on how to build a
"generic" routine to copy one table to another would greatly be
appreciated.
james
Click to expand...
Create a select command against your new table, and then use a DataAdapter
with a CommandBuilder to automagically flush your DataTable.

EG.

OleDbCommand cmd = new OleDbCommand("select * from myTable",con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = . . . ;
da.Update(dt);

David
 
Thank you David for the info. I guess there is no simple way to do what I am wanting to do.
What I actually need to do is copy the DATA from the table that is in memory and insert it into the new database which has a
prebuilt table that matches the in-memory table. (Column Header Names etc.)
As I stated, I have 63 different databases with single tables in them. And each database and table, has different schema
(Column Header names, Field types & Field sizes). What I have done so far is, to
load CSV files built from the old databases and use those to build the new Access Databases, minus the actual data. ( I could
find no way using ADOX to add the data along with the new table(s) and it's schema) So, I have been trying to add the data
after building the table(s) using OleDB commands.
The data exists in a table in a dataset called : objdataset1.
Here is what I have done so far:

This routine builds the actual database and table from the in=memory dataset:


Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim lngIndex As Long

With SaveFileDialog1

..Filter = "Access Database(*.mdb)|*.mdb"

End With

SaveFileDialog1.ShowDialog()

Dim cat As New ADOX.Catalog

Dim tbl As New ADOX.Table

Dim col As New ADOX.Column


Dim dr As DataRow

Dim colcount As Integer

Dim mycol As Column

Dim x As Integer 'for column count

Dim y As Integer ' for row count

Dim mytype As Type

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & SaveFileDialog1.FileName)

tbl.Name = txtDbName.Text

cat.Tables.Append(tbl)

''counts columns in dataset

colcount = objDataset1.Tables(0).Columns.Count

lngIndex = colcount

mytype = objDataset1.Tables(0).Columns.GetType

TextBox1.Text = colcount.ToString

For x = 0 To colcount - 1

col = New ADOX.Column

' sets each column's name in the new database

col.Name = objDataset1.Tables(0).Columns(x).ColumnName '.ToString

If col.Name = "RECNUM" Then

col.Type = DataTypeEnum.adInteger

col.DefinedSize = 50

col.ParentCatalog = cat

ElseIf col.Name = "CLIENTID" Then

col.Type = DataTypeEnum.adInteger

col.DefinedSize = 50

ElseIf col.Name = "REFID" Then

col.Type = DataTypeEnum.adInteger

col.DefinedSize = 50

ElseIf col.Name = "RECORDID" Then

col.Type = DataTypeEnum.adInteger

col.DefinedSize = 50

ElseIf col.Name = "NOTES" Then

col.Type = DataTypeEnum.adVarWChar

col.DefinedSize = 255

col.Attributes = ColumnAttributesEnum.adColNullable

ElseIf col.Name = "MAKE" Then

col.Type = DataTypeEnum.adVarWChar

col.DefinedSize = 255

col.Attributes = ColumnAttributesEnum.adColNullable

ElseIf Not col.Name = "" Then

col.Type = DataTypeEnum.adVarWChar

col.DefinedSize = 255

col.Attributes = ColumnAttributesEnum.adColNullable

End If

tbl.Columns.Append(col)

'trying to add data to table below


Next

cat.Tables.Refresh()


End Sub





Here is the seperate routine I am working on (and having no success) to add the data to the new database and table:



Private Sub btnAddData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddData.Click

'objDataset1.HasChanges()

Dim mytbl As String

mytbl = txtDbName.Text

Dim dsTable As String

dsTable = objDataset1.Tables(0).TableName

With OpenFileDialog1

..Filter = "Access Database(*.mdb)|*.mdb"

End With

OpenFileDialog1.ShowDialog()

conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" +
OpenFileDialog1.FileName + ";Mode=ReadWrite")

conn.Open()

If conn.State.ToString() = "Open" Then

comm = New OleDbCommand("INSERT * INTO mytbl FROM dsTable", conn)

da = New OleDbDataAdapter(comm)

ds = New DataSet(mytbl)

da.AcceptChangesDuringFill = False

Dim dr As DataRow

Dim aCol As Integer

Dim bRow As Integer

Dim i As Integer

Dim j As Integer

Dim myrow As DataRow

bRow = objDataset1.Tables(0).Rows.Count

aCol = objDataset1.Tables(0).Columns.Count

For i = 0 To objDataset1.Tables(0).Rows.Count - 1

For j = 0 To objDataset1.Tables(0).Columns.Count - 1

myrow = ds.Tables(mytbl).NewRow() ' THIS IS WHERE THE ERROR IS THROWN

Dim cellValue As Object = Me.DataGrid1(i, j)

myrow.Item(j) = cellValue.ToString

Next

ds.Tables(mytbl).Rows.Add(myrow)

Next

End If

Try





da.Update(ds, mytbl)

conn.Close()

Catch oledbe As OleDbException

MessageBox.Show(oledbe.Message)

End Try

MessageBox.Show("Record Added")

End Sub




And of course, it fails at: myrow = ds.Tables(mytbl).NewRow()

with this error message:

Additional information: Object reference not set to an instance of an object

The table mytbl is the name of the table in the new database that I am trying to add the data to. So, I don't understand why it
is throwing an error. And dsTable is the table in the dataset,,, objDataset1.Tables(0) that is in memory and also displayed in
a datagrid on my form.

It looks like I will need to generate a new INSERT statement for each database that I create and load, on the fly. But, I am
lost as to how to go about that. And since the field names etc. can vary, it gets even more complicated. Maybe, I should just
be happy and allow Access to do all the hard work instead of trying to build my own import routine.

james
 
Back
Top