Insert into from ms access to oracle db

Question

I am trying to create an event which will truncate the data on oracle db table "ded_limit_analysis" and insert from ms access table "Ded-Limit-Analysis" using ADODB connection.

So far I am able to set up the connection and able to excute the truncate query to oracle db table after doing lot research, see the below code. I am pretty much new in this stuff, so it will be great if someone help to achieve this one.

for your information, ms database name is Ded-Limit-ACCM.accdb and I have made both the tables column name as identical, please help...

    Private Sub CheckCon()
    Dim rs As New ADODB.Recordset
    'Dim db As Database
    '   Dim cntr As Long
    Dim con As New ADODB.Connection

Dim str As String
'con.Open ("Provider=PROVIDER;Data Source=SOURCE;User ID=USERID; Password=PASSWORD;")
str = "Provider=PROVIDER;Data Source=SOURCE;Persist Security Info=True;Password=PASSWORD;User ID=USERID"
'Set cnn = CreateObject(“ADODB.Connection”)
con.Open str
'Set rs = CreateObject(“ADODB.Recordset”)
 If con.State = adStateOpen Then
      MsgBox "Welcome to database!"
   Else
      MsgBox "Sorry. No database."
   End If
strSQL = "truncate table ded_limit_analysis"
rs.Open strSQL, con


Do Until rs.EOF
MsgBox rs.Fields(0)
rs.MoveNext
Loop

End Sub

Adding the code I have done so far but not having any luck, I am not getting any error also, please see the below code.

Private Sub comInsert_Click()

Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim strSQL As String
Dim str As String
Dim dbs As Database
Set dbs = OpenDatabase("C:\Users\cthoud01\Documents\Ded-Limit-ACCM.accdb")

str = "Provider=MSDAORA;Data Source=SOURCE;Persist Security Info=True;Password=PASSWORD;User ID=USERID"
con.Open str
 If con.State = adStateOpen Then
      MsgBox "Welcome to database!"
   Else
      MsgBox "Sorry. No database."
   End If

 strSQL = "Insert Into ded_limit_analysis (PDPD_ID,PDDS_DESC,PRODUCT_CAT,BASE_PDPD_ID,PROD_CYCLE,HMO_IND_DED,HMO_FAM_DED,HMO_DED_TO_OOP,HMO_IND_FAC_DED,HMO_FAM_FAC_DED,HMO_DED_PFX_RQD,INN_IND_DED,INN_FAM_DED,INN_DED_TO_OOP,OON_IND_DED,OON_FAM_DED,OON_DED_TO_OOP,INN_OON_DED_PFX_RQD,DED_CARRY_OVR,PLAN_TIER,INN_OON_DED_REL,HMO_IND_OOP,HMO_FAM_OOP,INN_IND_OOP,INN_FAM_OOP,OON_IND_OOP,OON_FAM_OOP,INN_OON_OOP_REL,RX_DED_AMT,RX_DED_TO_MED_DED,RX_DED_TO_OOP,LMT1_SERV,LMT1_TYPE,LMT1_VALUE,LMT1_NTWK,LMT2_SERV,LMT2_TYPE,LMT2_VALUE,LMT2_NTWK,LMT3_SERV,LMT3_TYPE,LMT3_VALUE,LMT3_NTWK,LMT4_SERV,LMT4_TYPE,LMT4_VALUE,LMT4_NTWK,LMT5_SERV,LMT5_TYPE,LMT5_VALUE,LMT5_NTWK,LMT6_SERV,LMT6_TYPE,LMT6_VALUE,LMT6_NTWK,LMT7_SERV,LMT7_TYPE,LMT7_VALUE,LMT7_NTWK,LMT8_SERV,LMT8_TYPE,LMT8_VALUE,LMT8_NTWK,HMO_LTLT_PFX_RQD,INN_LTLT_PFX_RQD,OON_LTLT_PFX_RQD) " _
 & "select * " _
 & "from [Ded-Limit-Analysis];"
con.Execute strSQL


con.Close
dbs.Close

End Sub

Answer 1

I have exactly same requirements as yours. I am providing my solution with comments but its in c#. you'll have to bundle all methods into one class , define any missing variables and invoke the class method ReadAndInsertIntoDB()and you are done.

You can run it from your visual studio or prepare the exe (Console application) suggested and execute from dos prompt. it will keep working in background.

