tranferring data from Access to SQL Server Express

[Marcus]

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...

Thanks for any help,
Marcus

*** Please respond to this group. I do not check this email address ***

 

[Armin Zingler]

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

 

[Guest]

Marcus,

The easiest way to get an Access 2000 database into SQL Server is to use
Access's Upsizing Wizard.

Kerry Moorman

 

[Cor Ligthert [MVP]]

Marcus,

Did you try this sample on our website already?

http://www.vb-tips.com/default.aspx?ID=65217d17-ac72-44f3-9d35-421ed535c609

I hope this helps,

Cor

 

[Marcus]

Thanks for your reply, Kerry. The Upsizing Wizard is not an option as
this is part of a VB.Net application on a machine that will not have
the Access runtime. This must all happen "automagically", i.e. the code
grabs any number of mdb files (structure unknown prior to running the
code) and generates the equivalent SQL Server Express database for
each, as well as copies over the data. I have coded the
structure-building part and that works fine. The problem I am at now is
getting the data over. Each table that is copied over has exactly the
same number of columns at the destination with the same names, and as
close to the same data types as possible given the subtle differences
between Jet and Sql server datatypes. I was hoping I would be able to
take the data table from each Access and just "shoe-horn" it into the
SQL Server table. But the only way I can see how to do this currently
is to create a dataadapter for each table with all the various
parameters types for each row and then create an insertcommand. This
will be a big headache for me I think... >>sigh<<

Marcus

 

[Marcus]

Thanks for you code, Armin. Unfortunately, I don't think I will be able
to make a linked server in my case as the Access database will be
elsewhere on the network and likely not available directly.

If I looped through all the tables in the Access DB and put that data
into a ado.net datatable for each, I thought that it should be fairly
easy to just "shoe horn" each of those datatables into the appropriate
SQL Server database table that has the exact same number of columns and
as close to the same datatypes as the source Access DB. But I am
getting the feeling that I am going to have to create a dataadapter for
each of these table with an command object that has all of the
sqlparameters for all the different kinds of columns that each table
will have. That will involve a lot of coding iterating through all the
tables and creating the appropriate parameter types for the
insertcommand. Am I missing something here?

Cheers,
Marcus

 

[Marcus]

Thanks for your suggestion, Cor. You've got a good sight there. It
looks like SqlBulkCopy is a great feature of ADO.Net 2.0. Unfortunately
I am using 1.1. Do you know of any way to accomplish copying a
datatable into a SQL Server table with the same structure (exactly the
same # of columns, same column names, and same datatypes)? Do I really
need to create a dataadapter for each table and determine all the
datatypes of each of the columns and created the parameter datatypes
and then add an insertcommand? This seems like an awful lot of coding.
Cheers,
Marcus

 

[Cor Ligthert [MVP]]

Marcus,

It seems that this is again on the wishlist for the next version after 2005
(To create a database from an XSD or an XML)

AFAIK is there now not an easy solution for this.

Cor

 

[Marcus]

I am not at work so can't check this out, but I was wondering if using
the SqlCommandBuilder would be able to help me out. What I was thinking
was to create a DataAdapter for my destination table (which is empty)
and create a SelectCommand object with a "select * from [tablename]"
for this da. This of course would not return anythingon the fill, but
then at that point could I not create a SqlCommandBuilder with this
dataadapter to build the insertcommand? I am trying to get away from
manually building the insertcommand and having to figure out during
runtime what all the parameters and their datatypes need to be.

Cheers,
Marcus

 

[Cor Ligthert [MVP]]

Marcus,

Of course you can, however first you have to build the tables on the
SQLServer the rest is in fact a piece of cake.

(Be aware that you do in advance of the updating than a fillschema or just a
fill and place after that the data in the dataset, so that the data will be
insterted).

To create tables on a SQL Server
http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

To list the Access tables
http://www.vb-tips.com/default.aspx?ID=26f91edd-044c-4e71-8c6c-e9d7983c1e05

Get the Access Schema to read it (is in this sample)
http://www.vb-tips.com/default.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53


I hope this helps a little bit.

Cor

..

 

[Paul Clement]

¤ 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...
¤

If you have already set up the table structures on the SQL Server side there is no need to export
each row individually. You can accomplish this by connecting to the Access database and using a SQL
statement such as the one below:

INSERT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1] SELECT * FROM AccessTable

Keep in mind that OLE Object and Memo columns are special cases so you will probably have to use a
different export method.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Marcus]

