Microsoft ADO (Advanced Data Objects)

[jj2007]

MS ADO is an incredible mess, but I got something running and would appreciate feedback if it also works on other machines and Windows versions.

Extract the attached files to a temp folder, then drag database files over the executable. Please tell me what works and what doesn't, and your Windows version. Thanks a lot

Please tell me also if MS Office is installed on your machine; it should work without it, but I'd love to get confirmation.

The source is an ugly fat mess, no chance to publish that. However, here is a simpler use of OpenAdo (builds with MasmBasic version 19 January 2024):

Code Select Expand

include \masm32\MasmBasic\MasmBasic.inc
  Init                ; can be xls, xlsx, mdb or dbf files
  .if OpenAdo(Chr$("\Masm32\examples\IczelionSQL\jj\LifeExOECD.xls"))
    For_ ecx=0 To Min(9, OpenAdo(0))-1    ; 0=first sheet
        Print Str$("%_i  ", ecx), AdoTable$(ecx, 0)
        PrintLine At(60) Spc2$, AdoTable$(ecx, 1)
    Next
  .else
    MsgBox 0, AdoErr$(), "OpenAdo:", MB_OK
  .endif
EndOfCode

[jj2007]

Still fighting with M$ ADO, but it's working fine now, and handles xls, xlsx, mdb, dbf, wks and wk4 files.

I am having a lot of fun. For example, there is an obscure error 80040E21h which means "Errors occurred" (!). You can find it mentioned 7400 times googling Microsoft.JET.OLEDB "80040E21". However, 90% of the "solutions" offered are bollocks.

I chased it down on this page (a rare look at a closed source library ):



It turns out that the error occurs in cells with "Vermont" in the attached AdoDemoSup.xls - they have a very special cell property that is, as far as I can see, neither desired nor documented

There is no fix for this error (the JET driver can't handle it); however, OpenAdo will find it and insert #error# into the bad cell.

P.S.: Building the source requires MB 22 January 24

[sinsi]

Can't get any .xls files from the zip to work, popup dialog says
"OpenAdo: Remote ADO Error: ADO could not find the specified provider"

The ODBC data source app shows no DSNs at all but does show an xls driver

Fresh Windows 10 Home installed on VMWare Workstation

Edition   Windows 10 Home
Version   22H2
Installed on   ‎21/‎01/‎2024
OS build   19045.3803
Experience   Windows Feature Experience Pack 1000.19053.1000.0

[jj2007]

Thanks for the feedback, Sinsi

Mine shows no system DNS but 3 drivers on the user tab, see below. I am using the recommended strings:

Code Select Expand

    pvXls db 'Provider="Microsoft.ACE.OLEDB.12.0"', 59, 'Data Source=#;Extended Properties="Excel 12.0 Xml;"', 0
    pvMdb db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#', 0
    pvDbf db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=.;Extended Properties="dBASE IV;"', 0
    pvWkx db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#;Extended Properties="Lotus WK4"', 0
My notebook is a recent Windows 10 machine, almost virgin. However, I did install a) the Microsoft Office Excel viewer and b) the Microsoft Access Runtime 2013.

Further investigation shows that the Excel viewer does not install the missing driver; it's the MS Access runtime that makes xls and xlsx files work with OpenAdo.

The great majority of web pages dealing with MsAdo recommends the Microsoft.JET.OLEDB.4.0 provider, so I had assumed it was installed

OpenAdo uses ACE.OLEDB.12 for xls and xlsx, but for *.xls files, this also works:

Code Select Expand

pvXls db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#;Extended Properties="Excel 8.0;"', 0
I've made a test with a Windows 7 VM, which appears to be virgin. So I can confirm that the Excel files don't work without the Access Runtime, but mdb (Access), dbf, wk4 and wks work fine. I realise I hadn't included any Access files to the test package - here they are, attached below.

[TimoVJL]

Just a simple test:

Code Select Expand

"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""

Code Select Expand

nRecs = -1
TABLE_NAME      Database
TABLE_NAME      World$Excel 2003 connection strings

[jj2007]

Just a simple test:

Full code?

[TimoVJL]

same old schema code

Code Select Expand

#define UNICODE
#define _UNICODE
#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#define INITGUID
#include <ole2.h>
#include <stdio.h>
#include <tchar.h>

