We have a C++/ADO program that is inserting very large numbers of records (50K-400K) into a MS-ACCESS database. We have tried three approaches so far -

1.) ADO RecordSet with individual AddNew + Update. Took about 15/20 minutes.
2.) ADO RecordSet using AddNew with BatchUpdate. Took about 10 minutes.
3.) ADO connection or command object executing a SQL INSERT statement. Took about 5 minutes.

We need to get this process to the point it can be executed in about 20 seconds. This is possible, as I can cut the 50K records out of Excel and paste them into the database in about that length of time. Is there a secret way to insert into a table in ACCESS?

This is the latest code:

c = ds->getConn();
iter = _uploadList.begin();
while (iter != _uploadList.end())
{
    str.Format("INSERT INTO VaRData (EffectiveDate,ProductOne,ProductTwo,BucketOne,Buc ketTwo,Rho,SigmaOne,SigmaTwo) VALUES ");
    str2.Format("(%u,", laod);
    str2.AppendFormat("%u,%u,", (*iter)->idOne, (*iter)->idTwo);
    str2.AppendFormat("%u,%u,",((*iter)->bukOne - l_const + 1), ((*iter)->bukTwo - l_const + 1));
        str2.AppendFormat("%f,%f,%f)", (*iter)->rho, (*iter)->sigmaOne, (*iter)->sigmaTwo);
    str.Append(str2);
    c->Execute(str.AllocSysString(), NULL, adExecuteNoRecords);
    iter++;
        }
}
catch(_com_error e)
.
.
.

The only thing I can think of is to go back to Using ADO Batch Update and be very careful with the Cursor Type. Try a foward only cursor or a static cursor with a BatchOptimistic lock for best performance. I am not sure how this will affect your current measurements but might help a bit. I do not think 20 secs is possible, but I am not expert. :-)

FWIW, I was finally able to use the clipboard to post the data into MS-Access. It was painful as you can't use the Access tlb files because they depend on obsolete and conflicting versions of ADO as well as conflicting versions of DAO. I have attached the code below. Please note that it is not cleaned up and assumes you have Access running. To do's are adding code to launch Access and open the tables as well as cleaning up exception handling.

It was worth the effort though as total program time went from over 5 minutes to under 40 seconds.

