Creating an Access database from a SQL Database

  • Thread starter Thread starter RSH
  • Start date Start date
R

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.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.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 = "(e-mail address removed)";

objMsg.From = "(e-mail address removed)";

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
 
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 said:
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.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.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 = "(e-mail address removed)";

objMsg.From = "(e-mail address removed)";

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
Click to expand...
 
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 said:
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.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.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 = "(e-mail address removed)";

objMsg.From = "(e-mail address removed)";

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
Click to expand...
 
It is a significant amount of data (roughly 150 mb).


RSH said:
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.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.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 = "(e-mail address removed)";

objMsg.From = "(e-mail address removed)";

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
Click to expand...
 
Hi,

Use DTS packages

RSH said:
It is a significant amount of data (roughly 150 mb).


RSH said:
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.ColumnName.ToString() != "ID")

{

drAccessAdd[dsSQLServer.Tables[0].Columns.ColumnName.ToString()] =
dr[dsSQLServer.Tables[0].Columns.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 = "(e-mail address removed)";

objMsg.From = "(e-mail address removed)";

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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax Error in INSERT INTO Statement 2
Create an SQL database from a cmd prompt? 5
Uploading An Image from a Windows App to A Database via webservices 2
excel connection and VB 1
Loading data from a huge access database 2
Display an image from access database 1
Delete multiple files from a folder using an Access Database 7
How to issue a SQL query to Access table from Access form? 3

Back
Top