#include "msado15.h"

#pragma comment(lib, "user32.lib")
#pragma comment(lib, "ole32.lib")
#pragma comment(lib, "oleaut32.lib")

LPTSTR ErrorString(DWORD errno)
{
    static TCHAR szMsgBuf[0x1000];
    //DWORD msglen =
    FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM|FORMAT_MESSAGE_IGNORE_INSERTS,
        NULL, errno, MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPTSTR) & szMsgBuf, 0x1000 * sizeof(TCHAR), NULL);
    //MessageBox(0, szMsgBuf, szAppName, MB_OK);
    return szMsgBuf;
}

void ADOError(Connection15 *pCn)
{
    HRESULT hr;
    Errors *pErrors = NULL;
    hr = pCn->lpVtbl->get_Errors(pCn, &pErrors);
    if (pErrors) {
        LONG nErrCnt;
        pErrors->lpVtbl->get_Count(pErrors, &nErrCnt);
        printf("Error count %d\n", nErrCnt);
        if (nErrCnt) {
            Error *pError = NULL;
            VARIANT vIdx;
            vIdx.vt = VT_INT;
            vIdx.lVal = 0;
            hr = pErrors->lpVtbl->get_Item(pErrors, vIdx, &pError);
            if (!hr) {
                LONG nErr;
                pError->lpVtbl->get_Number(pError, &nErr);
                BSTR bstrErr;
                pError->lpVtbl->get_Description(pError, &bstrErr);
                printf("%Xh %ls\n", nErr, bstrErr);
                SysFreeString(bstrErr);
            }
        }
    }
}

//int main(int argc, char **argv)
int main(void)
{
    HRESULT hr;
    Connection15 *pCon = NULL;
    //Recordset15 *pRst = NULL;
    _Recordset *pRst = NULL;

    hr = CoInitialize(NULL);
    hr = CoCreateInstance(&CLSID_Connection, NULL, CLSCTX_ALL, &IID__Connection, (void **)&pCon);
    if (hr)
        MessageBox(0, TEXT("Error CoCreateInstance()"), 0, 0);
    else
    {
        VARIANT v1, v2;
        VariantInit(&v1);
        VariantInit(&v2);
        BSTR bstrProvider = SysAllocString(L"Microsoft.JET.OLEDB.4.0");
        BSTR bstrConStr = SysAllocString(L"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");

        hr = pCon->lpVtbl->put_Provider(pCon, bstrProvider);
        hr = pCon->lpVtbl->put_ConnectionString(pCon, bstrConStr);
        hr = pCon->lpVtbl->Open(pCon, NULL, NULL, NULL, adConnectUnspecified);
        //hr = pCon->lpVtbl->Open(pCon, bstrConStr, NULL, NULL, adConnectUnspecified);
        SysFreeString(bstrProvider);
        SysFreeString(bstrConStr);
        LONG nState;
        hr = pCon->lpVtbl->get_State(pCon, &nState);
        if (nState == adStateOpen) {
            SAFEARRAY *pSA = NULL;
            SAFEARRAYBOUND rgsabound;
            rgsabound.lLbound = 0; 
            rgsabound.cElements = 4; 
            pSA = SafeArrayCreate(VT_VARIANT, 1, &rgsabound);

            v1.vt = VT_EMPTY;

            long idx;
            for (int i = 0; i < 3; i++) {
                idx = i;
                SafeArrayPutElement(pSA, &idx, &v1);
            }
            v1.vt = VT_BSTR;
            v1.bstrVal = SysAllocString(L"TABLE");
            idx = 3;
            SafeArrayPutElement(pSA, &idx, &v1);

            VARIANT vtCriteria;
            vtCriteria.vt = VT_ARRAY|VT_VARIANT;
            vtCriteria.parray = pSA;
           
            VARIANT vtMissing;
            //vtMissing.vt = VT_EMPTY;
            vtMissing.vt = VT_ERROR;
            vtMissing.scode = DISP_E_PARAMNOTFOUND;

            //hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtMissing, vtMissing, &pRst);
            hr = pCon->lpVtbl->OpenSchema(pCon, adSchemaTables, vtCriteria, vtMissing, &pRst);
            if (!hr) {
                Fields* pFlds;
                Field* pFld;
                long nRecs = 0;
                VARIANT_BOOL vb = 0;
                hr = pRst->lpVtbl->get_RecordCount(pRst, &nRecs);
                printf("nRecs = %d\n", nRecs);
                VARIANT vName;
                vName.vt = VT_BSTR;
                vName.bstrVal = SysAllocString(L"TABLE_NAME");
                while (!pRst->lpVtbl->get_EOF(pRst, &vb) && vb == 0) {
                //while (!pRst->lpVtbl->MoveNext(pRst)) {    // don't work
                    hr = pRst->lpVtbl->get_Fields(pRst, &pFlds);
                    if (!hr) {
                        hr = pFlds->lpVtbl->get_Item(pFlds, vName, &pFld);
                        if (!hr) {
                            BSTR bstrTmp;
                            hr = pFld->lpVtbl->get_Name(pFld, &bstrTmp);
                            if (!hr) {
                                printf("%ls\t", bstrTmp);
                                SysFreeString(bstrTmp);
                            }
                            VARIANT v3;
                            VariantInit(&v3);
                            v3.vt = VT_BSTR;
                            v3.bstrVal = bstrTmp;
                            hr = pFld->lpVtbl->get_Value(pFld, &v3);
                            printf("%ls\t", v3.bstrVal);
                            SysFreeString(bstrTmp);
                            pFld->lpVtbl->Release(pFld);
                        }
                        pFlds->lpVtbl->Release(pFlds);
                        printf("\n");
                    }
                    hr = pRst->lpVtbl->MoveNext(pRst);
                    if (hr) break;
                }
                // done
                pRst->lpVtbl->Release(pRst);
                printf("\nRecordset close\n");
            } else {
                //printf("hr=%Xh %ls\n", hr, GetADOErrorCode(hr));
                printf("hr=%Xh %ls\n", hr, ErrorString(hr));
                ADOError(pCon);
            }
        } else {
            printf("adStateClosed\n");
            //printf("hr=%Xh\n", hr);
            ADOError(pCon);
        }
        pCon->lpVtbl->Release(pCon);
    }
    CoUninitialize();
    return 0;
}