Thanks, Paul, that worked. However, I would like to do it via the
following method, and for the life of me I don't know why this is not
working. I have the exact same table names and columns in the SQL
Server, but they are all empty. I want to transfer the data from the
Access tables to the SQL Server tables. Here is my code for daeling
with one table at a time:

Public Function delete_me(ByVal mySourceDataTable As DataTable) As
Boolean
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim cmd As New SqlCommand
Dim DestinationTableName As String =
mySourceDataTable.TableName

cmd.CommandText = "SELECT * FROM " + DestinationTableName
cmd.Connection = conn
da.SelectCommand = cmd

'create a SQLCommandBuilder to auto generate InsertCommand
Dim cb As New SqlCommandBuilder(da)

conn.Open()

da.Fill(ds)

For Each dr As DataRow In mySourceDataTable.Rows
ds.Tables(0).ImportRow(dr)
Next

If ds.HasChanges() Then
MessageBox.Show("DS has been changed")
'da.Update(ds)
End If

da.Update(ds)

conn.Close()

End Function


For some reason, the ds.hasChanges() is never true. When I have a look
at the destination datatable that the rows are being impored into
(ds.Tables(0) ), all the data is there. How can I get the update to
occur??!?

Thanks again,
Marcus

 

[Marcus]

Thanks, Cor. Yes, I have already created the database structure in my
VB.Net code on SQL Server. However, I am still having trouble actually
transferring the data from Access to SQL Server (so far not a piece of
cake for ME!!). Please have a look at my response to Paul Clement's
post in this thread on March 6.

Cheers,
Marcus

 

[Marcus]

P.S.

I have seen references in various posts to setting
"AcceptChangesDuringFill" to false for the dataadapter. However, this
has no effect in my code and ds.haschanges is still always false.

Marcus

 

[Cor Ligthert [MVP]]

Marcus,

I forgot to tell you about that AcceptChangesDuringFill, sorry (probably you
have seen that I have adviced that very often)

However you have to set that before the Fill of the Original Table.

(I believe that you can use direct your original table to update withouth
that looping).

You need the fill of the destination in my opinion only to get the
information for the commandbuilder.

(Although I am now even in doubt if that is really needed, I get more and
more the idea that I have forever misinterpreted that and that the
commandbuilder visits the server himself).

Sorry about the misinformation.

Cor

 

[Marcus]

Unfortunately, setting AcceptChangesDuringFill = false ahead of the
dataadapter fill still has no affect on the update (ds.haschanges is
still = false) when I import a row. I got around this by creating a new
row and then adding it to the datatable. These are the steps I took:

1. Create a dataAdapter for the destination table, using "Select * from
[DestinationTable]"
2. Create a commandbuilder object using this dataAdapter. This will
retrieve the schema from the DestinationTable, and generate the
InsertCommand (and other command object) with the proper CommandText,
and associated parameters.
3. Fill a new dataset with the dataadapter. This will not return any
data into the dataset as the destination table is currently empty, but
it will create the schema in the dataset.
4. Loop through the datarows of the source table. For each iteration,
create a new datarow of table(0) or the dataset (i.e. myDataRow =
ds.Tables(0).NewRow). Loop through the columns of the source datarow
and copy the column values from the source to the new row. Add the new
row to the dataset table (i.e. ds.Tables(0).Rows.Add(myDataRow)).
Adding this new row WILL set the ds.haschanges to TRUE.
5. After iterating though all of the data rows of the source table,
call the dataAdapter.update(ds) to update the changes to the database.

However, now I have encountered ANOTHER PROBLEM!! In the source Access
database, most of the tables have an autonumber primary key. The code
will create an Identity datatype for that table over on SQL Server. All
good so far. The problem arises when I want to transfer over the exact
data from Access to SQL (I need the same values in the autonumber field
to be copied over to the Identity field in order to maintain foreign
key relationships). The autonumber values are NOT copied, instead they
are generated on the SQL side starting at one and sequentially working
up. This is done because the insertcommand created by the
commandbuilder does not include the identity column. I have seen others
post the same problem. Setting "SET INDENTIY_INSERT tablename ON" will
not matter as the insertcommand does not even include the parameter for
the identity field. It is looking like I will have to create the
insertcommand and all the associated parameters with my own code
dynamically. I was REALLY hoping that I didn't need to do this as that
will be a headache. Does anyone have any other thoughts on how I can
avoid this route?

I will say that currently my solution is working using Paul's solution:

(INSERT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1]
SELECT * FROM AccessTable )

But I really would prefer to utilize a more object-oriented
DataTable-->Datatable transfer.

Cheers,
Marcus