Export Access Table to MySQL database using C# program

  • Thread starter Thread starter Faby
  • Start date Start date
F

Faby

Hi,

How to export Access Table to MySQL database using C# program?
Code writtem by me given below. It gives error at the last line.


Microsoft.Office.Interop.Access.Application oAccess = new
Microsoft.Office.Interop.Access.Application();

oAccess = new Microsoft.Office.Interop.Access.ApplicationClass();

oAccess.OpenCurrentDatabase("E:\\RateFormat\\lergdata.mdb", true, null);

oAccess.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acLink,
"ODBC Database", "ODBC;Driver={MySQL ODBC 5.1
Driver};Server=faby-pc;Database=crossgrid_v1;User=root;
Password=faby;Option=3;",
Microsoft.Office.Interop.Access.AcObjectType.acTable, "Lerg10",
"Lerg10",false , false);


Last line gives the following error:
System.Runtime.InteropServices.COMException was unhandled by user code
HelpLink="JETERR40.CHM#5003146"
Message="ODBC--call failed."
Source=""
ErrorCode=-2146825142

Can you please help me..

Thanks in advance.

Faby
 
I think that should be:

Microsoft.Office.Interop.Access.AcDataTransferType.acExport

acLink would assume that the table already exists in MySQL and you're trying
to link to it (as opposed to import it)
 
hi Faby,

First of all, you should ask your question in one of the .net newsgroups.
How to export Access Table to MySQL database using C# program?
Code writtem by me given below. It gives error at the last line.
Click to expand...
Microsoft.Office.Interop.Access.Application oAccess = new
Microsoft.Office.Interop.Access.Application();
oAccess = new Microsoft.Office.Interop.Access.ApplicationClass();
oAccess.OpenCurrentDatabase("E:\\RateFormat\\lergdata.mdb", true, null);
oAccess.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acLink,
"ODBC Database", "ODBC;Driver={MySQL ODBC 5.1
Driver};Server=faby-pc;Database=crossgrid_v1;User=root;
Password=faby;Option=3;",
Microsoft.Office.Interop.Access.AcObjectType.acTable, "Lerg10",
"Lerg10",false , false);
Click to expand...
This basically creates a linked table in your Access database file. It
has nothing to do with an export to MySQL.

Use ADO.Net or the EntLib DAAB to read your data:

http://msdn.microsoft.com/en-us/library/dd203144.aspx
http://davidhayden.com/blog/dave/archive/2007/01/28/EnterpriseLibraryDAABMicrosoftAccess2007.aspx

Use something like this with the MyODBC driver to insert record by
record or a batch into your MySQL table:

public void InsertRow(string myConnectionString)
{
// If the connection string is empty, use a default.
if(myConnectionString == "")
{
myConnectionString = "Database=Test;Data Source=localhost;
User Id=username;Password=pass";
}

MySqlConnection myConnection = new MySqlConnection(myConnectionString);
string myInsertQuery = "INSERT INTO Orders (id, customerId, amount)
Values(1001, 23, 30.66)";
MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}



mfG
--> stefan <--
 
Back
Top