dottnett
چهارشنبه 13 مرداد 1389, 09:06 صبح
با سلام
برای وارد کردن اطلاعات از Access به sql server از کد زیر استفاده نمودم
Private Sub PerformImportToSql(ByVal Filename As String)
Dim table As DataTable = New DataTable
Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";User Id=; Password=;")
Dim sqlConnection As New SqlClient.SqlConnection("Data Source=name server;Initial Catalog=test;Integrated Security=True;")
Try
' Import the Ms Access
accConnection.Open()
Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * from jobs", accConnection)
accDataAdapter.Fill(table)
accConnection.Close()
For Each row As DataRow In table.Rows
row.SetAdded()
Next
'Export to MS SQL
sqlConnection.Open()
Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * from table_3 ", sqlConnection)
Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
sqlDataAdapter.Update(table)
sqlConnection.Close()
Catch ex As Exception
If accConnection.State = ConnectionState.Open Then
accConnection.Close()
End If
If sqlConnection.State = ConnectionState.Open Then
sqlConnection.Close()
End If
MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine & ex.ToString)
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim fileName As String = ""
Dim ofd As New OpenFileDialog
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
fileName = ofd.FileName
TextBox1.Text = fileName
PerformImportToSql(TextBox1.Text)
End If
End Sub
با این کد اطلاعات اطلاعات به داخل sql server اضافه می شود ولی مشکل من اینجاست که می خواهم اگر به داده های موجود در Access اضافه شد بتوانم اطلاعات جدید را به sql server اضافه نمایم ولی نمی دانم چه طور باید این کار را انجام دهم
برای وارد کردن اطلاعات از Access به sql server از کد زیر استفاده نمودم
Private Sub PerformImportToSql(ByVal Filename As String)
Dim table As DataTable = New DataTable
Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";User Id=; Password=;")
Dim sqlConnection As New SqlClient.SqlConnection("Data Source=name server;Initial Catalog=test;Integrated Security=True;")
Try
' Import the Ms Access
accConnection.Open()
Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * from jobs", accConnection)
accDataAdapter.Fill(table)
accConnection.Close()
For Each row As DataRow In table.Rows
row.SetAdded()
Next
'Export to MS SQL
sqlConnection.Open()
Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * from table_3 ", sqlConnection)
Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
sqlDataAdapter.Update(table)
sqlConnection.Close()
Catch ex As Exception
If accConnection.State = ConnectionState.Open Then
accConnection.Close()
End If
If sqlConnection.State = ConnectionState.Open Then
sqlConnection.Close()
End If
MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine & ex.ToString)
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim fileName As String = ""
Dim ofd As New OpenFileDialog
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
fileName = ofd.FileName
TextBox1.Text = fileName
PerformImportToSql(TextBox1.Text)
End If
End Sub
با این کد اطلاعات اطلاعات به داخل sql server اضافه می شود ولی مشکل من اینجاست که می خواهم اگر به داده های موجود در Access اضافه شد بتوانم اطلاعات جدید را به sql server اضافه نمایم ولی نمی دانم چه طور باید این کار را انجام دهم