SQL code import into Access 2007

Question

Hey everyone,

I basically need to know how to import SQL code into Access. I've tried one way but that requires me to do one table and one value at a time which takes a lot of time.

Can anyone help?

Answer 1

SQL code? Or data? "one table and one value" makes me think it's the latter. If so, I'd suggest dumping the data out into a .csv file and importing that into Access tables.

Or maybe using a tool like Microsoft's DTS to map and move the data between sources. That would be the best idea.

Answer 2

Well, some days ago I needed to shift data from an Access database to SQL (reverse of what you're doing). I found it simpler to write a simple script that would read data from my access database and insert it into SQL.

I don't think doing what you need to do is any different.

I don't know if it will help, but I posting my code (It's a simple C# function). You can just change the connections and it will work. Of course I only had 3 fields so I hard-coded them. You can do the same for your db schema.

protected void btnProcess_Click(object sender, EventArgs e)
{
    //Open the connections to the access and SQL databases
    string sqlDBCnn = @"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDBFileName=|DataDirectory|\mydb.mdf;user instance=true";
    string accessDBCnn = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=C:\mydb.mdb";

    OleDbConnection cnnAcc = new OleDbConnection(accessDBCnn);
    cnnAcc.Open();

    SqlConnection cnnSql = new SqlConnection(sqlDBCnn);
    cnnSql.Open();

    SqlCommand cmSql = new SqlCommand("DELETE tablename", cnnSql);
    cmSql.ExecuteNonQuery();

    //Retrieve the data from the Access Database
    OleDbCommand cmdAcc = new OleDbCommand("SELECT * FROM tablename", cnnAcc);
    OleDbDataReader drAcc = cmdAcc.ExecuteReader();

    using (drAcc)
    {
        if (drAcc.HasRows)
        {
            //Loop through the access database records and add them to the database
            while (drAcc.Read())
            {
                SqlCommand cmdSql = new SqlCommand("INSERT INTO tablename(Category, Head, Val) VALUES(@cat,@head,@val)",cnnSql);

                SqlParameter parCat = new SqlParameter("cat",System.Data.SqlDbType.VarChar,150);
                SqlParameter parHead = new SqlParameter("head",System.Data.SqlDbType.VarChar,150);
                SqlParameter parVal = new SqlParameter("val",System.Data.SqlDbType.VarChar);
                parCat.Value = drAcc["Category"].ToString();
                parHead.Value = drAcc["Head"].ToString();
                parVal.Value = drAcc["Val"].ToString();
                cmdSql.Parameters.Add(parCat);
                cmdSql.Parameters.Add(parHead);
                cmdSql.Parameters.Add(parVal);

                cmdSql.ExecuteNonQuery();
            }
        }
    }

    lblMsg.Text = "<p /> All Done Kapitone!";

}

Answer 3

If you are trying to import data, rather than SQL code (see Duffymo's response), there are two ways.

One is to go where the data is and dump a .CSV file and import that, as Duffymo responded.

The other is to create a table link from the Access database to a table in the source database. If the two databases will talk to each other this way, you can use the data in the remote table as if it were in the Access database.

Answer 4

I guess you are talking about "importing" both structure and data from SQL to ACCESS. ACCESS does not accept standard TSQL scripts that you could generate directly from your SQL Database. There are some commercial products like EMS that can more or less do the job for you. EMS has a data exporter module that can take your SQL data in different formats, including Access.

Another way would be to open an Access file and write some basic VBA code, taking advantage of the DoCmd.TransferDatabase method, where you can link OR copy tables from other databases into Access.

I forgot if these methods also allow the transfer of a 'clean' database model, including primary keys and relations... You'll have to give it a try.