"Marcus" <ho**********@hotmail.com> schrieb
I created a VB.Net 1.1 application that iterates through all the
tables in any basic Access 2000 database passed to it and generates
the same table structure in a SQL Server Express database. The
structure is created fine (with minor data conversions from one to
the other, e.g. yes/no --> bit, memo --> text, etc). My problem now
is transferring the data over from Access to SQL Server. I thought
it would be a fairly straight forward process, but I don't think I
thought it through that well, unless I am missing something. I
currently have the code retrieving a DataTable object from Access
one at a time. The only way I can think of now to get the data into
SQL Server is to create a DataAdapter for each table in SQL Server
and create an InsertCommand for each of adapters. This will involve
iterating through all the columns of each table, determing their
data types and length, and then adding the parameters. Is this what
I have to do, or is there a shorter method? Perhaps dump the Access
table to a file and then use bulk import utility (bcp) for
example??? Hmmm...
Not exactly what you are looking for, but this is the (quick&dirty) code
that I wrote to import the whole database. Therefore you would have to
adjust the data types again. You can also change the code to use "insert
into" instead of "select into" statements to import the data only.
1. Add a "linked server" to the SQL server instance. It's a link to the
source database.
2. On a Form, add a multiline textbox named 'txtLog' and a button named
'Button1'.
3. Add an SqlConnection named 'SqlConnection1'. This is the destination
database.
4. Insert the code below. Change the value of the constant 'linkedServer' to
the name of your linked server.
Const linkedServer As String = "testMDB"
Private Sub Button1_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Dim tables As New ArrayList
Windows.Forms.Cursor.Current = Cursors.WaitCursor
Me.txtLog.Text = String.Empty
Me.SqlConnection1.Open()
Try
'get all tables
cmd = New SqlCommand("sp_tables_ex", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
.Add("@table_server", SqlDbType.NVarChar).Value = linkedServer
.Add("@table_type", SqlDbType.NVarChar).Value = "TABLE"
End With
reader = cmd.ExecuteReader()
Try
Do While reader.Read
tables.Add(reader("TABLE_NAME").ToString)
Loop
Finally
reader.Close()
End Try
'import all tables
For Each tname As String In tables
Me.txtLog.AppendText(tname & "... ")
Me.txtLog.SelectionStart = Me.txtLog.Text.Length
Try
Dim cmd2 As New SqlCommand
Dim count As Integer
cmd2.CommandText = "select * into [" & tname _
& "] from " & linkedServer & "...[" & tname & "]"
cmd2.Connection = Me.SqlConnection1
cmd2.CommandTimeout = 3600 '60 Min.
count = cmd2.ExecuteNonQuery
Me.txtLog.AppendText(count.ToString & vbCrLf)
Me.txtLog.SelectionStart = Me.txtLog.Text.Length
Catch ex As Exception
Me.txtLog.AppendText(ex.Message & vbCrLf)
Me.txtLog.SelectionStart = Me.txtLog.Text.Length
End Try
Refresh()
AZ.Win32.PeekMessage(Nothing, Nothing, 0, 0, 0)
Next
Finally
Me.SqlConnection1.Close()
Windows.Forms.Cursor.Current = Cursors.Default
End Try
End Sub
Maybe there's a simpler (built-in) way, but when I wrote the code I wanted
to learn how to do it on my own.
Armin