Creating an Access database from a SQL Database

[RSH]

Hi,

I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the job
well, but it seems a bit slow. Is there anything that i can do to speed the
app up a bit?

namespace SQLToAccessBackup

{

class SqlToAccessConvertor

{

// RSH 3/23/2006

// This module creates an Access database from a SQL database

// It requires blank database (will not be destroyed) that has the

// same schema as it's SQL counterpart

// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb

public SqlConnection cnSQL;

public OleDbConnection cnAccess;

public String strError = "";

public long lCounter;

public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)

{
int iTotalRecords = 0;

String strDatabasePath = "";

String strSQL = "";

String dStartTime = "";

String dFinishTime = "";

dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))

{

File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");

File.Delete(DataPath + Database + ".mdb");

}

File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");

strDatabasePath = DataPath + Database + ".mdb";

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");

cnAccess.Open();

cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);

cnSQL.Open();

// START LOOP OF ALL SQL TABLES HERE

strSQL = "USE [" + Database + "]";

SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);

myCommand.ExecuteNonQuery();

strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);

DataSet dsSQLTables = new DataSet();

daSQLTables.Fill(dsSQLTables);

int iTotalTables = dsSQLTables.Tables[0].Rows.Count;

int iCurTableIdx = 0;

lCounter = 0;

string strCurTable = "";

Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " + dsSQLTables.Tables[0].Rows.Count.ToString());

foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)

{

strCurTable = drSQLTables[0].ToString();

iCurTableIdx++;

// ACCESS

String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);

cbDevelopment.QuotePrefix = "[";

cbDevelopment.QuoteSuffix = "]";

// SQL

String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";

SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);

DataSet dsSQLServer = new DataSet();

daSQLServer.Fill(dsSQLServer);

int j = 1;

int maxRecords = dsSQLServer.Tables[0].Rows.Count;

foreach (DataRow dr in dsSQLServer.Tables[0].Rows)

{

DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

j++;
if (lCounter == 1000)

{

Console.WriteLine("Processing Table: " + strCurTable + "(" + (iCurTableIdx)
+ ")" + " Row " + (j - 2) + " of " + maxRecords);

lCounter = 0;

}

lCounter++;

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

iTotalRecords++;

if (dsSQLServer.Tables[0].Columns[i].ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{

daAccess.Update(dsAccess, "DT");

}

else

{

// Write to error log

strError = drAccessAdd.RowError.ToString() + "\\n";

}

}

}

dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

MailMessage objMsg = new MailMessage();

objMsg.To = "rh******@timeplus.com";

objMsg.From = "rh******@timeplus.com";

objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
" -- Sql to Access backup conversion job results.";

objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records were
written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;

SmtpMail.SmtpServer = "SMTPSERVER1";

SmtpMail.Send(objMsg);

cnAccess.Close();

cnAccess.Dispose();

cnSQL.Close();

cnSQL.Dispose();

if (strError.Length 1)

{

String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";

TextWriter tw = new StreamWriter(strFilename);

tw.WriteLine(strError);

tw.Close();

}

}

}

}

Thanks for any help you might be able to offer!

RSH

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

How much data are you talking about?

You could have an empty access created already and then just import the
data.
If you have a considerable amount of data then creating a DTS is a good
idea, you can run the DTS from the code without any problem
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"RSH" <wa*************@yahoo.comwrote in message
news:uY**************@TK2MSFTNGP05.phx.gbl...

Hi,

I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the
job well, but it seems a bit slow. Is there anything that i can do to
speed the app up a bit?

namespace SQLToAccessBackup

{

class SqlToAccessConvertor

{

// RSH 3/23/2006

// This module creates an Access database from a SQL database

// It requires blank database (will not be destroyed) that has the

// same schema as it's SQL counterpart

// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb

public SqlConnection cnSQL;

public OleDbConnection cnAccess;

public String strError = "";

public long lCounter;

public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)

{
int iTotalRecords = 0;

String strDatabasePath = "";

String strSQL = "";

String dStartTime = "";

String dFinishTime = "";

dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))

