none
Copy Access Tables Into SQL Server RRS feed

  • Question

  • I am utilizing Access 2013 and Visual Studio 2015 - I have written multiple syntaxes on how to link tables from Sql Server into Access, but how to do opposite?  I need to copy all tables in an access database (non-system tables) into a SQL Server 2008 database.  The database name would be FromAccess and the table name in SQL would need to be the same as the table name in access.  So access name = tblCustomers then SQL name = tblCustomers

    What is the syntax that would achieve this?  I need to iterate the tables (cycle them) not just a 1 to 1 relationship like the example shows.

    (I have roughly 240 tables I need to get into SQL Server) --- And yes I am needing to use .NET to do this.
    Thursday, September 1, 2016 12:22 AM

Answers

  • Well the errors are not related to the question at hand. I do see you call GetTables but never use the list returned.

    The foreach should read

    foreach (string tbl in GetTableNames())
    In regards to the COM error, my guess is it has to do with the use of DAO objects and not sure why you are using this but instead use OleDb classes.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 1, 2016 2:40 PM
    Moderator

All replies

  • (I have roughly 240 tables I need to get into SQL Server) --- And yes I am needing to use .NET to do this.

    Maybe you should ask your question in the appropriate forum. And I don't think you need .NET to do it.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Thursday, September 1, 2016 12:37 AM
  • (I have roughly 240 tables I need to get into SQL Server) --- And yes I am needing to use .NET to do this.

    Maybe you should ask your question in the appropriate forum. And I don't think you need .NET to do it.

    I need to use .NET framework and C# to copy in the tables, not the SQL Server Import Wizard.

    Thursday, September 1, 2016 12:40 AM
  • Hello,

    Even though you indicating using C# you would be much better off using Microsoft SQL Server Migration Assistant for Access.

    https://www.microsoft.com/en-us/download/details.aspx?id=43690

    For code, see the following done in vb.net which is an easy translation

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5f60c377-4be3-46e6-8d84-2fc9fdbd8330/use-c-to-import-access-tables-into-sql-server?forum=sqldataaccess


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 1, 2016 12:46 AM
    Moderator
  • @Kareninstructor - thank you for that link, I even found C# syntax that would work.

    source = "Provider= Microsoft.JET.OleDB.4.0;" + "data source=" + mdbFileName;
    mConnection = new OleDbConnection(source);
    mConnection.Open();
    mCommand = new OleDbCommand("SELECT * INTO " + inputACCESStablename + " IN'' [ODBC; Driver={SQL Server};Server=.\\SQLEXPRESS;Database=" + inputSQLdatabasename + ";User ID=YourID; Password= YourPassword; Connect Timeout=30] FROM " + inputACCESStablename, mConnection);
    mCommand.ExecuteNonQuery();
    mConnection.Close(); 

    My last ? is how would I iterate my local tables in the access database ignoring any system tables?


    Thursday, September 1, 2016 12:53 AM
  • I am away from a Visual Studio install to test this, but is this close to what I am after?

    string sqlcon = "ODBC;DRIVER=SQL Server;SERVER=servername;DATABASE=DBName;Trusted_Connection=Yes";
    string path = "R:\Test.accdb";
    DAO.DBEngine db = new DAO.DBEngine();
    dd = db.OpenDatabase(path);
    for (int q = tableNames.GetLowerBound(0); q <= tableNames.GetUpperBound(0); q++)
    {
    	foreach (DAO.TableDef tabledef in dd.TableDefs)
    	{
    		mConnection = new OleDbConnection(path);
    		mConnection.Open();
    		mCommand = new OleDbCommand("SELECT * INTO " + tabledef + " IN' "" + sqlcon + " FROM " + inputACCESStablename, mConnection);
    		mCommand.ExecuteNonQuery();
    		mConnection.Close(); 
    	}
    }


    Thursday, September 1, 2016 1:15 AM
  • @Kareninstructor - thank you for that link, I even found C# syntax that would work.

    source = "Provider= Microsoft.JET.OleDB.4.0;" + "data source=" + mdbFileName;
    mConnection = new OleDbConnection(source);
    mConnection.Open();
    mCommand = new OleDbCommand("SELECT * INTO " + inputACCESStablename + " IN'' [ODBC; Driver={SQL Server};Server=.\\SQLEXPRESS;Database=" + inputSQLdatabasename + ";User ID=YourID; Password= YourPassword; Connect Timeout=30] FROM " + inputACCESStablename, mConnection);
    mCommand.ExecuteNonQuery();
    mConnection.Close(); 

    My last ? is how would I iterate my local tables in the access database ignoring any system tables?


    The key is to get the table names and iterate with them e.g.

    public List<string> GetTableNames()
    {
        var thisBuilder = new OleDbConnectionStringBuilder();
        thisBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
        thisBuilder.DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "NorthWind_1.accdb");
        var tableNames = new List<string>();
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = thisBuilder.ConnectionString })
        {
            cn.Open();
            var dt = cn.GetSchema("Tables");
            tableNames = dt.AsEnumerable().Where(dr => dr.Field<string>("TABLE_TYPE") == "TABLE").Select(dr => dr.Field<string>("TABLE_NAME")).ToList();
            return tableNames;
        }
    }



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 1, 2016 1:20 AM
    Moderator
  • @Kareninstructor - so this would be closer to what I need then, right?

    public List<string> GetTableNames()
    {
        var thisBuilder = new OleDbConnectionStringBuilder();
        thisBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
        thisBuilder.DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "R:\Test.accdb");
        var tableNames = new List<string>();
        using (OleDbConnection cn = new OleDbConnection { ConnectionString = thisBuilder.ConnectionString })
        {
            cn.Open();
            var dt = cn.GetSchema("Tables");
            tableNames = dt.AsEnumerable().Where(dr => dr.Field<string>("TABLE_TYPE") == "TABLE").Select(dr => dr.Field<string>("TABLE_NAME")).ToList();
            return tableNames;
        }
    }
    
    public static void UploadToSQLServer()
    {
    	string sqlcon = "ODBC;DRIVER=SQL Server;SERVER=servername;DATABASE=DBName;Trusted_Connection=Yes";
    	string path = "R:\Test.accdb";
    	DAO.DBEngine db = new DAO.DBEngine();
    	dd = db.OpenDatabase(path);
    	GetTableNames();
    	for (int q = tableNames.GetLowerBound(0); q <= tableNames.GetUpperBound(0); q++)
    	{
    		foreach (DAO.TableDef tabledef in dd.TableDefs)
    		{
    			mConnection = new OleDbConnection(path);
    			mConnection.Open();
    			mCommand = new OleDbCommand("SELECT * INTO " + tabledef + " IN' "" + sqlcon + " FROM " + inputACCESStablename, mConnection);
    			mCommand.ExecuteNonQuery();
    			mConnection.Close(); 
    		}
    	}
    }

    Thursday, September 1, 2016 1:37 AM
  • It looks pretty good to me, it's kind of hard for me to examine everything because I'm on the treadmill right now so give it a shot

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 1, 2016 1:39 AM
    Moderator
  • It looks pretty good to me, it's kind of hard for me to examine everything because I'm on the treadmill right now so give it a shot

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    I am away from a Visual Studio install until the morning, but will definitely give it a whirl then.  I free handed that with notepad++ and the C# plugin :D

    Thank you for the help to this piont :)

    Thursday, September 1, 2016 1:45 AM
  • Finally got to a Visual Studio install and am almost compile error free.  This is full on syntax, but it gives me an error of 

    An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in LinkToSQL.exe

    Additional information: Unrecognized database format 'R:\Test.accdb'.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace LinkToSQL
    {
        class Program
        {
            static void Main(string[] args)
            {
                UploadToSQLServer();
            }
            public static List<string> GetTableNames()
            {
                var thisBuilder = new OleDbConnectionStringBuilder();
                thisBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
                thisBuilder.DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "R:\\Test.accdb");
                var tableNames = new List<string>();
                using (OleDbConnection cn = new OleDbConnection { ConnectionString = thisBuilder.ConnectionString })
                {
                    cn.Open();
                    var dt = cn.GetSchema("Tables");
                    tableNames = dt.AsEnumerable().Where(dr => dr.Field<string>("TABLE_TYPE") == "TABLE").Select(dr => dr.Field<string>("TABLE_NAME")).ToList();
                    return tableNames;
                }
            }
    
            public static void UploadToSQLServer()
            {
                string sqlcon = "ODBC;DRIVER=SQL Server;SERVER=servername;DATABASE=DBName;Trusted_Connection=Yes";
                string path = "R:\\Test.accdb";
                DAO.Database dd;
                DAO.DBEngine db = new DAO.DBEngine();
                dd = db.OpenDatabase(path);
                List<string> tblNames = GetTableNames();
                foreach (string tbl in tableNames)
                {
                    foreach (DAO.TableDef tabledef in dd.TableDefs)
                    {
                        System.Data.OleDb.OleDbConnection mConnection = new System.Data.OleDb.OleDbConnection(path);
                        mConnection.Open();
                        System.Data.OleDb.OleDbCommand mCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO " + tabledef + " IN' " + sqlcon + " FROM " + tabledef, mConnection);        
                        mCommand.ExecuteNonQuery();
                        mConnection.Close();
                    }
                }
            }
        }
    }


    Thursday, September 1, 2016 1:25 PM
  • Well the errors are not related to the question at hand. I do see you call GetTables but never use the list returned.

    The foreach should read

    foreach (string tbl in GetTableNames())
    In regards to the COM error, my guess is it has to do with the use of DAO objects and not sure why you are using this but instead use OleDb classes.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 1, 2016 2:40 PM
    Moderator
  • (I have roughly 240 tables I need to get into SQL Server) --- And yes I am needing to use .NET to do this.

    Maybe you should ask your question in the appropriate forum. And I don't think you need .NET to do it.

    I need to use .NET framework and C# to copy in the tables, not the SQL Server Import Wizard.

    There are several ways to do it with least option of that being to use ADO.NET, SQL Command objects and C#.

    https://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/

    Friday, September 2, 2016 4:49 AM