473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

tranferring data from Access to SQL Server Express

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

Mar 4 '06 #1
15 4704
Marcus,

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

Kerry Moorman
"Marcus" wrote:
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 ***

Mar 4 '06 #2
"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

Mar 4 '06 #3
Marcus,

Did you try this sample on our website already?

http://www.vb-tips.com/default.aspx?...5-421ed535c609

I hope this helps,

Cor

"Marcus" <ho**********@hotmail.com> schreef in bericht
news:11**********************@j33g2000cwa.googlegr oups.com...
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 ***

Mar 4 '06 #4
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

Mar 5 '06 #5
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

Mar 5 '06 #6
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.
sigh<<.


Cheers,
Marcus

Mar 5 '06 #7
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" <ho**********@hotmail.com> schreef in bericht
news:11**********************@v46g2000cwv.googlegr oups.com...
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.
sigh<<.


Cheers,
Marcus

Mar 5 '06 #8
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

Mar 5 '06 #9
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?...a-6680e677e994

To list the Access tables
http://www.vb-tips.com/default.aspx?...c-e9d7983c1e05

Get the Access Schema to read it (is in this sample)
http://www.vb-tips.com/default.aspx?...6-fc0d5c470f53
I hope this helps a little bit.

Cor

..
"Marcus" <ho**********@hotmail.com> schreef in bericht
news:11**********************@t39g2000cwt.googlegr oups.com...
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

Mar 6 '06 #10
On 3 Mar 2006 16:47:49 -0800, "Marcus" <ho**********@hotmail.com> wrote:

¤ 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)
Mar 6 '06 #11
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

Mar 7 '06 #12
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

Mar 7 '06 #13
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

Mar 7 '06 #14
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
Mar 7 '06 #15
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

Mar 8 '06 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

33
by: Uwe Range | last post by:
Hi to all! A customer of mine told me some days ago that her IT-people told her ACCESS would not be such a good idea for continuing with our project, because Access will not be continued in the...
16
by: Andy_Khosravi | last post by:
I'm in a bit of a pickle. My employer, a health insurance firm, had me design a small database to track benefit issues. The intended users were technical specialists and some managers (about 90...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
12
by: Chris Springer | last post by:
I'd like to get some feedback on the issue of storing data out to disk and where to store it. I've never been in a production environment in programming so you'll have to bear with me... My...
3
by: VB.NET | last post by:
I'm using a mysql database and connecting my vb.net program to the DB over a network connection. i would like to bring this data over to a vb.net random access file. does anyone know how to...
1
by: vaj | last post by:
Hi everyone, Im not looking to tranfer the datasets on a CE device to a remote server. Tried RDA but my sql server isnt compatible with the version of SQL server CE I have on the device.So what...
5
by: Ted | last post by:
I am working on two versions of an application, one of which will be a windows forms application (which will need to be redistributable) and the other will be a web application. I have MS Visual...
8
by: Lykins | last post by:
We currently use Access 2003 in our company and have had this issues from every version from Access 97 to 2003. We deal with large databases and run a lot of queries over tables with millions of...
0
Dököll
by: Dököll | last post by:
Continued from: http://www.thescripts.com/forum/thread762010.html -VB 6.0 Professional -Microsoft DAO 3.6 Reference Search Database table... An attempt to fetch data housed in Access:
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.