I am assuming that column names and data types for source and destination tables are same or equivalent. I have setup all the variables through app.config file.

string msAccessFilepath, oracleConnStr, msAccessConnectString;      
        string destinationTable, isTruncateDestinationTable, sourceDBfileExtension;
        OleDbConnection oleDBConnection = null;
        int DBCommitRecordBatchSize;

    // Open oledbconnection based on file type extension
    public CopyAccessToOracle() //Constructor
        {
            try
            {  
                msAccessFilepath = ConfigurationManager.AppSettings["MDBDataSource"];                          
                oracleConnStr = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
                isTruncateDestinationTable = ConfigurationManager.AppSettings["DestinationTableToTruncate"].Trim().ToUpper();  // YES or NO                        
                DBCommitRecordBatchSize = ConfigurationManager.AppSettings["DBCommitRecordBatchSize"].Length > 0 ? Convert.ToInt32(ConfigurationManager.AppSettings["DBCommitRecordBatchSize"].Trim()) : 10000;


                sourceDBfileExtension = Path.GetExtension(msAccessFilepath).Trim().ToUpper();
                if (sourceDBfileExtension == ".MDB")
                {                    
                    msAccessConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + msAccessFilepath;
                }
                else if (sourceDBfileExtension == ".ACCDB")
                {                 
                    msAccessConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + msAccessFilepath;
                }
                oleDBConnection = new OleDbConnection(msAccessConnectString);                 
                oleDBConnection.Open();
            }
            catch (Exception Ex)
            {
                ....
            }
        }

Get list of tables from destination db to be updated

private List<string> GetOracleDBTableList()
        {
            List<string> oracleTables = new List<string>();
            try
            {
                OracleConnection connection = new OracleConnection(oracleConnStr);
                OracleDataAdapter adap = new OracleDataAdapter("select Table_Name from tabs", connection);
                DataTable dt = new DataTable();
                adap.Fill(dt);
                oracleTables = dt.AsEnumerable().Select(col => col.Field<string>("Table_Name")).ToList().ConvertAll(t => t.ToUpper());
            }
            catch (Exception Ex)
            {
                 .....
            }
            return oracleTables;
        }

Get List of tables from source db to read data

private List<string> GetMsAccessDBTableList()
        {
            // Microsoft Access provider factory
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            DataTable userTables = null;
            List<string> tableNames = new List<string>();

            try
            {
                using (DbConnection connection = factory.CreateConnection())
                {
                    if (sourceDBfileExtension == ".MDB")
                        connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + msAccessFilepath;
                    else if (sourceDBfileExtension == ".ACCDB")
                        connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + msAccessFilepath;

                    string[] restrictions = new string[4];
                    restrictions[3] = "Table";
                    connection.Open();

                    userTables = connection.GetSchema("Tables", restrictions);
                }

                for (int i = 0; i < userTables.Rows.Count; i++)
                    tableNames.Add(userTables.Rows[i][2].ToString().ToUpper());
            }
            catch (Exception Ex)
            {
               .....
            }
            return tableNames;
        }

Extension Method to check column exist in destination table

public static class DataRecordExtensions
    {
        public static bool HasColumn(this IDataRecord dr, string columnName)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return true;
            }
            return false;
        }

        public static bool HasColumn(this string[] columnList, string columnsToFind)
        {
            if (columnList.Length == 1 && (string.IsNullOrWhiteSpace(columnList[0]) || string.IsNullOrEmpty(columnList[0])))
                return false;
            return columnList.Any(col => string.Equals(col.Trim().ToLower(), columnsToFind.Trim().ToLower()));
        }
    }

This implementation is assuming that source and destination db has tables with same column name and equivalent data types.

