Export Access Table to MySQL database using C# program

[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

 

[Douglas J. Steele]

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)

 

[Stefan Hoffmann]

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.

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);

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