{

File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");

File.Delete(DataPath + Database + ".mdb");

}

File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");

strDatabasePath = DataPath + Database + ".mdb";

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");

cnAccess.Open();

cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);

cnSQL.Open();

// START LOOP OF ALL SQL TABLES HERE

strSQL = "USE [" + Database + "]";

SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);

myCommand.ExecuteNonQuery();

strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);

DataSet dsSQLTables = new DataSet();

daSQLTables.Fill(dsSQLTables);

int iTotalTables = dsSQLTables.Tables[0].Rows.Count;

int iCurTableIdx = 0;

lCounter = 0;

string strCurTable = "";

Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " +
dsSQLTables.Tables[0].Rows.Count.ToString());

foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)

{

strCurTable = drSQLTables[0].ToString();

iCurTableIdx++;

// ACCESS

String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);

cbDevelopment.QuotePrefix = "[";

cbDevelopment.QuoteSuffix = "]";

// SQL

String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";

SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);

DataSet dsSQLServer = new DataSet();

daSQLServer.Fill(dsSQLServer);

int j = 1;

int maxRecords = dsSQLServer.Tables[0].Rows.Count;

foreach (DataRow dr in dsSQLServer.Tables[0].Rows)

{

DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

j++;
if (lCounter == 1000)

{

Console.WriteLine("Processing Table: " + strCurTable + "(" +
(iCurTableIdx) + ")" + " Row " + (j - 2) + " of " + maxRecords);

lCounter = 0;

}

lCounter++;

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

iTotalRecords++;

if (dsSQLServer.Tables[0].Columns[i].ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{

daAccess.Update(dsAccess, "DT");

}

else

{

// Write to error log

strError = drAccessAdd.RowError.ToString() + "\\n";

}

}

}

dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

MailMessage objMsg = new MailMessage();

objMsg.To = "rh******@timeplus.com";

objMsg.From = "rh******@timeplus.com";

objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year
+ " -- Sql to Access backup conversion job results.";

objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records
were written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;

SmtpMail.SmtpServer = "SMTPSERVER1";

SmtpMail.Send(objMsg);

cnAccess.Close();

cnAccess.Dispose();

cnSQL.Close();

cnSQL.Dispose();

if (strError.Length 1)

{

String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";

TextWriter tw = new StreamWriter(strFilename);

tw.WriteLine(strError);

tw.Close();

}

}

}

}

Thanks for any help you might be able to offer!

RSH

[sloan]

Isn't there a way to ...

Insert Into (mdb)Customers Select * from (sqlserver) Customers

?

I used to do this in DAO....

I think your code is doing a row by row. Which may be where the slowness is
coming from. (aka, the default dataAdapter behavior)

Sorry I don't know more, but I ran into this issue back in 1998, and using
DAO was able to wholesale copy the 3 or 4 tables I needed.



"RSH" <wa*************@yahoo.comwrote in message
news:uY**************@TK2MSFTNGP05.phx.gbl...

Hi,

I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the

job

well, but it seems a bit slow. Is there anything that i can do to speed

the

app up a bit?

namespace SQLToAccessBackup

{

class SqlToAccessConvertor

{

// RSH 3/23/2006

// This module creates an Access database from a SQL database

// It requires blank database (will not be destroyed) that has the

// same schema as it's SQL counterpart

// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb

public SqlConnection cnSQL;

public OleDbConnection cnAccess;

public String strError = "";

public long lCounter;

public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)

{
int iTotalRecords = 0;

String strDatabasePath = "";

String strSQL = "";

String dStartTime = "";

String dFinishTime = "";

dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))

{

File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");

File.Delete(DataPath + Database + ".mdb");

}

File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");

strDatabasePath = DataPath + Database + ".mdb";

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");

cnAccess.Open();

cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);

cnSQL.Open();

// START LOOP OF ALL SQL TABLES HERE

strSQL = "USE [" + Database + "]";

SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);

myCommand.ExecuteNonQuery();

strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);

DataSet dsSQLTables = new DataSet();

daSQLTables.Fill(dsSQLTables);

int iTotalTables = dsSQLTables.Tables[0].Rows.Count;

int iCurTableIdx = 0;

lCounter = 0;

string strCurTable = "";

Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " +

dsSQLTables.Tables[0].Rows.Count.ToString());

>


foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)

{

strCurTable = drSQLTables[0].ToString();

iCurTableIdx++;

// ACCESS

String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);

cbDevelopment.QuotePrefix = "[";

cbDevelopment.QuoteSuffix = "]";

// SQL

String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";

SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);

DataSet dsSQLServer = new DataSet();

daSQLServer.Fill(dsSQLServer);

int j = 1;

int maxRecords = dsSQLServer.Tables[0].Rows.Count;

foreach (DataRow dr in dsSQLServer.Tables[0].Rows)

{

DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

j++;
if (lCounter == 1000)

{

Console.WriteLine("Processing Table: " + strCurTable + "(" +

(iCurTableIdx)

+ ")" + " Row " + (j - 2) + " of " + maxRecords);

lCounter = 0;

}

lCounter++;

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

iTotalRecords++;

if (dsSQLServer.Tables[0].Columns[i].ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{

daAccess.Update(dsAccess, "DT");

}

else

{

// Write to error log

strError = drAccessAdd.RowError.ToString() + "\\n";

}

}

}

dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

MailMessage objMsg = new MailMessage();

objMsg.To = "rh******@timeplus.com";

objMsg.From = "rh******@timeplus.com";

objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year

+

" -- Sql to Access backup conversion job results.";

objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records

were

written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;

SmtpMail.SmtpServer = "SMTPSERVER1";

SmtpMail.Send(objMsg);

cnAccess.Close();

cnAccess.Dispose();

cnSQL.Close();

cnSQL.Dispose();

if (strError.Length 1)

{

String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";

TextWriter tw = new StreamWriter(strFilename);

tw.WriteLine(strError);

tw.Close();

}

}

}

}

Thanks for any help you might be able to offer!

RSH

[RSH]

It is a significant amount of data (roughly 150 mb).
"RSH" <wa*************@yahoo.comwrote in message
news:uY**************@TK2MSFTNGP05.phx.gbl...

Hi,

I have a situation where I have created a little application that makes an
Access database from a SQL Database for reporting purposes. it does the
job well, but it seems a bit slow. Is there anything that i can do to
speed the app up a bit?

namespace SQLToAccessBackup

{

class SqlToAccessConvertor

{

// RSH 3/23/2006

// This module creates an Access database from a SQL database

// It requires blank database (will not be destroyed) that has the

// same schema as it's SQL counterpart

// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb

public SqlConnection cnSQL;

public OleDbConnection cnAccess;

public String strError = "";

public long lCounter;

public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)

{
int iTotalRecords = 0;

String strDatabasePath = "";

String strSQL = "";

String dStartTime = "";

String dFinishTime = "";

dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))

{

File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");

File.Delete(DataPath + Database + ".mdb");

}

File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");

strDatabasePath = DataPath + Database + ".mdb";

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + "Data
Source=" + strDatabasePath + ";Persist Security Info=False");

cnAccess.Open();

cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);

cnSQL.Open();

// START LOOP OF ALL SQL TABLES HERE

strSQL = "USE [" + Database + "]";

SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);

myCommand.ExecuteNonQuery();

strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);

DataSet dsSQLTables = new DataSet();

daSQLTables.Fill(dsSQLTables);

int iTotalTables = dsSQLTables.Tables[0].Rows.Count;

int iCurTableIdx = 0;

lCounter = 0;

