I had a code which uses data view to transfer from Access to SQL Server. All you have to modify to transfer from SQL Server to Access, is to change OleDB* variables (objects) to Sql* variables (objects) and vice versa.
Read Access table to data view:
Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
ByRef oView As DataView, _
ByVal SelectSQL As String, ByVal WhereSQL As String)
'
' Export Access table to data view
Dim strSQL As String
Dim oRS As DataSet
Dim oConnOleDb As OleDbConnection
Dim oCmdOleDb As OleDbCommand
Dim oDAOleDb As OleDbDataAdapter
Try
strSQL = ""
oCmdOleDb = Nothing
oDAOleDb = Nothing
oConnOleDb = New OleDbConnection(ConnectionStr)
oCmdOleDb = oConnOleDb.CreateCommand()
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
If WhereSQL.Length > 0 Then
strSQL = strSQL & " WHERE " & WhereSQL
End If
' Execute
oCmdOleDb.CommandText = strSQL
oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
oRS = New DataSet
oDAOleDb.Fill(oRS, TableName)
oView = New DataView(oRS.Tables(0))
oConnOleDb.Close()
oConnOleDb = Nothing
oDAOleDb = Nothing
oRS = Nothing
oCmdOleDb = Nothing
Catch ex As Exception
End Try
End SubAnd insert data view to SQL Server:
Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
ByRef oImportView As DataView, _
ByVal SelectSQL As String, ByVal WhereSQL As String)
'
' Import table from data view
Dim strSQL As String
Dim oConn As SqlConnection
Dim oCmd As SqlCommand
Dim oDA As SqlDataAdapter
Dim oRS As DataSet
Dim oView As DataView
Try
strSQL = ""
oConn = New SqlConnection(ConnectionStr)
oConn.Open()
oCmd = Nothing
oDA = Nothing
oCmd = oConn.CreateCommand()
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
If WhereSQL.Length > 0 Then
strSQL = strSQL & " WHERE " & WhereSQL
End If
' Execute
oCmd.CommandText = strSQL
oDA = New SqlDataAdapter(oCmd)
oRS = New DataSet
oDA.Fill(oRS, TableName)
oView = New DataView(oRS.Tables(0))
' Set rowstates so that rows are inserted
oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
' The trick is to use CommandBuilder to create INSERT statement "automatically"
Dim oCmdBuilder As SqlCommandBuilder
oCmdBuilder = New SqlCommandBuilder(oDA)
' Finally call update to commit changes to database table
oDA.Update(oView.Table)
oConn.Close()
oConn = Nothing
oDA = Nothing
oRS = Nothing
oCmd = Nothing
Catch ex As Exception
End Try
End SubAnd here's how you use it
Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")Just provide connection strings and table name(s). Naturally you should handle errors properly etc.