public void ReadAndInsertIntoDB()
        {
            OleDbDataReader reader = null;
            OracleDataAdapter oraAdapter = null;
            DataSet oraDS = null;
            OracleCommandBuilder oraCommandBuilder = null;
            OracleConnection oraConnection = null;
            OleDbCommand oleDBCommand = null;
            OracleCommand oracleDBCommand = null;
            DataTable tblDestination;
            DataRow orderRow;
            int recordCount = 0;
            bool isRecordExist = false;
            string tableSuccessfullyCopied = string.Empty;
            List<string> oracleTables = new List<string>();
            List<string> msAccessTables = new List<string>();

            try
            {
                oracleTables = GetOracleDBTableList();
                msAccessTables = GetMsAccessDBTableList();

                List<string> sourceTables = msAccessTables.Where(aTab => oracleTables.Contains(aTab)).ToList();
                foreach (string sourceTable in sourceTables)
                {
                    destinationTable = sourceTable;

                    string selectSourceQuery = "SELECT *  FROM " + sourceTable;
                    //This will give no records but blank table
                    string selectDestinationQuery = "SELECT * from " + destinationTable + " where 1=2 ";
               //Trucate existing records from table. This is to avaoid conflict if there is matching primary key.you can comment this step. 
                    string truncateDestinationTableQuery = "TRUNCATE TABLE " + destinationTable;

                    //Create an OleDbCommand Object.
                    oleDBCommand = new OleDbCommand(selectSourceQuery, oleDBConnection);                    
                    reader = oleDBCommand.ExecuteReader();

                    oraConnection = new OracleConnection(oracleConnStr);                 
                    oraConnection.Open();

                    if (isTruncateDestinationTable == "YES")
                    {                        
                        oracleDBCommand = new OracleCommand(truncateDestinationTableQuery, oraConnection);
                        int rowsDeleted = oracleDBCommand.ExecuteNonQuery();                        
                    }


                    oraAdapter = new OracleDataAdapter(selectDestinationQuery, oraConnection);
                    // Create a Empty Dataset to Fill with data Read Through Data Reader.
                    oraDS = new DataSet();
                    oraCommandBuilder = new OracleCommandBuilder(oraAdapter);
                    // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
                    // key & unique key information to be retrieved unless AddWithKey is specified.
                    oraAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    oraAdapter.Fill(oraDS);
                    tblDestination = oraDS.Tables[0];

                    //Exclude Some of the Columns Who has Default Value at Destination Table
                    if (destinationTableColumnToSkip.Length > 0)
                    {
                        for (int index = 0; index < destinationTableColumnToSkip.Length; index++)
                        {                            
                            tblDestination.Columns.Remove(destinationTableColumnToSkip[index].Trim());
                        }
                    }

                    //Reading All Column Names of source Table of Dataset.
                    string[] sourceColumnNames = Enumerable.Range(0, reader.FieldCount)
                              .Select(reader.GetName)
                               .ToArray();


                    //Reading All Column Names of Destination Table of Dataset.
                    string[] destinationColumnNames = tblDestination.Columns.Cast<DataColumn>()
                                     .Select(x => x.ColumnName)
                                     .ToArray();


                    //Begin Reading Data from Source and Assigning to Destination .
                    recordCount = 0;
                    while (reader.Read())
                    {
                        orderRow = tblDestination.NewRow();
                        isRecordExist = false;                       
                        foreach (string columnName in destinationColumnNames)
                        {
                            if (sourceColumnNames.HasColumn(columnName))
                            {
                                isRecordExist = true;
                                orderRow[columnName] = reader[reader.GetOrdinal(columnName)] ?? DBNull.Value;
                            }
                            else 
                            {
                isRecordExist = true;
                                //If Column is not matching then  add your logic to get correct column name and assign the value

                            }
                        }

                        if (isRecordExist) // Add new row if record exists
                            oraDS.Tables[0].Rows.Add(orderRow);
                        // if Record count is more than Batch Size. Save the records in batch
                        if (recordCount % DBCommitRecordBatchSize == 0)
                        {                            
                            oraAdapter.Update(oraDS);                            
                            //Delete all rows from table after saving it to database
                            oraDS.Tables[0].Clear();
                        }
                    }
                    //Inserting Records Into Oracle Database Using Dataset.
                    // Commit records if its less than 1000                   
                    oraAdapter.Update(oraDS); 
                }

                //Clearing Oracle Connection Pool So that Next Connection to DB will Not Fail
                OracleConnection.ClearAllPools();
            }
            catch (Exception Ex)
            {
                ....
            }
            finally
            {              
                if (null != oleDBConnection) oleDBConnection.Close();
                if (null != reader) reader.Close();
                if (null != oraAdapter) oraAdapter.Dispose();
                if (null != oraDS) oraDS.Dispose();
                if (null != oraConnection) oraConnection.Close();               
            }
        }

To call this implementation after you created the class

CopyAccessToOracle objCopyAccessToOracle = new CopyAccessToOracle()
objCopyAccessToOracle.ReadAndInsertIntoDB();