string strCurTable = "";

Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " +
dsSQLTables.Tables[0].Rows.Count.ToString());

foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)

{

strCurTable = drSQLTables[0].ToString();

iCurTableIdx++;

// ACCESS

String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);

cbDevelopment.QuotePrefix = "[";

cbDevelopment.QuoteSuffix = "]";

// SQL

String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH (NOLOCK)";

SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);

DataSet dsSQLServer = new DataSet();

daSQLServer.Fill(dsSQLServer);

int j = 1;

int maxRecords = dsSQLServer.Tables[0].Rows.Count;

foreach (DataRow dr in dsSQLServer.Tables[0].Rows)

{

DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

j++;
if (lCounter == 1000)

{

Console.WriteLine("Processing Table: " + strCurTable + "(" +
(iCurTableIdx) + ")" + " Row " + (j - 2) + " of " + maxRecords);

lCounter = 0;

}

lCounter++;

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

iTotalRecords++;

if (dsSQLServer.Tables[0].Columns[i].ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{

daAccess.Update(dsAccess, "DT");

}

else

{

// Write to error log

strError = drAccessAdd.RowError.ToString() + "\\n";

}

}

}

dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

MailMessage objMsg = new MailMessage();

objMsg.To = "rh******@timeplus.com";

objMsg.From = "rh******@timeplus.com";

objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year
+ " -- Sql to Access backup conversion job results.";

objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records
were written to: " + DataPath + Database + ".mdb \n\n The following errors
occurred during processing:\n" + strError;

SmtpMail.SmtpServer = "SMTPSERVER1";

SmtpMail.Send(objMsg);

cnAccess.Close();

cnAccess.Dispose();

cnSQL.Close();

cnSQL.Dispose();

if (strError.Length 1)

{

String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";

TextWriter tw = new StreamWriter(strFilename);

tw.WriteLine(strError);

tw.Close();

}

}

}

}

Thanks for any help you might be able to offer!

RSH

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Use DTS packages

"RSH" <wa*************@yahoo.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

It is a significant amount of data (roughly 150 mb).
"RSH" <wa*************@yahoo.comwrote in message
news:uY**************@TK2MSFTNGP05.phx.gbl...

>Hi,

I have a situation where I have created a little application that makes
an Access database from a SQL Database for reporting purposes. it does
the job well, but it seems a bit slow. Is there anything that i can do
to speed the app up a bit?

namespace SQLToAccessBackup

