how to get data from access mdb and put it into postgresql table, using visual c#?

Question

Well, what i've already done - is i'm able to connect to access mdb table via Microsoft.Jet.OLEDB provider, then i can get a data from table, using select query, OleDbDataAdapter and a DataSet.

Now, i'm able to connect to Postgresql via Npgsql, but what escapes me - is how am i able to get the data from access table and put it into postgresql table?

What i want to accomplish - is get a data from access mdb table and insert it into a postgresql table using "select into" query.

Answer 1

I don't think you'll be able to use a "SELECT INTO" query from one database to another in this instance (since the two databases are on different engines). What you will have to do is store read the data into some CLR object from the MDB and then read the CLR data into the PostgreSQL database, which is fairly trivial. It's just not possible to do it the way you want

Answer 2

Here are some very amateur notes that may help you. They refer to SQL Server Express, because I do not have access to postgressql. I got the connection string from linking the SQL server table to Access and checking the connection property, however, you can also look here: http://www.connectionstrings.com/postgre-sql. The two lines insert values from table1 in Access into table2 in SQL Express. In Access, the query will fail without warnings if it is not correct (Access 2010 in this case). I tried this in Visual c# 2010 Express.

//create the database connection
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\docs\\db.mdb");

//create the command object and store the sql query
OleDbCommand aCommand = new OleDbCommand("INSERT into [ODBC;Description=TEST;DRIVER=SQL Server;SERVER=COMP\\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=test].Table2 (id, atext) select id, atext from table1", aConnection);

Answer 3

Hey, thanks to all who have helped me, i've finally figured it out. So, for all who will encounter the same situation - here's the solution with a correct connection string for Postgresql.:

//create the database connection
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\docs\\db.mdb");

//create the command object and store the sql query
OleDbCommand aCommand = new OleDbCommand("INSERT into [ODBC;DSN=PostgreSQL;DATABASE=postgres;SERVER=localhost;PORT=5432;UID=postgres;PWD=test;].Table2 (id, atext) select id, atext from table1", aConnection);