Title: VB.net copy table from 1 database (ODBC) to another (Access) without using select into
Tags: database;vb.net;copy
Question: I am using VB.Net and need to copy 9 tables from an ODBC connection (Gupta database) to an Access database.
I can not use the sql statement SELECT INTO..... as the odbc connection doesn't support this.
In vb6 I have used:
Set rsCopierenTabel = New Recordset
sqlCopierenTabel = "SELECT * FROM " &amp; tblTabel
rsCopierenTabel.Open sqlCopierenTabel, conUnit4, adOpenKeyset, adLockOptimistic
If rsCopierenTabel.RecordCount &gt; 0 Then
rsCopierenTabel.MoveFirst
Set rsPlakkenTabel = New Recordset
sqlPlakkenTabel = "SELECT * FROM " &amp; strTabel
rsPlakkenTabel.Open sqlPlakkenTabel, conAccess, adOpenKeyset, adLockOptimistic
Do Until rsCopierenTabel.EOF
rsPlakkenTabel.AddNew
For i = 0 To rsCopierenTabel.Fields.Count - 1
rsPlakkenTabel.Fields(i) = rsCopierenTabel.Fields(i)
Next i
rsPlakkenTabel.Update
rsCopierenTabel.MoveNext
Loop
rsPlakkenTabel.Close
Set rsPlakkenTabel = Nothing
End If
rsCopierenTabel.Close
Set rsCopierenTabel = Nothing
But this is for vb6 and now I am using VB.Net.
Isn't there an easy way to do this?
I have looked into datasets which have a link to the connection, but always to the same connection as in which they were filled.
Could somebody point me to the right way?
I don't want to loop the columns as I don't know the names of the columns.
(that is why fields(i) are so easy)
Thanks in advance,

Here is the accepted answer: You could read the source tables in a DataSet, that would hold the 9 DataTable objects.  The rows in those DataTable objects would all have a RowState of Unchanged.  Loop the rows in each DataTable and use the SetAdded method on each row.
Once the rows are in an Added row state you can use the following code block to save them to your DB:
Private Sub UpdateAddedRows(MyDbAdapter As DbDataAdapter, dt As DataTable)
Dim AddedRows As DataRow() = dt.[Select](Nothing, Nothing, DataViewRowState.Added)
If AddedRows.Length &gt; 0 Then
Dim InsertCommand As DbCommand = NewDbCommand()
Dim SQL As String = "INSERT INTO " &amp; Convert.ToString(dt.TableName) &amp; " ("
Dim Vals As String = ""
Dim dc As List(Of String) = ColumnList(dt)
For dcInx As Integer = 0 To dc.Count - 1
Dim p As DbParameter = DataColumnParameter(dt.Columns(dc(dcInx)))
InsertCommand.Parameters.Add(p)
If dcInx &gt; 0 Then
SQL += ", "
Vals += ", "
End If
SQL += dc(dcInx)
Vals += ParamNameDecorated(p.ParameterName)
Next
SQL += ") VALUES (" &amp; Vals &amp; ")"
InsertCommand.CommandText = SQL
MyDbAdapter.InsertCommand = InsertCommand
MyDbAdapter.Update(AddedRows)
End If
End Sub
This code block can be called to read one of the source tables:
Private Function GetData(ByRef ds As DataSet, TableName As String, SQL As String, pst As Boolean) As DataSet
Dim MyDbAdapter As DbDataAdapter = NewDbDataAdapter()
Dim MyDbCommand As DbCommand = NewDbCommand()
MyDbCommand.CommandText = SQL
If ds Is Nothing Then
ds = New DataSet()
End If
If ds.Tables.Contains(TableName) Then
ds.Tables.Remove(TableName)
End If
Dim dt As New DataTable(TableName)
ds.Tables.Add(dt)
MyDbAdapter.SelectCommand = MyDbCommand
MyDbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
MyDbAdapter.Fill(ds, TableName)
Catch ex As Exception
Debug.Print("Exception getting data. " + ex.Message)
Throw
End Try
Return ds
End Function
Comment for this answer: Thank you, I have a datatable of the original database table but didn't change the rowstate so the table didn't get updated when I save it into the new database. Now I know why.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:05/15/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<database><vb.net><copy>', 'Title': 'VB.net copy table from 1 database (ODBC) to another (Access) without using select into', 'ViewCount': 2946, 'id': 23681577, 'user_AboutMe': '', 'user_AccountId': 3715150.0, 'user_DisplayName': 'Brian', 'user_Reputation': 33.0, 'user_Views': 1.0, 'user_id': 3091554.0}}