[jj2007]

So how does your code...

Code Select Expand

        BSTR bstrProvider = SysAllocString(L"Microsoft.JET.OLEDB.4.0");
        BSTR bstrConStr = SysAllocString(L"Data Source=WorldCo.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
... differ from mine?

Code Select Expand

pvXls db 'Provider="Microsoft.ACE.OLEDB.12.0"', 59, 'Data Source=#;Extended Properties="Excel 12.0 Xml;"', 0
pvMdb db 'Provider="Microsoft.JET.OLEDB.4.0"', 59, 'Data Source=#', 0

I use a more recent provider that allows to read both *.xls and *.xlsx files.

[NoCforMe]

@Timo: just a question:

Do you ever post assembly-language code here? All I've ever seen from you is C. Hmm; looking at the name of this whole website ...

Just askin'.

[sinsi]

I noticed that you were using ODBC 64-bit, so I had a look - no DSNs at all and one driver (for SQL server).
The two mdb files opened OK

[jj2007]

you were using ODBC 64-bit

How do you determine that? My code is definitely 32-bit...

[sinsi]

you were using ODBC 64-bit

How do you determine that? My code is definitely 32-bit...

Your two screenshots 

[jj2007]

Your two screenshots 

Oops


To the left, C:\Windows\System32\odbcad32.exe (note there is 2x "32" in the path - Micros*t...)
To the right, C:\Windows\SysWOW64\odbcad32.exe

Not much difference except for the drivers tab

[jj2007]

Attached my spreadsheet editor. It's not yet ready for the ShowCase, so I post it here, hoping to get some feedback on bugs.

Note it has only a right-click context menu. You can either drag a file over the exe, or use the Open spreadsheet function.
Allowed file types are tab, txt and csv, plus xls, xlsx, mdb, dbf, wks for those who have the ADO drivers installed.

Extract all files in the archive to a folder, then drag the tab or xls file over the exe.



P.S.: see also SQL/Microsoft ADO (Advanced Data Objects) VT_DATE quirk