Importing from one Access DB to another Access DB

Question

4.00/5 (1 vote)

See more:

Hello

This is what I have. I have a query that works without a password.

C#

OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
String strConnection
strConnection = (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\AccessDB1.mdb");
objConnection = new OleDbConnection(strConnection);
objConnection.ConnectionString = strConnection;
objConnection.Open();

strInsertSQL = ("Insert into Main(" +
                "ID, " +
                "ItemNum, " +
                "DataType, " +
                "Data, " +
                "Tesxt) ") +
                "SELECT " +
                "ID, " +
                "ItemNum, " +
                "DataType, " +
                "Data, " +
                "Tesxt " +
                " FROM [MS Access; database=c:\\AccessDB2.mdb;].[tblMain]" +
                " where Tesxt in (1,2,3,4)" +
                " order by ID";
            objCmd = new OleDbCommand(strInsertSQL, objConnection);
            
            objCmd.ExecuteNonQuery();
            objConnection.Close();

Now I'm trying to connect to AccessDB2 when its password protected. The password is qwerty123.
I've tried this

C#

" FROM [MS Access; database=c:\\AccessDB2.mdb;Password=qwerty123].[tblMain]" +

This didn't work. Visual Studio 2010 errored out and said "Not a Valid Password" How is this possible?? It is the password that I put in to Access when I open AccessDB2.

I've tried this

C#

" FROM [MS Access; database=c:\\AccessDB2.mdb;Jet OLEDB:Database Password=qwerty123].[tblMain]" +

This didn't work either.

I've tried this

C#

" FROM [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\AccessDB2.mdb;Jet OLEDB:Database Password=qwerty123].[tblMain]" +

This didn't work either

I've tried every variation on this. Why is it that I can connect to AccessDB2 without a password and then when I try to use a password I'm not able to do the insert. Does anyone know why or have a solution??

Here's another question. Is there another way to do the exact same thing another way??

Thanks

Posted 18-Jul-12 16:24pm

mzrax

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2012-07-18T16:25:00

Solution 1

Why not read all the data in to memory, then insert it from there ?

Tesxt

Is this really a column name ?

Posted 18-Jul-12 16:25pm

Christian Graus

Comments

mzrax 18-Jul-12 22:27pm

Yes Tesxt is the actual column name. It was a mistype from the original programmer.

How do you put everything in memory and then insert it into another DB? I've been able to put it in memory from one DB but I've never been able to get it into another DB.

Christian Graus 18-Jul-12 22:40pm

Gotta love quality control....

You read it, put it in an array, then iterate over that array to do the insert.

mzrax 18-Jul-12 23:01pm

I've been able to get the table into a dataset from one DB but how do I get it from that dataset and insert it into anther DB or am I off the mark?? What would it look like??

Christian Graus 18-Jul-12 23:06pm

You can iterate over the data set to read values to insert, or you can read them in to an interim class. You'd iterate over the data each way, and create an insert for each line of data and run it.