Importing SQL Code into Access 2007
Okay, a few days ago I needed to move data from an Access database to SQL (the opposite is what you are doing). It was easier for me 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 other.
I don't know if this helps, but I am posting my code (this is a simple C # function). You can just change the connections and it will work. Of course I only had 3 fields so I 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!";
}
source to share
If you are trying to import data and not SQL code (see Duffymo's answer) there are two ways.
One is to go where the data is and dump the .CSV file and import it as Duffimo answered.
Another is to link a table from an Access database to a table in the source database. If the two databases talk to each other this way, you can use the data in the remote table as if it were in an Access database.
source to share
SQL code? Or data? "one table and one value" makes me think this is the latter. If so, I would suggest dumping the data into a .csv file and importing it into Access tables.
Or perhaps using a tool like Microsoft DTS to display and move data between sources. That would be a better idea.
source to share
I am assuming you are talking about "importing" both structure and data from SQL into 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 export module that can accept your SQL data in a variety of formats, including Access.
Another way would be to open an Access file and write some basic VBA code using the DoCmd.TransferDatabase method where you can link OR copy tables from other databases in Access.
I forgot that these methods also allow you to port a "clean" database model, including primary keys and relationships ... you have to try.
source to share