{

class SqlToAccessConvertor

{

// RSH 3/23/2006

// This module creates an Access database from a SQL database

// It requires blank database (will not be destroyed) that has the

// same schema as it's SQL counterpart

// The word 'Blank' should be added to the Access Template databases name
Example: GlobalBlank.mdb

public SqlConnection cnSQL;

public OleDbConnection cnAccess;

public String strError = "";

public long lCounter;

public void RunDataInsert(String SourceServer, String Database, String
Template, String DataPath)

{
int iTotalRecords = 0;

String strDatabasePath = "";

String strSQL = "";

String dStartTime = "";

String dFinishTime = "";

dStartTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

Console.WriteLine("Process Begun : " + dStartTime);
if (File.Exists(DataPath + Database + ".mdb"))

{

File.Copy(DataPath + Database + ".mdb", DataPath + Database + "-BAK-" +
DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year +
DateTime.Now.Hour + DateTime.Now.Minute + ".mdb");

File.Delete(DataPath + Database + ".mdb");

}

File.Copy(DataPath + Template + ".mdb", DataPath + Database + ".mdb");

strDatabasePath = DataPath + Database + ".mdb";

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + strDatabasePath + ";Persist Security Info=False");

cnAccess.Open();

cnSQL = new SqlConnection("Data Source=" + SourceServer + "; Integrated
Security=SSPI; Initial Catalog=" + Database);

cnSQL.Open();

// START LOOP OF ALL SQL TABLES HERE

strSQL = "USE [" + Database + "]";

SqlCommand myCommand = new SqlCommand(strSQL, cnSQL);

myCommand.ExecuteNonQuery();

strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

SqlDataAdapter daSQLTables = new SqlDataAdapter(strSQL, cnSQL);

DataSet dsSQLTables = new DataSet();

daSQLTables.Fill(dsSQLTables);

int iTotalTables = dsSQLTables.Tables[0].Rows.Count;

int iCurTableIdx = 0;

lCounter = 0;

string strCurTable = "";

Console.WriteLine("Processing Table: " + strCurTable + " Table " +
(iCurTableIdx + 1) + " of " +
dsSQLTables.Tables[0].Rows.Count.ToString());

foreach (DataRow drSQLTables in dsSQLTables.Tables[0].Rows)

{

strCurTable = drSQLTables[0].ToString();

iCurTableIdx++;

// ACCESS

String strAccessSQL = "SELECT * FROM " + strCurTable;

OleDbDataAdapter daAccess = new OleDbDataAdapter(strAccessSQL, cnAccess);

DataSet dsAccess = new DataSet();

daAccess.Fill(dsAccess, "DT");

OleDbCommandBuilder cbDevelopment = new OleDbCommandBuilder(daAccess);

cbDevelopment.QuotePrefix = "[";

cbDevelopment.QuoteSuffix = "]";

// SQL

String strSQLServer = "SELECT * FROM [" + strCurTable + "] WITH
(NOLOCK)";

SqlDataAdapter daSQLServer = new SqlDataAdapter(strSQLServer, cnSQL);

DataSet dsSQLServer = new DataSet();

daSQLServer.Fill(dsSQLServer);

int j = 1;

int maxRecords = dsSQLServer.Tables[0].Rows.Count;

foreach (DataRow dr in dsSQLServer.Tables[0].Rows)

{

DataRow drAccessAdd;

drAccessAdd = dsAccess.Tables["DT"].NewRow();

j++;
if (lCounter == 1000)

{

Console.WriteLine("Processing Table: " + strCurTable + "(" +
(iCurTableIdx) + ")" + " Row " + (j - 2) + " of " + maxRecords);

lCounter = 0;

}

lCounter++;

for (Int16 i = 0; i < dr.ItemArray.Length; i++)

{

iTotalRecords++;

if (dsSQLServer.Tables[0].Columns[i].ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns[i].ColumnName.ToString()];

}

}

dsAccess.Tables["DT"].Rows.Add(drAccessAdd);

if (drAccessAdd.HasErrors == false)

{

daAccess.Update(dsAccess, "DT");

}

else

{

// Write to error log

strError = drAccessAdd.RowError.ToString() + "\\n";

}

}

}

dFinishTime = DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute;

MailMessage objMsg = new MailMessage();

objMsg.To = "rh******@timeplus.com";

objMsg.From = "rh******@timeplus.com";

objMsg.Subject = DateTime.Now.Month + DateTime.Now.Day +
DateTime.Now.Year + " -- Sql to Access backup conversion job results.";

objMsg.Body = "The SQL to Access conversion job scheduled to run at: " +
dStartTime + " ran to completion at: " + dFinishTime + ".\n\n" +
String.Format("{0:###,###,###,###,###}", iTotalRecords - 1) + " Records
were written to: " + DataPath + Database + ".mdb \n\n The following
errors occurred during processing:\n" + strError;

SmtpMail.SmtpServer = "SMTPSERVER1";

SmtpMail.Send(objMsg);

cnAccess.Close();

cnAccess.Dispose();

cnSQL.Close();

cnSQL.Dispose();

if (strError.Length 1)

{

String strFilename = AppDomain.CurrentDomain.BaseDirectory +
"DataImportErrorLog-" + ".txt";

TextWriter tw = new StreamWriter(strFilename);

tw.WriteLine(strError);

tw.Close();

}

}

}

}

Thanks for any help you might be able to offer!

RSH