Mark Jackson



    void mrgVaRProdList::uploadList(void)
    {
#define APPEND_LINE_SIZE 120

        mrgDataSrc * ds;
        mrgRecSet rs;
        list<mrgVaRPoint *>::iterator iter;
        int i;
        unsigned long _l, counter;
        Date bd, d1, d2;
        CString str, buf;
        char * chr;
        size_t bufLen;
        HGLOBAL h;
        HWND wh = GetConsoleHWND();

        ds = _mParent->getDataObj();
        rs.setDataSrc(ds);
        bd.setDate(_mParent->getAsOfDate());
        str = bd.getDateStr();
        i = (int)_uploadList.size();
        bufLen = i * APPEND_LINE_SIZE;
        h = GlobalAlloc(GHND, bufLen);
        try
        {
            if (!OpenClipboard(wh))
            {
                throw("OpenClipboard");
            }
            if (!EmptyClipboard())
            {
                throw("EmptyClipboard");
            }
            rs.ExecSQL("Delete * from VaRData");
            iter = _uploadList.begin();
            counter = 1;
            _l = 0;
            chr = (char *)GlobalLock(h);
            while (iter != _uploadList.end())
            {
                buf.Format("%u\t%s\t", counter, str);
                buf.AppendFormat("%u\t%u\t", (*iter)->idOne, (*iter)->idTwo);
                d1.setDate((*iter)->bukOne);
                d2.setDate((*iter)->bukTwo);
                buf.AppendFormat("%s\t%s\t", d1.getDateStr(), d2.getDateStr());
                buf.AppendFormat("%f\t%f\t%f\r\n", (*iter)->rho, (*iter)->sigmaOne, (*iter)->sigmaTwo);
                for (i = 0; i < buf.GetLength(); i++)
                {
                    chr[_l] = buf.GetAt(i);
                    _l++;
                }
                counter++;
                iter++;
            }
            GlobalUnlock(h);
            if (SetClipboardData(CF_TEXT, h) == NULL)
            {
                throw("SetClipboardData");
            }
            CloseClipboard();
        }
        catch(_com_error e)
        {
            PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
        }
        catch(const char *c)
        {
            LPVOID lpMsgBuf;
            FormatMessage(
                FORMAT_MESSAGE_ALLOCATE_BUFFER |
                FORMAT_MESSAGE_FROM_SYSTEM |
                FORMAT_MESSAGE_IGNORE_INSERTS,
                NULL,
                GetLastError(),
                MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), // Default language
                (LPTSTR) &lpMsgBuf,
                0,
                NULL
            );
            str.SetString((LPTSTR)lpMsgBuf);
            LocalFree(lpMsgBuf);
            cout << "Error in mrgVaRProdList.uploadList()-" << c << ":" << str.AllocSysString() << endl;
        }
        try
        {
            // our ID's
            CLSID clsid;
            IUnknown *pUnk;
            IDispatch *pDisp;
            IDispatch *pDispDoCmd;
            DISPID dispid_DoCmd;
            DISPID dispid_OpenTable;
            DISPID dispid_RunCommand;
            //parameter setup
            DISPPARAMS dispparamsNoArgs = {NULL, NULL, 0, 0};
            OLECHAR FAR* szFunction;
            // for getting/passing results
            HRESULT hr;
            VARIANT varResult;
            // arguments for methods
            VARIANT varArgs[1];
            DISPPARAMS dpArgs;
            BSTR bstrTemp;

            CLSIDFromProgID(L"Access.Application", &clsid);

            // Get an interface to the running instance, if any..
            hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
            if (hr < 0)
            {
                // TODO - we need to start an instance
                throw("Error GetActiveObject");
            }

            // Get IDispatch interface for Automation...
            hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pDisp);
            if (hr < 0)
                throw("Error QueryInterface - IID_IDispatch");
            // Release the no-longer-needed IUnknown...
            pUnk->Release();

            szFunction = OLESTR("DoCmd");
            hr = pDisp->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_DoCmd);
            if (hr < 0)
            {
                pDisp->Release();
                throw("Error GetIDsOfNames for DoCmd");
            }
            hr = pDisp->Invoke(dispid_DoCmd, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET,
                &dispparamsNoArgs, &varResult, NULL, NULL);
            if (hr < 0)
            {
                pDisp->Release();
                throw("Error Invoke for DoCmd");
            }
            pDispDoCmd = varResult.pdispVal;

            szFunction = OLESTR("OpenTable");
            hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_OpenTable);
            if (hr < 0)
            {
                pDisp->Release();
                pDispDoCmd->Release();
                throw("Error GetIDsOfNames for OpenTable");
            }

            bstrTemp = ::SysAllocString(OLESTR("VaRData"));
            varArgs[0].vt = VT_BSTR;
            varArgs[0].bstrVal = bstrTemp;
            dpArgs.cArgs = 1;
            dpArgs.cNamedArgs = 0;
            dpArgs.rgvarg = varArgs;

            //Invoke the OpenTable Method
            hr = pDispDoCmd->Invoke(dispid_OpenTable, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
                           &dpArgs, NULL, NULL, NULL);
            ::SysFreeString(bstrTemp);
            if (hr < 0)
            {
                pDisp->Release();
                pDispDoCmd->Release();
                throw("Error Invoking OpenTable");
            }

            szFunction = OLESTR("RunCommand");
            hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1,
                LOCALE_USER_DEFAULT, &dispid_RunCommand);
            if (hr < 0)
            {
                pDisp->Release();
                pDispDoCmd->Release();
                throw("Error GetIDsOfNames RunCommand");
            }

            varArgs[0].vt = VT_I2;
            varArgs[0].iVal = 0x26; // acCmdPasteAppend
            dpArgs.cArgs = 1;
            dpArgs.cNamedArgs = 0;
            dpArgs.rgvarg = varArgs;
            hr = pDispDoCmd->Invoke(dispid_RunCommand, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
                           &dpArgs, NULL, NULL, NULL);

            // release interface pointers
            pDispDoCmd->Release();
            pDisp->Release();
        }
        catch(_com_error e)
        {
            PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
        }
        catch(const char *c)
        {
            cout << "Error in mrgVaRProdList.uploadList()-" << c << endl;
        }
        return;
    }