best way to migrate tables from MS access to sql server and consolidate

Question

I have a ms access database that has one table for each photo album with the name of the table as the albumname

tablename = "Trips" fields: picID, comment

i am migrating to sql server and i wanted to fix this normalization issue and just have

1. One table called Albums with albumID and albumName
2. One table called pictures with picID, albumID, etc . .

is there anyway to automate this process of taking multiple tables in access and moving into one table in Sql server

Answer 1

Adding further to @rexem, you can get the list of table names (i.e. albums) in Access, using the following query

SELECT ID, Name
FROM msysobjects 
WHERE type = 1 AND flags=0

You can use this to INSERT records into Albums table (by creating a linked Table to SQL Server DB's Album table).

EDIT: I am using Access 2007 & can't see "Show System Objects" option.
Alternatively, you will have to write code to get the tables inside MS-Access

See if this link helps - http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun06/hey0608.mspx

Answer 2

thanks for the tips. . i got it working using this code below . .

public void InsertIntoSQL()
    {
        string accessDataSource = ConfigurationSettings.AppSettings["photosDB"];
        OleDbConnection msAccessConnection = new OleDbConnection(accessDataSource);
        msAccessConnection.Open();

        string SQLServerDataSource = ConfigurationManager.ConnectionStrings["kantro_49478ConnectionString"].ConnectionString;
        var sqlConnection = new SqlConnection(SQLServerDataSource);
         sqlConnection.Open();


        DataTable table = msAccessConnection.GetSchema("tables");

        foreach (DataRow row in table.Rows)
        {
            string albumName = row[2].ToString();
            if (!string.IsNullOrEmpty(albumName) && !albumName.StartsWith("~"))
            {
                string sql = "Select * from " + albumName;
                OleDbDataAdapter objCommand1 = new OleDbDataAdapter(sql, msAccessConnection);
                DataSet ds1 = new DataSet();
                objCommand1.Fill(ds1);

                SqlDataAdapter objCommand = new SqlDataAdapter("Select album_id from PhotoAlbums where album_name = '" + albumName + "'", sqlConnection);
                DataSet ds = new DataSet();
                objCommand.Fill(ds);
                string albumID = ds.Tables[0].Rows[0][0].ToString();

                foreach (DataRow dRow in ds1.Tables[0].Rows)
                {
                    string sql1 = "INSERT INTO Pictures VALUES (" + dRow[0].ToString() + ", " + albumID + ")";

                    SqlCommand myCommand = new SqlCommand(sql1);
                    myCommand.Connection = sqlConnection;
                    myCommand.ExecuteNonQuery();
                }
            }
        }
        msAccessConnection.Close();
    }

Answer 3

You could just migrate it to SQL Server first and then do the conversion. May make it easier than trying to wrangle with Access and SQL Server.

Here's a SQL Server Access Migration tool.

http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx