Uploading data from access to SQL Server

[Einat Lapid]

Hi,

I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support SQL
Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from access_source_table".
This method worked fine but fails with wierd errors when the source table
was large around 1000000 rows). Even few Access MVP could not come with
something better then to read each row from Access and to generate
parametrised insert for that row in SQL Server. That worked fine but of
course was relativly slow.

Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample code is
below.

I just wonder how come nobody was able to point me to this kind of simple
solutiion ? Is there any caveats and hidden problems here ?

static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();

// Reader to source

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();

//bulk upload to destination //

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);

int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();

 

[MH]

Wouldn't it be easier to pull the data through to SQL Server rather than
trying to push it from Access?

DTS packages are designed to take all the work out of importing and
exporting in SQL Server.

 

['69 Camaro]

Hi.

I just wonder how come nobody was able to point me to this kind of simple
solutiion ?

I don't mean to embarrass you, but someone needs to tell you this: "If you
ask the wrong question, don't be surprised when you get the wrong answer."

Your post is likely to be ignored by most, because the solution you have
offered will not work in Access, the topic of this newsgroup. Your solution
requires a programming language that Access wasn't built to handle. (It
appears to be Java to me, but I understand that C# is Java-like.) No Access
expert is going to advise you to use this language within Access, so you
shouldn't be surprised at this. And don't be surprised that Access experts
don't know this other programming language. Why should they? These experts
are volunteers who freely offer their time and advice, not employees of
yours that you may require to learn certain skills if they want to continue
their employment with you.

Unless it's a trivial amount of data (1,000,000 rows is not trivial), one
doesn't "push" data from Access to SQL Server. The accepted practice is to
make the database server do the labor, not the client workstation. That
means executing a DTS package (which may use one of the five built-in tasks
or a custom procedure) or a stored procedure, implementing the DTS
Import/Export Wizard or writing a query in the Query Analyzer. (SSIS has
replaced DTS in the newest version of SQL Server, which was only released
four months ago, so most SQL Server experts haven't even had a chance to
work with this yet.) These are native to SQL Server, so one cannot expect
Access experts to know about these non-Access tools.

But you aren't using SQL Server, are you? In five posts, this is the first
one where you've mentioned that you are using SQL Server 2005 Express, which
doesn't have the Enterprise Manager and all the neat GUI tools built into
it. DTS and SSIS for SQL Server 2005 aren't available to you. Perhaps you
are using a third-party management tool GUI. Perhaps not. You cannot
expect the Access experts to know what tools you are using if you don't tell
them, and you cannot expect them to tell you how to use the non-Access tools
that were just recently published by Microsoft.

This method worked fine but fails with wierd errors when the source table
was large around 1000000 rows).

Does the workstation have the latest service packs installed for both Jet
and MDAC? Have you ensured that no Reserved words or illegal characters are
used for the names of tables, fields, queries or aliases within this query?
Have you ensured that the query isn't using a Cartesian Join? Have you
ensured that there's enough disk space available (2 GB max) in the temp
database file that Jet uses for executing queries? If all else fails, have
you ensured that no other applications are running while this query is being
executed?

Is there any caveats and hidden problems here ?

Access developers can't use this code within Access, because it uses the
wrong programming language. Since SQL Server 2005 Express is a newly
released product and few have experience with it as yet, you are likely to
discover any problems only after deployment. Earlier versions of SQL Server
could eat up disk space with logging info if the wrong database settings
were used for a bulk load, but I have no idea whether this is still the case
with your version, or whether the library your code is using avoids this
problem. I'm sure you'll get a better idea of what to look out for when you
ask this question in a more appropriate newsgroup.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hi,

I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support SQL
Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from access_source_table".
This method worked fine but fails with wierd errors when the source table
was large around 1000000 rows). Even few Access MVP could not come with
something better then to read each row from Access and to generate
parametrised insert for that row in SQL Server. That worked fine but of
course was relativly slow.

Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample code
is below.

I just wonder how come nobody was able to point me to this kind of simple
solutiion ? Is there any caveats and hidden problems here ?

static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();

// Reader to source

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();

//bulk upload to destination //

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);

int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();

 

[Einat Lapid]

Not realy - first I use SQLEXPRESS so I don't have DTS. Second I can't
assume SQL Server can connect back to the .MDB file that might be located on
the local hard drive.

Wouldn't it be easier to pull the data through to SQL Server rather than
trying to push it from Access?

DTS packages are designed to take all the work out of importing and
exporting in SQL Server.

Hi,

I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support
SQL Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from
access_source_table". This method worked fine but fails with wierd errors
when the source table was large around 1000000 rows). Even few Access MVP
could not come with something better then to read each row from Access
and to generate parametrised insert for that row in SQL Server. That
worked fine but of course was relativly slow.

Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample code
is below.

I just wonder how come nobody was able to point me to this kind of
simple solutiion ? Is there any caveats and hidden problems here ?

static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();

// Reader to source

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM "
+ TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader =
OleDbCommand.ExecuteReader();

//bulk upload to destination //

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);

int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();

 

[Brendan Reynolds]

SqlBulkCopy appears to be a new feature of .NET 2.0, so it isn't really very
surprising if no one suggested it in an Access forum. You're best bet would
probably be to ask about it in an ADO.NET forum such as
microsoft.public.dotnet.framework.adonet

--
Brendan Reynolds
Access MVP

Not realy - first I use SQLEXPRESS so I don't have DTS. Second I can't
assume SQL Server can connect back to the .MDB file that might be located
on the local hard drive.

Wouldn't it be easier to pull the data through to SQL Server rather than
trying to push it from Access?

DTS packages are designed to take all the work out of importing and
exporting in SQL Server.

Hi,

I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support
SQL Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from
access_source_table". This method worked fine but fails with wierd
errors when the source table was large around 1000000 rows). Even few
Access MVP could not come with something better then to read each row
from Access and to generate parametrised insert for that row in SQL
Server. That worked fine but of course was relativly slow.

Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample
code is below.

I just wonder how come nobody was able to point me to this kind of
simple solutiion ? Is there any caveats and hidden problems here ?

static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();

// Reader to source

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM "
+ TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader =
OleDbCommand.ExecuteReader();

//bulk upload to destination //

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);

int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0}
miliseconds", End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();